Build these 6
- KPI tiles + trenda few numbers drive a recurring review
- Single scorecardone number is the point
- Actual vs targeta goal exists
- Small multiplesthe same metric across many things
- Rankingrank order is the message
- Trend focuschange over time is the whole point
Skip these 3
- Gauges & speedometersone number in the space of many
- 3D chartsdepth distorts instead of adding
- KPI soupfifteen widgets, eight colours
Two questions decide it: how many numbers actually matter — one, a handful, or many of the same kind — and whether the story is the level or the change over time. The three on the right fail the same way: they spend a lot of space to carry one number.
I build dashboards for a living — the reporting our managers open every morning at a multi-branch clinic — and I package one as a template. So two honest disclosures up front. I'm not selling you a rival to Excel, which means I can tell you the truth about it, including where it runs out. And every design opinion below is grounded in a named source at the end, not in my taste — because "don't build this" is only worth saying if someone can check it.
First, the only question that matters
Before you look at a single example, answer two things about the numbers you're putting on the page. Almost every good dashboard falls out of the answers.
- How many numbers actually matter? One, a handful (say three to six), or many of the same kind (a metric repeated across twelve clinics, twenty reps, fifty products)?
- Is the story the level, or the change? Do you care what the number is right now, or how it has moved over time?
Notice what isn't on that list: "all of them, on one screen." A dashboard exists to support one decision or one review. If you can't say what decision your dashboard is for, no layout will save it — and that, not the choice of chart, is why most dashboards fail.
The six layouts
The workhorse. A row of three-to-five headline numbers along the top, one chart below showing the trend of the most important one. In my experience most weekly operational reviews want exactly this and nothing more.
Each tile is just a cell referencing a PivotTable result, dressed with a border; the chart is a single PivotChart line. Drop a slicer wired to every PivotTable and the whole thing filters at once.
One metric owns the screen — a north-star number, or a single KPI on a wall-mounted display. One big figure, a sparkline for its trend, and a comparison to its target or to last period.
A large cell for the number, an in-cell sparkline beside it (Excel 2010 and later), and conditional formatting to colour it against a goal. That's the whole build.
Bars for the actual value, a thin marker for the goal, and instant colour telling you who cleared the bar. This is the layout for anything measured against a number it's supposed to hit — collection rate versus benchmark, sales versus quota, utilisation versus plan.
In Excel it's a bar chart with the target added as a second series drawn as markers. Stephen Few's bullet graph is the refined version of exactly this idea, built specifically to replace the gauge (more on that below).
The same little chart repeated once per segment, on a shared scale. Twelve clinics, twenty reps, a metric per product line — the eye scans the grid and the outliers jump out. It's Edward Tufte's favourite device for a reason: it turns "compare twenty things" into a single glance.
The fastest build in Excel is a column of sparklines, one row per segment. For more detail, a grid of tiny identical PivotCharts.
A horizontal bar chart, sorted highest to lowest, top item highlighted. For any question shaped like "which are the biggest / worst / most" — top services by revenue, the weekdays with the worst no-show rate, providers by volume.
A PivotTable sorted descending, plotted as a bar chart — or, if you want it in-grid, conditional-formatting data bars in a sorted table. Bars horizontal, not vertical, so the labels stay readable.
One metric, one big line, the timeline annotated with the things that moved it. When the entire story is the trajectory — a turnaround, a launch, a recovery — give it the whole canvas and mark the events so the chart explains itself.
A line PivotChart, with events added as a helper series or plain text boxes. Keep it to one line if you can.
The three you'll see everywhere — and shouldn't build
Every dashboard gallery is full of these. They look impressive in a screenshot and cost you on the screen. Here's each one, and the named source that says so — because "trust me" isn't good enough.
A dial, a needle, a coloured arc — one number dressed as a car dashboard. The problem isn't that it's ugly; it's that it's expensive. Stephen Few, who wrote the book on dashboard design, made the case against them when he built the bullet graph — the actual-vs-target bar from layout 3 — as their replacement. It was, in his words, "developed to replace the meters and gauges that are often used on dashboards," and its linear form "supports more efficient reading than radial meters" in a fraction of the footprint. A gauge spends the room of many numbers to show one; the bullet graph shows the same comparison in a strip. If you have to build one anyway, gauge charts in Excel walks through the doughnut-and-pie trick and the single case where it earns its space.
Depth and tilt don't add information — they distort it. In a tilted 3D pie the added depth makes a slice read as larger than its true value, and the skew makes any two slices harder to compare; Few shows both in Save the Pies for Dessert. It's a working example of what Tufte calls the "lie factor" — a graphic that exaggerates an effect beyond what's actually in the data.
There's a deeper reason bars beat pies even in 2D. In the classic study of how accurately people read charts, Cleveland and McGill ranked the visual cues by accuracy: position along a common scale sits at the top; length, direction and angle share the next tier down; area sits below all three. A bar encodes value as length and position; a pie makes you compare angles and areas instead — and area sits a tier below length, though angle shares length's tier. Keep a pie only for two or three slices of a genuine whole; past that, use a bar.
The one that wins design awards and helps no one: every metric the team could think of, each in its own bright box, glowing on a dark theme. Two things break it. First, Tufte's data-ink ratio — most of the "ink" is decoration, borders and gradients rather than data, so the actual numbers have to fight to be seen. Second, if the meaning rides on red versus green — "red is bad, green is good" — it's invisible to the roughly 8% of men, about one in twelve, with colour vision deficiency, who see the same drab pair for both.
The fix isn't decoration, it's subtraction. One decision, the few numbers that serve it, and colour used to mean one thing — not to fill space. If everything on the screen is highlighted, nothing is.
Which version of Excel builds these?
Almost everything above needs nothing beyond a mainstream, recent Excel — no add-ins, no macros. A few features have a floor worth knowing, especially before you promise a Mac user something that only exists on Windows.
Two of those deserve a full sentence because people get burned by them:
- You do not need Power Pivot for a normal dashboard. A Table, a handful of PivotTables and one slicer — the standard build — is fully interactive and has worked since Excel 2010 (slicers came to plain tables and the Timeline arrived in 2013). Power Pivot and the Data Model earn their keep only when you must relate several tables or write DAX measures. And they're Windows-desktop features — not in Excel for Mac, and absent from some Office editions. If your team is on Macs, design around that now, not after you've built it.
- PivotCharts can't do everything. A PivotChart "can be any chart type except an xy (scatter), stock, or bubble chart" — so for a scatter you plot from the data directly. And trendlines, data labels and error bars on a PivotChart don't survive a refresh. Minor, until the chart you spent an hour formatting resets itself.
Worth an aside: the worksheet grid tops out at 1,048,576 rows — unchanged since Excel 2007. If your source is bigger than that, the Data Model can hold far more than the grid can, because it doesn't store the data on a sheet. That's usually also the signal that you've outgrown a spreadsheet and want Power BI.
What to actually put on it
Layout is only half the job; the other half is choosing numbers worth the screen. That's its own decision, and I've written it up separately: the five numbers worth a weekly look for a clinic, and the longer list of practice KPIs with the benchmarks that are real and the ones that aren't. Whatever the field, the rule holds — put on the dashboard only what changes a decision, and give the most important number the trend chart.
If you want the mechanics end to end — Table, PivotTables, the one slicer that filters everything — that's the step-by-step build. This piece picks the shape; that one clicks it together. Prefer a free, browser-based tool? The same layouts work in Google Sheets.
Frequently asked questions
Answer two questions. How many numbers matter — one, a handful, or many of the same kind? And is the story the level or the change over time? One number, its level: a scorecard. A handful: KPI tiles, plus a trend chart if the change matters. The same metric across many segments: small multiples. A sorted list: a ranking. Performance against a goal: actual-vs-target bars. One metric whose point is its trajectory: a single trend chart.
Six cover almost everything: KPI tiles plus a trend chart, a single scorecard, actual-vs-target bars, small multiples, a ranking/leaderboard, and a trend focus. Which one is right isn't a matter of taste — it's decided by how many numbers matter and whether you care about the level or the change.
Gauges and speedometers (they spend the space of many numbers to show one — use a bullet graph or a number with a target), 3D charts especially 3D pies (depth and tilt distort the values), and the "KPI soup" of fifteen glowing widgets on a dark background (it buries the data in decoration, and red-vs-green coding fails the ~8% of men with red-green colour blindness). All three are staples of dashboard galleries and all three cost you clarity.
No. A named Excel Table, a few PivotTables and one slicer make a fully interactive dashboard, and that has worked since Excel 2010 (slicers reached plain tables, and the Timeline arrived, in 2013). Power Pivot and the Data Model only matter when you need to relate multiple tables or write DAX. Important caveat: they are Windows-desktop features — not available in Excel for Mac.
Yes. Slicers make PivotTables click-to-filter — introduced for PivotTables in Excel 2010 and for ordinary tables in Excel 2013 — and one slicer can control several PivotTables at once via Report Connections, provided they share a data source. A Timeline slicer does the same for dates. No VBA required.
WRITTEN BY
Olha · clinic data analyst
I build the reporting our managers open every morning at a multi-branch medical clinic — and package it so other practices don't have to start from scratch.
Every Excel capability here was checked against Microsoft's own support documentation; the version-introduction years are established release history. The design claims are attributed to their original authors — Stephen Few on gauges and pies, Cleveland & McGill on how accurately we read charts, Tufte on data-ink and distortion — and I quote them only where I could confirm the exact wording at the source. The illustrations are original, built to show each layout; they are not screenshots of anyone's product. Lucid Vitals is not affiliated with Microsoft.