- 01DataOne flat data tableEvery row is one event — a visit, a claim, an appointment. The only place raw data lives.
- 02CalcOne PivotTable per KPIAll built from that same table, parked on a hidden calc sheet.
- 03ViewOne dashboard sheetCharts and numbers, plus one slicer and one timeline that drive every PivotTable at once.
Almost every failed Excel dashboard fails the same way: each chart is built from a different range, and then the filters do not talk to each other. The fix is structural, not cosmetic — start from the shape.
Here's the whole build, step by step, using a clinic's numbers as the running example — plus the version limits and performance ceilings that most tutorials never mention.
The architecture (get this right and the rest is clicking)
Almost every failed Excel dashboard fails the same way: someone builds each chart from a different range, then discovers the filters don't talk to each other. So start from the shape, not the styling:
- One flat data table — every row is one event (a visit, a claim, an appointment). This is the only place raw data lives.
- One PivotTable per KPI, all built from that same table, parked on a hidden calc sheet.
- One dashboard sheet that shows the charts and numbers — and one slicer plus one timeline that drive every PivotTable at once.
Microsoft's own dashboard walkthrough describes exactly this: format the source as a Table, build "multiple PivotTables, PivotCharts, and PivotTable tools," then add "Slicers and a Timeline, which allow your PivotTables and charts to automatically expand and contract." If you follow the architecture, the dashboard is mostly assembly.
Step by step
Eight steps, in order. Only one of them is the dashboard — the slicer that drives every PivotTable at once. Everything before it is preparation.
Get your data into one flat table
Export from your practice-management system and lay it out so one row = one visit: date, provider, location, service, amount billed, amount collected, appointment status. One header row, no merged cells, no blank rows, no totals mixed into the data.
This is the least glamorous step and the one that decides everything. A dashboard cannot fix a messy source.
Turn it into an Excel Table (Ctrl+T)
Select the data and press Ctrl+T (or Insert > Table). Two things now work in your favour: the table expands automatically when you type a new row beneath it, and structured references "adjust automatically" as data changes — so next month's export doesn't break your formulas or your ranges.
Give it a real name (Table Design > Table Name), like Visits. Every PivotTable will point at this one object.
Build one PivotTable per KPI
Insert > PivotTable, source = Visits. A PivotTable is Microsoft's tool "to calculate, summarize, and analyze data" — which is exactly what a KPI is. Make one for each question you want answered:
Revenue by month · Visits by provider · No-shows by weekday · Collected vs billed by payer. Put them all on a sheet called Calc — you'll hide it later.
Add a PivotChart to each one
Select a PivotTable and Insert > PivotChart. The chart is bound to its PivotTable: filter the pivot and, as Microsoft puts it, "the chart will also be filtered." On Mac and Excel for the web, create the PivotTable first, then chart it.
Keep chart types boring on purpose — columns for time, bars for ranking, a line for trend. Nobody ever made a better decision because of a 3-D pie.
Wire ONE slicer to every PivotTable — this is the dashboard
Click any PivotTable, then Insert > Slicer and pick a field (Location, Provider, Service). Now the crucial part: with the slicer selected, go to Slicer > Report Connections (in Excel for the web: Slicer Settings > PivotTable Connections) and tick every PivotTable you want it to control.
That single step is what turns a pile of charts into a dashboard: one click on "Location: North" and every KPI on the screen re-filters at once.
Add a timeline for dates
With a PivotTable selected, PivotTable Analyze > Insert Timeline. Microsoft describes it as "a dynamic filter option that lets you easily filter by date/time, and zoom in on the period you want with a slider control" — years, quarters, months or days. Connect it to all your PivotTables the same way (Report Connections).
Now you have the two controls a practice dashboard actually needs: who/where and when.
Lay out the dashboard sheet
On a clean sheet, place the charts in a grid: headline KPI numbers along the top, trend beneath, breakdowns below that. Put the slicer and timeline top-right where a filter belongs. Turn off gridlines (View > Gridlines), and use View > Freeze Panes so the KPI row stays visible as people scroll.
Then hide the Calc sheet. Users should never see the plumbing.
Finish with sparklines and light conditional formatting
A sparkline is "a tiny chart in a worksheet cell" — perfect for a 12-month trend sitting right next to the KPI number (Insert > Sparklines). Conditional formatting's data bars, colour scales and icon sets "make it easier to compare the values of a range of cells at the same time."
Use both sparingly. Microsoft's own performance guidance notes that Excel recalculates visible conditional formats on every recalculation — carpet-bomb the sheet with them and you'll feel it.
Which Excel do you actually need?
This is where a lot of practices get burned, so let's be precise. The core method above — Table, PivotTable, PivotChart, slicer, timeline — works in Excel 2013 and later, and most of it since 2010. You can build the whole dashboard on an old office PC.
What you cannot use on older Excel are the newer formula functions. Microsoft says it outright: "XLOOKUP is not available in Excel 2016 and Excel 2019." The same goes for the dynamic-array trio — FILTER, UNIQUE and SORT require Excel 2021 or Microsoft 365 (they arrived with the 2021 release). If your front desk is on Office 2019 and you build the spine of your dashboard on FILTER formulas, it will simply not work when they open it.
Does it refresh itself?
Partly — and this changed recently, though not for everyone yet. Microsoft is rolling out Auto Refresh for PivotTables built on data inside the same workbook, on by default, and its own page marks the feature as available to Microsoft 365 Insider participants. On ordinary Current Channel you will not see it yet.
Everywhere else, refreshing is a manual act. On older perpetual versions, and for any external data — a Power Query connection, a database, a file you import — you refresh with Data > Refresh All, or tick "Refresh data when opening the file" so it updates each time someone opens the workbook. External connections can also be set to refresh on a timer.
The honest version: Excel never reaches into your practice-management system by itself. Somebody exports, somebody refreshes. Build the habit into the routine — export Monday morning, hit Refresh All, read the dashboard.
When Excel stops being enough
Excel is a genuinely capable dashboard tool, and for a single-location practice it may be all you ever need. But it has real ceilings, and they're documented:
Microsoft's performance guidance is refreshingly blunt about what actually slows a workbook: "it's not the number of formulas or the size of a workbook that consumes the calculation time. It's the number of cell references and calculation operations, and the efficiency of the functions being used." It also warns off volatile functions — NOW(), TODAY(), OFFSET(), INDIRECT() — which recalculate every single time, and against "complex mega-formulas."
So the moment your dashboard gets slow, don't buy a bigger laptop — simplify the model. And if you find yourself maintaining twenty interlocking PivotTables across multiple locations, that's the signal you've outgrown the tool: that's what Power BI does for clinics, and it's free to build on the desktop.
What to actually put on it
The mechanics are the easy half. The harder question is which numbers earn a spot — and the answer isn't "all of them." Start with the handful that change decisions: revenue and collection rate, no-shows, provider utilisation, new and returning patients. Each one belongs on the dashboard because someone will do something about it on Monday. We break down the full set in the 12 KPIs every medical practice should track. Not sure which shape the numbers want? I laid out six dashboard layouts and when each one fits — and the three to never build. And if you'd rather stay in a free browser tool, here's the same dashboard in Google Sheets — pivot tables and one slicer, the same idea.
Frequently asked questions
Yes. Microsoft's own guidance is to format your source data as an Excel Table, build multiple PivotTables and PivotCharts from it, then add slicers and a timeline so the whole dashboard filters together. No VBA or add-ins required.
Insert a slicer, then use Slicer > Report Connections (PivotTable Connections in Excel for the web) and tick every PivotTable you want it to control. The key limit, per Microsoft: slicers can only be connected to PivotTables that share the same data source — so build every PivotTable from the same Excel Table.
The core method — Table, PivotTables, PivotCharts, slicers, timeline — works in Excel 2013 and later, and most of it since 2010. XLOOKUP, FILTER, UNIQUE and SORT require Excel 2021 or Microsoft 365; Microsoft states they are not available in Excel 2016 or 2019.
Partly, and only on the Insider channel so far: Microsoft lists PivotTable Auto Refresh as an Insider feature. Everywhere else, and for external data such as Power Query connections, you refresh manually with Data > Refresh All, or tick "Refresh data when opening the file."
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 feature, version limit and performance figure in this guide is taken from Microsoft's own documentation, linked below. Menu paths can differ slightly between Windows, Mac and Excel for the web. Lucid Vitals is not affiliated with Microsoft.