Design a useful dashboard

Start with the decision, not the decoration

A sales manager asks: are we on plan, what changed, and where should we investigate? A useful dashboard answers those questions with a small set of defined measures, supporting comparisons and an exception list.

Write the audience, time period, unit and decision at the top of your design notes. For this lesson, the audience is a regional sales manager reviewing April. Sales are net sales after discounts, in one stated currency. Variance means Actual minus Target. Positive variance therefore means sales above target.

A dashboard can be one readable sheet with ordinary formulas and charts. It does not require a Data Model, several chart types or a paid visualization product.

Worked example: define the measures

East has Actual 120,000 and Target 100,000. West has Actual 80,000 and Target 100,000. Total Actual is 200,000 and total Target is 200,000. Overall attainment is 100%, while the regional view reveals East at 120% and West at 80%.

Attainment is total Actual divided by total Target for the selected scope. A zero Target needs an explicit undefined or no-target result. Do not average regional percentages when targets differ: Actual 120/Target 100 and Actual 40/Target 50 combine to 160/150, or about 106.7%, rather than a simple 100% average.

Include both the current value and its comparison. A tile reading 200,000 without a period, unit or target leaves the reader to guess whether the result is good.

Build a readable Excel layout

1. Keep input data on a clearly named source sheet, ideally as a table.
2. Create a summary sheet with total Actual, total Target and attainment formulas or measures.
3. On Dashboard, place the period and active selection near the top, then the KPI values.
4. Add a monthly trend line chart and a category or regional comparison chart.
5. Include a short exception table showing the largest gaps and the underlying exact values.
6. Use consistent number formats, descriptive labels and adequate contrast. Include words or symbols as well as color for status.

Use a line for a chronological trend and columns or bars for comparing categories. Keep units clear; avoid placing currency and percentages on an unexplained shared scale.

Connect every view to the intended scope

When using PivotTables, create compatible reports from the same source and connect Region slicers to every intended PivotTable. A filtered chart beside an unfiltered KPI can tell a contradictory story. Label a fixed-scope measure explicitly if it is intentionally unaffected.

Test a known selection such as East and compare the dashboard against the underlying records. Clear the slicer and recheck the grand total. Add one source record, refresh queries and PivotTables as needed, and confirm the new value reaches each relevant chart and KPI.

Show an as-of date or last verified refresh note that actually reflects the data process. A TODAY formula shows today's date, not proof that the source refreshed today.

Review the dashboard as the reader will see it

Set a practical zoom, inspect the sheet at a normal laptop size, and open a PDF or print preview if a fixed report is needed. Check that the chart titles, filters and exception table remain visible and understandable.

Keep enough free space for refreshed PivotTables and growing exception lists. Provide access to the underlying summary so exact values can be checked. Review the shared workbook's complete contents, because hiding a source sheet does not remove it.

Charts and formulas work across many Excel editions, while native slicer, PivotTable and model authoring options vary. The browser chart builder below practices chart selection and labeling on fixed data. It does not automatically build or audit an entire Excel dashboard.