How to Build a KPI Dashboard in Google Sheets (Step by Step)

To build a KPI dashboard in Google Sheets: put clean data on one tab, summarize it with pivot tables or the QUERY function, chart the results, and add one slicer wired to everything so a single click filters the whole screen. That's not a workaround — Google's own help pages document every piece of this and even call it a "custom dashboard." No Apps Script, no add-ons.

Here's the full build, step by step, using a clinic's numbers as the running example — plus the two limits that matter most for whether Sheets is the right home for your dashboard — the cell ceiling, and how "live" your data really is — and a few smaller ones worth knowing.

The architecture (get this right and the rest is clicking)

Almost every messy Sheets dashboard fails the same way: someone builds each chart from a different range on a different tab, then discovers the filter only moves one of them. So start from the shape, not the styling:

• One clean data tab — every row is one event (a visit, a claim, an appointment). This is the only place raw data lives.
• One summary layer — pivot tables and QUERY formulas that roll that data up into each KPI, all reading from that same tab.
• One dashboard tab that shows the charts and numbers — with a single slicer that drives every chart and pivot at once.

Google documents each of these pieces itself: pivot tables to summarize, charts to visualize, and slicers that — in Google's words — let you "add a slicer to filter your tables, charts, or pivot tables" when "you create a custom dashboard." Follow the architecture and the dashboard is mostly assembly.

Step by step

How "live" is live? (pulling in outside data)

The tempting move is to link one master sheet to others with IMPORTRANGE so the dashboard "updates itself." It can — but it pays to know exactly what "live" means here, because this is where Sheets is most often oversold. Straight from Google's documentation:

• It's not real time. IMPORTRANGE "automatically checks for updates every hour while the document is open." A number changed at 9:05 may not arrive until 10.
• It has a size ceiling. Each pull is "capped at 10MB of received data per request."
• It can't use volatile helpers. Import functions "can't directly or indirectly reference a volatile function like NOW, RAND, or RANDBETWEEN" — the one exception being TODAY.
• It travels the internet even inside your own account. Google warns that the import "still goes through the internet" even if you own the file — and that as a result "you may experience delays and intermittent connections that slow load speed."

For a small dashboard fed by data that already lives in the same file, none of this bites — pivots and charts refresh on change and you'll never notice. It's the moment you stitch many files together with IMPORTRANGE that "live" turns into "hourly, over the wire, under 10MB."

Where Google Sheets is the right tool — and where it breaks

Sheets is a genuinely good dashboard tool, and for a single-location practice it may be all you ever need. Google itself markets it that way — a Workspace post from January 2026, "The 5-minute data dashboard," pitches building one to "share the story behind the data with easy-to-understand charts and graphs." Where Sheets wins is real: it's free, installs nothing, and is built for live collaboration — a file "can only be edited on up to 100 open tabs or devices" at once, which is a limit most teams will never reach.

But it has documented ceilings, and they're worth knowing before you build your whole reporting life on top of them:

Two more constraints are worth naming honestly. First, performance drifts as the sheet grows: Google cautions that NOW is "a volatile function, updating on every edit made to the spreadsheet, and can hurt spreadsheet performance," and that conditional formatting "can take a long time to calculate… As the data grows, there are more and more evaluations that need to happen." Second — and this one isn't a Google quote but a design fact — Sheets has no formal relational data model. There's no equivalent of Excel's Data Model or Power BI's relationships; you join tables with lookups and QUERY, which is fine for one flat table and strains once you have several that need to relate.

So the honest boundary: reach for Sheets when the data is small and live, the team is collaborating in the browser, and free-and-instant matters more than structure. Outgrow any of those — many locations, a real model, data that won't sit inside one file — and the cracks show. That's the point where a purpose-built model earns its keep, and Power BI is free to build on the desktop. One more myth worth retiring: none of this is gated behind paid Google Workspace. Google documents a single 10-million-cell limit for every Sheet and its function list isn't plan-restricted — a free account builds the same dashboard; Workspace buys admin, sharing controls and support, not capability.

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 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. And if you're weighing Sheets against a spreadsheet you can take offline, here's the same build done in Excel — and six dashboard layouts and when each one fits.

Frequently asked questions

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 Google Sheets feature, limit and quote in this guide is taken from Google's own documentation, linked below. Menu paths and some limits can change over time and can differ between browsers and account types. Lucid Vitals is not affiliated with Google.