A dashboard does not fix messy data. It shows it faster. Ten minutes spent on the structure of your Excel file saves hours of confusion later. Here is the checklist we share with every client before a build.
Key takeaways
- Keep one row per record and one header row, with no merged cells.
- Store dates as real dates and amounts as real numbers, not as text.
- Standardise category names so that one region or product is never spelt three ways.
- Leave subtotal and total rows out of the data sheet and let the dashboard calculate them.
- Share a sample with the real structure and dummy values when you order a dashboard.
1. One row per record
Each row should describe one thing: one application, one policy, one account or one sale. Avoid layouts where months run across the columns. A long, simple table is what every dashboard and pivot expects.
2. One header row, no merged cells
Use a single header row with a unique name for every column. Remove merged cells, blank columns and decorative title rows above the data.
3. Real dates and real numbers
Dates stored as text are the most common reason a trend chart looks wrong. Check that date columns are true dates and that amounts are numbers without currency text or stray spaces.
4. Consistent names
“North”, “north ” and “NORTH” are three different regions to a computer. Standardise spellings for categories such as region, product, source and status. A drop-down list in the entry sheet prevents the problem at the source.
5. No totals inside the data
Subtotal and grand total rows get counted twice. Keep them out of the data sheet and let the dashboard calculate them.
6. Keep the columns stable
Try to export the same columns every month. If the layout must change, choose a dashboard that offers column mapping so you can point each field to its new column.
7. Share a safe sample
When you ask someone to build a dashboard, send a sample with the real structure but dummy values. The builder needs the column names and the kind of values in them, not your confidential figures.
Frequently asked questions
What is the best data layout for an Excel dashboard?
A long, flat table: one row per record, one column per field and a single header row. This is the layout PivotTables, Power Query and dashboards all expect.
Why does my trend chart show the wrong months?
Usually because the date column is stored as text. Convert it to a true date, check the day and month order, and the trend will group correctly.
Do I need to share real data to get a dashboard built?
No. A sample file with the same column names and made-up values is enough to design, build and test the dashboard.
Ready when your file is
With these seven points in place, most reports can be turned into a dashboard that refreshes the moment you upload the new file.
