A dashboard is one screen that answers “how are we doing?” without anyone scrolling through raw rows. In Google Sheets it’s three tabs — raw data, calculations, and the dashboard itself — wired together with QUERY and a few charts, refreshing live as new data lands. Build it once and the numbers stay current on their own, which is the whole point of a dashboard over a report you rebuild by hand each week.
Separate data, calculations, and display
The single habit that makes a dashboard maintainable is keeping three jobs on three tabs. Mix them and every change risks breaking something; separate them and each tab has one clear purpose.
| Tab | What lives there |
|---|---|
| Raw Data | Source records, one row each — appended to, never reformatted |
| Calculations | QUERY and formulas that summarize raw data into KPI numbers |
| Dashboard | The charts and scorecards people actually look at |
Data flows one direction: raw feeds calculations, calculations feed the dashboard. When a number looks wrong, you know exactly where to look — the calculation tab — without untangling charts from formulas from source rows. This separation is what lets a dashboard survive months of new data and the occasional structural change; the dashboard tab never touches raw rows directly, so reformatting the source can’t break a chart.
What matters isn’t three literal tabs but three distinct layers — somewhere data arrives, somewhere it’s summarized, somewhere it’s shown. On a small dashboard the calculations layer can be a single hidden sheet, and the raw data can even be another file pulled in live. Collapse the layers into one and you get the spreadsheet everyone dreads inheriting, where one wrong click breaks a chart and nobody can tell why. Keep them apart and the thing stays debuggable for years.
Summarize the data with QUERY
The calculations tab is where raw rows become KPIs, and QUERY does most of the work in one formula instead of a dozen SUMIFs. It runs SQL-like commands against your data:
=QUERY('Raw Data'!A:E, "SELECT B, SUM(D) GROUP BY B")This returns total revenue per sales rep — column B grouped, column D summed — as a clean summary table that grows with the data. One QUERY replaces a stack of SUMIFS formulas, and it reshapes instantly when you change the SELECT. Build one summary table per chart you plan to show: revenue by rep, deals by stage, spend by month. Each is a QUERY on the calculations tab, and each becomes the source for one dashboard chart. When the source needs cleaning first, the QUERY function guide covers the WHERE and ORDER BY clauses that filter and sort before summarizing.
One QUERY per KPI keeps the calculations tab readable: each summary table is labeled, sits in its own block, and feeds exactly one chart. When a number on the dashboard looks off, you trace it to a single QUERY rather than picking through nested formulas. The SQL-like syntax reads almost like a sentence — SELECT what, GROUP BY which dimension — so a teammate can follow it without learning a new function from scratch, which matters when you’re not the only one maintaining the sheet.
Scorecard charts for the headline numbers
The numbers that matter most — total revenue, active deals, conversion rate — deserve to be big and obvious, not buried in a table. Scorecard charts do exactly that: a single large number with an optional comparison.
- Select the cell holding the KPI value on your calculations tab.
- Insert → Chart, then choose Scorecard chart under the chart-type menu.
- Add a baseline value to show the change — this month versus last.
A row of scorecards across the top of the dashboard gives an instant read: the three or four numbers a manager checks first, each one large enough to see from across a room. The optional baseline turns a static figure into a trend — “$48,000, up 12% on last month” — which is far more useful than the number alone. Scorecards are the headline; the charts below them are the detail.
Scorecard charts live under the “Other” category in the chart-type menu, and Google’s scorecard chart documentation covers the baseline and formatting options. Use them sparingly — three or four headline numbers, not ten. A scorecard earns its size by being something a manager checks first; a wall of them just recreates the dense table you were trying to escape, only bigger.
Charts that read the live summaries
Below the scorecards go the charts, each one built from a QUERY summary on the calculations tab. A column chart for revenue by month, a bar chart for deals by stage, a line for a trend over time — every chart points at a summary table, never at the raw data directly.
Keep the chart count honest: a dashboard with four well-chosen charts beats one with twelve nobody reads. Each chart should answer a question someone actually asks, and anything that doesn’t earn its space belongs back on the calculations tab. A conditional formatting rule on a summary table can flag outliers right where the chart can’t.
Layout is part of the message. People read a dashboard like a page — top-left first — so put the chart that answers the most-asked question there, not buried at the bottom. Group related charts together, give each one a short title that states what it shows, and leave whitespace between blocks. The difference between a dashboard people use and one they ignore is often just whether the important number is the first thing the eye lands on.
Keep it real-time and shareable
The “real-time” in a real-time dashboard comes from how the data arrives and who can see it. A few habits keep it live and trustworthy.
- ✓ Raw data is appended, not overwritten, so history stays intact
- ✓ Pull external sources with IMPORTRANGE so the dashboard reads live from where data is owned
- ✓ Share the Dashboard tab as view-only; keep edit access to Raw Data and Calculations
- ✓ QUERY ranges are open-ended (
A:E) so new rows flow through without edits
Share the file view-only with the team and everyone sees the same numbers update as they land — no exports, no emailed screenshots that are stale by the time they arrive. The dashboard becomes the single place to check status, and because the whole chain from raw data to chart updates itself, “is this current?” stops being a question anyone has to ask.
One screen, always current
The build is always the same shape: raw data on one tab, QUERY summaries on another, scorecards and charts on the third. Keep the three jobs separate, point every chart at a summary rather than the source, and share the result view-only. Do that and you have a dashboard that answers the team’s standing questions on one screen and keeps answering them, current, every time someone opens it — no rebuild, no manual refresh, no stale numbers.