Build a dashboard backward from the decisions it should support. Define a small set of KPIs, keep raw data and calculations off the presentation sheet, use consistent comparison periods, and make filters visible so the reader knows what the numbers include.
- Every dashboard element should answer a named business question.
- Separate raw data, transformation, calculations, and presentation.
- Show current value with target or prior-period context, not a number alone.
Design the questions before the layout
Ask what the reader needs to notice and what action follows. A sales dashboard might answer whether revenue is on target, which region explains the gap, and whether pipeline is sufficient for the next period. That is more useful than starting with a collection of attractive charts.
Define each metric in writing, including its unit, filter rules, and time period. Two teams may use the same KPI name for different calculations, so the dashboard should link to or include the definition.
Build a maintainable workbook
Use one layer for source data, one for cleanup or Power Query output, one for calculations, and one for the dashboard. Tables, PivotTables, and named cells make ranges easier to maintain. Protect formula cells when many people use the file.
- Place the most important status and comparison in the first visible area.
- Use slicers sparingly and state active filters near the report title.
- Add a last-refreshed timestamp and a simple data-quality check.
Lay out a one-page sales dashboard
Leadership needs current revenue, target attainment, regional contribution, and a twelve-week trend.
- Create KPI blocks for revenue and target attainment, each with prior-period context.
- Use a sorted bar chart for regional contribution and a line chart for the weekly trend.
- Add a visible date range, region filter, data refresh time, and link to metric definitions.
Common mistakes
- Filling the page with every available metric.
- Mixing source data and presentation so a refresh can overwrite the layout.
- Showing KPIs without targets, trends, or definitions.
- Hiding active filters and allowing readers to compare different scopes unknowingly.
Try one
Why should raw data live separately from the dashboard sheet?
Separation makes refreshes safer, calculations easier to audit, and presentation changes less likely to damage source records.
Sources
- Microsoft Excel help and learningOfficial reference for Excel features, formulas, tables, charts, and data tools.