Building an Excel Dashboard: One Screen, Three Questions
Most Excel dashboards fail for a reason that has nothing to do with Excel. They try to answer eight questions at once, so the person reading them has to work out where to look — and stops looking.
Short answer
Report or dashboard?
The word is used for both, and the difference matters because it changes how you build the thing.
| Report | Dashboard | |
|---|---|---|
| Read by | Skimming down a page | Changing a filter and watching it move |
| Lifetime | One period, then filed | Continuously in use |
| Layout constraint | Whatever fits the page | One screen, no scrolling |
| Interaction | None needed | Slicers, timelines, or nothing happens |
| Fails when | It does not answer the question asked | It answers too many questions at once |
If nobody is going to change a filter on it, build the report. A well-laid-out one-page report is more useful than a dashboard with slicers nobody touches, and it is a fraction of the work.
Before you build: the three questions
Ask what decisions the screen supports
Write them down and stop at three
Work out the one number for each question
Design the visuals backwards from there
The paper test
The architecture
A dashboard is the presentation layer of the same three-layer structure a monthly pack uses. The rules for the data and calculation layers are in the monthly MIS report guide; what changes for a dashboard is that the presentation layer has to work on one screen and respond to a click.
| Sheet | Holds | Visible to the reader |
|---|---|---|
| Data | The source extract, as a Table, one row per record | No — hidden or moved to the end |
| Calc | The pivot tables and any supporting formulas | No |
| Dashboard | KPI tiles, charts, slicers, the as-at date, a short note on definitions | Yes, and nothing else |
One source, one cache
KPI tiles
A KPI tile is a heading, a number and a comparison. That is all it is, and it is the object that does most of the work on a dashboard — because it answers the question before the reader has looked at a chart.
| Element | What it is | How to build it |
|---|---|---|
| Label | One line naming the measure and the period | A cell with plain text — Revenue (Apr–Sep 2025) |
| Value | The headline figure | A formula reading the pivot table, formatted to lakhs or crores |
| Comparison | Change against budget, prior period or prior year | A second formula, with conditional formatting to indicate direction |
| Indicator | An arrow or colour that carries the direction | Conditional formatting on the comparison cell — one rule per state |
| Measure | Value | vs Budget |
|---|---|---|
| Revenue YTD | ₹2.42 Cr | +3.2% |
| Gross margin | 31.4% | −1.8 pts |
| Receivables over 60 days | ₹18.6 L | +₹4.1 L |
| Cash and equivalents | ₹42.8 L | −₹6.2 L |
A tile with no comparison is half a tile
Charts on a dashboard
The chart types are the same ones any report uses — see pivot charts for the selection table. Three additional rules apply when they are on a dashboard rather than a page.
- No chart repeats another. A bar chart of revenue by state and a pie chart of revenue by state is one chart and one wasted panel.
- Titles state the finding, not the field names. “South region is 12% behind plan” gets read. “Revenue by Region” does not.
- No chart needs a legend if it has one series. Legends are for distinguishing series; a single-series chart has nothing to distinguish.
- Two colours, maybe three. One for the primary series, one for the comparison, and a colour reserved exclusively for “outside threshold”. Every additional colour reduces the meaning of the ones already there.
Slicers
Insert the slicer from one pivot table
Connect it to every pivot table
Set the slicer to the default you want
Put the slicer where it is seen
The forgotten filter is the dashboard's main failure mode
The as-at date
Every dashboard needs one, and it needs to be honest. A reader deciding whether to act on a figure has to know how old it is.
| Approach | What it shows | Verdict |
|---|---|---|
| TODAY() or NOW() | The date the file was opened | Wrong — it looks current even when the data is two months old |
| A typed date | Whatever was typed last | Fine, provided somebody owns updating it |
| MAX of the date column in the source table | The latest date actually in the data | Best — it cannot lie, and it updates with the refresh |
The MAX approach is the one to use, and it costs one formula. It also has a useful side effect: if the date does not move after a refresh, the data did not load.
Layout
| Zone | What goes there | Why |
|---|---|---|
| Top band | Title, period, as-at date, the slicer | Orientation first — what is this, and what am I looking at |
| Second band | Three to six KPI tiles with comparisons | The headline answer, before any chart is read |
| Middle | Two to four charts, most important on the left | The eye starts top-left; put the thing that matters most there |
| Bottom | One detail table, if the dashboard genuinely needs one | The detail is where the reader goes after something has caught their attention |
| Nowhere | Gridlines, row and column headers, the calc sheets, the source | Every piece of visual furniture competes with the data |
Turn off gridlines and headings on the dashboard sheet — View tab, uncheck Gridlines, Headings and Formula Bar. It takes five seconds and it is most of the difference between something that looks like a dashboard and something that looks like a spreadsheet.
Speed
- One cache, not eight. Build all pivot tables from one source in one session.
- Chart source ranges, not whole columns. A formula searching an entire column over a hundred thousand rows recalculates everything on every edit.
- Avoid OFFSET and INDIRECT. Both are volatile, meaning they recalculate whenever anything anywhere changes. XLOOKUP and INDEX achieve the same thing without that cost.
- Go easy on conditional formatting. Each rule over a whole column is evaluated for every cell in it, on every recalculation.
- Keep the calc layer on its own sheet. Not for speed — because a formula that takes time is easier to find when it is not buried under a chart.
Common mistakes
- Building the visuals before deciding the questions. The dashboard grows rather than being designed, and it shows.
- Slicers connected to one pivot table out of five. The most dangerous bug a dashboard can have, because the numbers on screen then disagree with each other.
- Rolling totals with no comparison. Nothing on the page says whether the figure is good or bad.
- A dashboard that scrolls. A dashboard that does not fit on a screen is a report with worse formatting.
- Sending the live workbook. The recipient refreshes it against a source they cannot see and gets an error, or reads cached figures dated two months ago and does not know.
- No definition of the measures. “Gross margin” means at least two things. One short note on the definitions ends the argument before it starts.
- Rebuilding the data by hand each month. If the source is assembled manually, the dashboard’s reliability is decided upstream of it — see Power Query in Excel.
What a dashboard cannot do
A dashboard presents agreed numbers well. It cannot make an unagreed number right, and the better it looks, the more authority it lends to whatever is underneath it. If the receivables figure comes from a ledger the customer disputes, or the input credit figure comes from a comparison nobody finished, the dashboard does not reveal it.
Where the underlying figure comes from a comparison — books against statement, return against register, ledger against bank — the comparison has to be done and explained first. That is the step Piloteq Automate handles, and the reason reconciliation comes before reporting in the monthly routine.
If you would rather not build this by hand
When the dashboard needs a number you can defend
A dashboard is a set of summaries with a filter on top. If the figures underneath came from an unreconciled comparison, the dashboard makes a wrong number look authoritative. Piloteq Automate settles that earlier step — matching, tolerances, group matching and a reason recorded per difference.
- ✓The same rules applied every period
- ✓Tolerance and group matching you configure
- ✓Missing records reported for both sides
- ✓A consistent result to report from
Frequently asked questions
What is the difference between an Excel report and a dashboard?+
A report is read; a dashboard is interrogated. A report answers a question for a period and is filed. A dashboard sits on one screen and lets somebody change the filter and watch the answer change. If nobody changes anything on it, what you have built is a report laid out attractively — which is fine, but it is not the same thing and it does not need slicers.
What should go on an Excel dashboard?+
Three questions, no more. Decide which three decisions the screen is meant to support and build only the visuals that answer them. Anything that does not answer one of the three belongs in the underlying workbook, not on the page. A dashboard trying to answer eight questions answers none of them, because the reader has to work out where to look.
How do I make an Excel dashboard update with new data?+
Keep the source data in an Excel Table, build the pivot tables on that Table, and press Refresh All after replacing the data. The pivot charts and the slicers follow automatically. The dashboard itself contains no data — only the pivot tables, the charts and the controls that read from them.
How do I reset slicers back to showing everything?+
Excel has no built-in clear-all for slicers. The options are: tell the reader in a cell at the top of the dashboard that a filter is applied, add a small macro that clears every slicer on the sheet, or use the slicer's own clear filter button in its top-right corner, which resets that one slicer. The macro is the only one that resets all of them in one action.
How do I show the date the dashboard data is current as of?+
Put a cell on the dashboard showing the latest date in the data — a MAX of the date column from the source table — rather than a NOW() formula. NOW() recalculates when the file is opened, so it would show today's date even if the data has not been refreshed since last month, which is exactly the impression the cell exists to prevent.
Why is my Excel dashboard slow?+
Usually because several pivot tables each hold their own copy of the source data, and several charts each read a pivot table independently. Building all the pivot tables from one source in the same session lets them share a cache. A large number of conditional formatting rules over whole columns, and volatile functions such as OFFSET and INDIRECT in the calc layer, are the other common causes.