Most teams already have the numbers. What they lack is one place to see the handful that matter. Revenue is buried in a billing export, new signups live in a CRM tab, churn sits in a support sheet. Nobody can answer "how are we doing this month" without opening five files and doing mental math.
A KPI dashboard fixes that. It is a single tab that reads from your raw data and surfaces your key metrics as clean tiles: the current value, how close you are to target, and which way the trend is heading. This guide walks through building one in Google Sheets, from picking the right metrics to keeping the whole thing live.
If you want a finished version to start from, grab our free weekly KPI dashboard template and follow along.
Pick the right KPIs
The first mistake people make is tracking everything. A dashboard with 20 tiles is not a dashboard, it is a spreadsheet with extra steps. Keep it to 5 to 8 KPIs, no more. Each one should tie directly to a goal you actually care about this quarter.
A good mix includes both lagging indicators (results that already happened, like revenue or churn) and leading indicators (early signals that predict the result, like demos booked or trial activations). Lagging tells you where you landed. Leading tells you where you are headed.
- Sales team: revenue closed, pipeline created, demos booked, win rate, average deal size
- SaaS product: monthly recurring revenue, new signups, activation rate, churn, net revenue retention
- Marketing: qualified leads, cost per lead, traffic, conversion rate, email signups
- Operations: orders shipped, on-time rate, average fulfillment time, return rate
Pick the five to eight that a manager would actually act on. If a number would not change a decision, leave it off the dashboard and keep it in the raw data where it belongs.
Structure your data
The single most important rule: keep your raw data on its own tab, and have the dashboard read from it. Never type numbers directly into a tile. The moment you hard-code a value, the dashboard is out of date.
Set up a tab called Data with one row per record and clean column headers. For a sales tracker, that might be:
A B C D E
Date Rep Stage Amount Region
2026-06-01 Priya Closed 4200 West
2026-06-01 Marcus Demo 0 East
2026-06-02 Priya Closed 1850 West
2026-06-03 Lena Closed 9600 North
Keep dates as real dates, amounts as plain numbers with no currency symbols mixed in, and categories spelled consistently. A second tab, Dashboard, will hold the tiles and pull everything it needs from Data with formulas. When new rows land in Data, the tiles update on their own.
Build the KPI tiles
Each tile answers three questions: what is the number, how does it compare to target, and which way is it moving. We will build all three.
The value
The headline number is just a filtered sum or count of your raw data. Use SUMIFS when you are adding up an amount and COUNTIFS when you are counting rows. Both let you filter on multiple conditions, like "closed deals in the West region this month."
=SUMIFS(
Data!D:D,
Data!C:C, "Closed",
Data!A:A, ">="&DATE(2026,6,1),
Data!A:A, "<="&DATE(2026,6,30)
)
=COUNTIFS(
Data!C:C, "Closed",
Data!A:A, ">="&DATE(2026,6,1),
Data!A:A, "<="&DATE(2026,6,30)
)
The first formula returns total revenue from closed deals in June. The second counts how many deals closed. To make the month dynamic instead of typed in, replace the dates with references to a cell holding the start of the current month, for example EOMONTH(TODAY(),-1)+1 for the first day and EOMONTH(TODAY(),0) for the last.
Progress to target
A raw number means little without context. Put your goal in a cell, divide the actual by the target, and you get a percentage that tells you at a glance whether you are on track. Wrap it in IFERROR so an empty or zero target shows a clean value instead of a #DIV/0! error.
=IFERROR( B4 / C4, 0 )
// B4 = actual (your SUMIFS result)
// C4 = target for the period
// Format the cell as a percentage.
// 0.87 displays as 87% of goal.
If you want the amount remaining instead of the percentage, use =MAX(0, C4 - B4) so it never shows a negative number once you pass the goal. Place the percent-to-target right under each headline value so the comparison is impossible to miss.
The trend
A single number hides momentum. A small trend line shows whether things are climbing or sliding. The fastest way to add one is SPARKLINE, which draws a tiny inline chart inside one cell. Feed it a row of recent values, like the last weeks of revenue.
=SPARKLINE(
G4:M4,
{"charttype","line";
"color","#0F9D58";
"linewidth",2}
)
// G4:M4 holds the last 7 periods of values.
If you would rather see the underlying numbers, use QUERY to build a small grouped trend table that you can read or chart. This one totals closed revenue by week:
=QUERY(
Data!A:D,
"SELECT YEAR(A)*100 + WEEK(A), SUM(D)
WHERE C = 'Closed'
GROUP BY YEAR(A)*100 + WEEK(A)
ORDER BY YEAR(A)*100 + WEEK(A)
LABEL SUM(D) 'Revenue'",
1
)
Point a SPARKLINE at the second column of that result, or drop a small line chart on top of it, and each tile now has a living trend.
Make it readable
A dashboard people trust looks calm and reads in seconds. Three things get you most of the way there.
For the on-target coloring, select your percent-to-target cells, open Format, Conditional formatting, and add a rule with Greater than or equal to 1 filled green, then a rule for values below 0.8 filled red. Anything in between stays neutral. Now a manager can scan the column and instantly see what needs attention.
Keep it live
The point of all those formulas is that the dashboard never goes stale. Because every tile reads from the Data tab, new rows flow straight through. You can append data manually, paste in an export each week, or pipe it in from a Google Form or an automated import. The tiles recalculate the moment the data lands.
For a recurring push, a few lines of Apps Script can email a snapshot of the dashboard on a schedule, so the team sees the numbers without opening the file. A simple time-driven trigger can run a function every Monday morning that grabs the key cells and sends them to a list. That is optional, but it turns a dashboard people forget into a report that shows up.
When you share the file, share it read-only with everyone except the one or two people who maintain the data. Use the green Share button, set viewers to "Viewer," and they get the live numbers without the ability to break a formula. The dashboard stays accurate because only a small group can touch the inputs.
A KPI dashboard is not about having more numbers. It is about having the right few, live, in one place, so the next decision is obvious.
Start with five KPIs, one Data tab, and three formulas per tile. Add conditional formatting, share it read-only, and you have a dashboard your team will actually open. When you are ready to skip the setup, our free weekly KPI dashboard template gives you the whole structure ready to fill in.