Structure
Separate the workbook into layers, one sheet each:
- 1Data — the raw table, untouched
- 2Calculations — pivots and helper formulas, hidden from users
- 3Dashboard — only the visible output
Mixing them is what produces the workbook nobody dares change. Keeping raw data separate also means refreshing is replacing one sheet, not rebuilding.
Layout
People read top-left first. Put the headline numbers there, the main trend below, breakdowns beneath that.
Use linked pictures (Copy → Paste Special → Linked Picture) to place a pivot's output anywhere on the dashboard without the pivot's own layout constraining the design. The picture updates when the pivot does.
Turn off gridlines on the dashboard sheet — View → uncheck Gridlines. It is one click and immediately makes a sheet look like a designed page rather than a spreadsheet.
KPI cards
A large number with a comparison beats a chart for a headline metric.
=TEXT(current, "$#,##0,,\"M\"") & " (" & TEXT(current/prior-1, "+0.0%;-0.0%") & ")"Merge a few cells, set a large font, and apply conditional formatting to colour by direction. The custom format with +0.0%;-0.0% shows the sign automatically.
Charts
Keep them plain: remove the chart border, drop the legend where one series makes it redundant, delete gridlines that are not aiding comparison, and label directly where you can.
A combo chart — bars for actuals, line for target — is the most useful single chart type for a business dashboard.
Sparklines (Insert → Sparklines) put a small trend inside a cell, which is ideal beside a KPI number.
Maintainability
Build every pivot on a Table, not a range, so new rows are included on refresh. Name ranges meaningfully. Document any non-obvious calculation in a notes sheet.
The test is whether someone else can add next month's data and refresh without asking you how.