From Messy Spreadsheet to Operating Dashboard
A practical path from the spreadsheet everyone is afraid to touch to a dashboard your team checks every week.
Most teams already have the data they need. It lives in a spreadsheet with merged cells, three date formats, totals typed in by hand and a tab called "FINAL v2 (use this one)". Turning that into a dashboard people trust is mostly careful cleanup and a few firm rules.
1. Start with the decisions, not the charts
List the three to five decisions the dashboard should support. "Which programs are behind on enrollment?" "Which invoices are overdue?" Every chart should answer one of those questions. If a chart doesn't change a decision, leave it out.
2. Separate raw, clean and display
Use three layers, even in a single workbook: a raw tab where data is pasted or imported and never edited, a clean tab built from it with formulas, and a dashboard tab that only reads from the clean data. When something looks wrong, you can trace it back in minutes.
3. Make the data tidy
The simplest structure is the most durable: one row per record, one column per attribute, one value per cell. That's the core of the "tidy data" idea described by statistician Hadley Wickham. In practice:
- No merged cells, blank spacer rows or totals inside the data.
- One date format, real dates rather than text.
- Consistent names: "NYC", "New York" and "nyc" become one value.
- Dropdown validation for any column people type into.
4. Replace typed numbers with formulas
Every total, rate and status on the dashboard should be calculated, never typed. Typed numbers are where silent errors live. If the team needs manual adjustments, give them their own clearly labeled column.
5. Build charts that answer the questions
Pair each decision from step one with one view: a trend line for "are we on track", a ranked bar for "where is the problem", a short table for "what needs action this week". Label charts with the question they answer, not the data they show.
6. Give it an owner and a rhythm
Decide who refreshes the data, when, and who reviews it. A dashboard without a weekly or monthly routine slowly drifts out of date until no one trusts it. Add a "How to use" tab that explains the sources, the refresh steps and who to ask.
A good operating dashboard is not impressive. It's current, correct and opened every week. That's what makes it worth building.