Everyone wants “a dashboard”. What they usually get is a decorated spreadsheet: fourteen charts on a rainbow background, three of them contradicting each other, refreshed by a person who has learned to dread Mondays. The difference between that and a real dashboard isn’t design flair. It’s architecture — and a ruthless answer to one question: what decisions is this page for?
A dashboard is not a place to show data. It’s a place where someone looks for at most a minute and knows whether things are fine and where to poke if they aren’t. One minute, one page. Everything else follows from taking that seriously.
The three-layer architecture
The single biggest cause of dashboard rot is mixing storage, calculation and display on one sheet. Separate them — three sheets, three jobs:
- Data — one proper Table (or a Power Query output if the data arrives monthly). Nobody ever types on this sheet; data lands here.
- Calc — the aggregation layer: PivotTables and SUMIFS working over the Table, plus the handful of named cells (targets, thresholds) the display compares against.
- Dashboard — the one visible page. Charts, KPI cells and slicers pointing at Calc. No raw data, and ideally no arithmetic beyond a reference — if a number needs computing, it computes on Calc where there’s room to audit it.
When the data doubles, you extend one Table. When a chart misleads, you fix one layer. The rainbow version has these three jobs braided through forty cells, which is why nobody dares touch it.
The five components
- A KPI row across the top: three to five numbers, each with a comparison (vs last month, vs target — a raw number answers nothing). Big font, plain cells. The ⚠ status comes from an IFS on Calc, not from someone remembering to colour a cell.
- One main chart — the trend that matters most, built by the truth rules: sorted, zero-based, direct labels, one colour plus grey.
- One or two breakdowns — smaller charts or a top-five list (a spilled SORT/FILTER does self-updating top-fives beautifully).
- Slicers for the one or two dimensions people genuinely ask about. Insert a slicer from any pivot, then right-click → Report Connections to wire it to every pivot on the page — one click, whole dashboard filters. That interactivity is most of what people mean when they say “dashboard”.
- A timestamp: a cell saying when the data was last refreshed. Costs nothing; saves every argument that starts “is this current?”
And conditional formatting exactly where it belongs: on the exceptions, nowhere else. A dashboard is the highest-stakes venue for the highlighting-not- decorating rule — it’s the page executives see.
The disciplines that keep it alive
One page means one page. Every addition must evict something — that’s the feature, not the constraint. The moment “just one more chart” wins, you’re six weeks from the rainbow. Park the overflow on a second sheet (“Detail”) and link to it.
The Monday routine should be: refresh, glance, send. If updating the dashboard involves pasting, retyping or “fixing the ranges”, the plumbing is wrong — Tables and queries exist so that new data flows through untouched. A dashboard you maintain by hand is a report with ambitions.
Prototype on paper first. Sketch the page, show the person it’s for, and ask what they’d decide differently for each element. Anything that changes no decision comes off the sketch. Cheapest iteration you’ll ever do.
This is stage five of the path working as one system — Tables feeding pivots feeding honest charts, with names, logic and formatting each doing their one job. If those pieces are solid, the dashboard is mostly assembly. If they aren’t, no amount of design will save it.
One page. One minute. Every pixel earning its place — or leaving.