Excel gets sneered at by people who have never shipped a dashboard on a deadline. It is on every machine in the building, everyone can read it, and for a lot of teams it is the honest answer to “where should this live.” The trick is knowing where its ceiling is, so you use it fully right up to that line and move on without drama when you cross it.
This is a practical build. When Excel is the right call, how to wire PivotTables and slicers into an interactive board, how to format numbers so people can scan them, how to refresh without copy-paste, and the exact signals that mean it is time to graduate to Power BI. The design principles underneath all of it live in the pillar on dashboard design.
When Excel is enough
Excel is enough more often than tool-vendor marketing wants you to believe. If your data fits comfortably (call it under a hundred thousand rows), one or two people maintain the thing, and a weekly or monthly refresh is fine, a spreadsheet dashboard will serve you for years. The data probably already lives in Excel or exports to it cleanly. Fighting that is effort spent for no reader benefit.
Here is the line. Excel starts to hurt when several things stack up at once:
- The data comes from three or more systems that need joining.
- More than a handful of people view it, and they all want it current.
- Someone needs it to refresh on its own, on a schedule, without a person clicking a button.
- You need row-level security so a regional manager sees only their region.
None of those is fatal on its own. Two or three together and you are propping up a spreadsheet with willpower. Until then, build it in Excel and do not apologize.
PivotTables and slicers
Build the engine and the dashboard on separate sheets. Put your clean data on one tab, your PivotTables on a working tab, and the dashboard on a third that the reader actually sees. The PivotTable is where aggregation happens, rolling up detail into a summary so a hundred thousand order rows become “revenue by month” in a couple of clicks.
Slicers are what make it feel alive. A slicer filters, the same job it does anywhere, and the part people miss is that one slicer can drive several PivotTables at once. Right-click the slicer, choose Report Connections, and tick every pivot it should control. Now one click on “West region” moves the whole board together instead of one chart at a time.
For the KPI cards, do not park raw pivots on the dashboard sheet. Pull the numbers into clean cells with a lookup so you control the formatting. GETPIVOTDATA reads a single value straight out of a pivot:
=GETPIVOTDATA("Revenue", $A$3, "Month", "Jul")
// or, if you loaded the data into the Data Model and wrote a measure:
=CUBEVALUE("ThisWorkbookDataModel", "[Measures].[Total Revenue]",
"[Calendar].[Month].[Jul]")The second form matters once you have more than one table. Load your tables into the Data Model, relate them by keys the way you would in any database, and Excel gives you a small star schema without you calling it that. A data model relates tables by keys, and that relationship is what lets one slicer filter revenue, units, and margin from three different tables in sync.
Formatting for scanning
Default Excel looks like a spreadsheet, and a dashboard should not. The single biggest upgrade is the one nobody does first: turn off the gridlines. Gridlines should fade into the background, and in Excel that means killing them entirely (View, uncheck Gridlines) so the eye follows your layout instead of a graph-paper grid.
Then fix the numbers. A KPI card displays one headline number, and that number should be readable at arm’s length, not squinted at. Use a custom number format so long figures round to the decision:
#,##0,"K" -> 48,200 shows as 48K
#,##0.0,,"M" -> 4,820,000 shows as 4.8M
0.0% -> 0.312 shows as 31.2%Each trailing comma divides by a thousand, so one comma gives you thousands and two give you millions. Number formatting rounds to significant digits, and a card reading 4.8M lands faster than one reading 4,820,000 while telling the reader everything the decision needs.
Three more moves and the sheet stops looking like a workbook:
- Conditional formatting for status. Set a rule so a value colors itself red, amber, or green against a threshold. Conditional formatting colors a value by rule, which means the color is earned by the data, not painted by hand and forgotten the next month.
- Sparklines for trend. Insert a line sparkline next to each KPI card. A sparkline compresses a trend into a small space, so a card can say “revenue, 4.8M, and here is the last twelve months” inside one row.
- Consistent color. Pick one color per metric and keep it everywhere. Consistent color signals the same metric, so if revenue is blue in the card it is blue in the chart, and the reader never has to relearn your palette mid-scan.
Keep the whole thing on one screen. A dashboard fits on one screen, and in Excel that means designing to the print area or the visible window, not letting it sprawl down to row 400 where nobody scrolls.
Refreshing data
The moment that breaks trust is predictable. Someone opens the dashboard on the 3rd, the numbers are from the 28th, and now every figure on the screen is suspect. Stale data erodes trust, and it erodes it retroactively, poisoning the numbers that were actually fine. So the refresh is not a nice-to-have, it is the difference between a dashboard and a museum piece.
Do not copy-paste new data over old. Use Power Query. Power Query transforms source data on the way in, so you point it at the file, folder, or database once, tell it how to clean the data, and from then on a single “Refresh All” pulls the latest and reruns every step. Data cleaning removes errors before display, and doing it in Power Query means the cleaning happens the same way every time instead of depending on whoever refreshed it remembering the steps.
Two settings finish the job. In the query properties, tick “Refresh data when opening the file” so the reader always sees current numbers. And keep one source, not five copies of it, because a single source of truth prevents conflicting numbers and the fastest way to lose an argument in a meeting is two tiles that disagree about the same figure. Excel on its own cannot run a refresh on a timed schedule; if you need that, the file has to live on OneDrive or SharePoint with Power Automate driving it, and that requirement is itself a hint about the next section.
When to move up to Power BI
So when do you actually leave? Not when someone tells you Excel is unprofessional. You leave when the job changes shape, and it announces itself clearly:
- You need a genuine scheduled refresh that runs without anyone opening the file.
- More than a handful of viewers need the same board, live, without you emailing versions.
- You are joining several large sources and Excel is starting to crawl.
- You need row-level security so each viewer sees only their slice.
- You want real time intelligence (year-to-date, same period last year) that DAX handles cleanly and worksheet formulas fake badly.
The move is less painful than it sounds because the skills carry over. Power Query is the same tool in both. Your Data Model relationships become the Power BI model. And Power BI draws a line Excel blurs: it separates reports from dashboards, where the report is the detailed multi-page thing and the dashboard is the pinned single-screen summary. If you have already built to one screen in Excel, you built the dashboard half correctly and the port is mostly mechanical. The worked layouts are in the Power BI dashboard examples, and if you would rather start from a frame than a blank canvas, the dashboard templates wireframe transfers to either tool.
Do not upgrade out of insecurity. Upgrade when a real constraint bites. Plenty of dashboards that run entire departments are Excel files that someone formatted with care, refreshed on a rule, and kept to one screen. That is not a downgrade, that is a job done.