Blog

How to Build a KPI Dashboard From a Spreadsheet (Excel or Google Sheets)

Pick KPIs that drive a decision, set up a three-tab sheet, write the formulas, and check the totals. A worked example for Excel and Google Sheets.

7 min read

All posts

Short answer: Choose four to six KPIs, and make sure each one is tied to a decision someone will make. Keep raw data on one tab and formulas on a second tab. The third tab, the dashboard, only references the formulas. Split any average that mixes very different kinds of customers. Then check that the dashboard total matches the raw data total every time you refresh.

The layout is the easy part, and plenty of Excel tutorials cover it well. Most spreadsheet dashboards fail earlier than that: they track numbers nobody acts on, or they show an average that hides what's going on. This guide spends most of its time there.

Step 1: Choose KPIs by the decision they drive

A KPI earns a tile on the dashboard if you can finish this sentence: "If this number moves past X, we will do Y."

KPI Definition Decision it drives
Monthly revenue vs target Sum of revenue in the month ÷ monthly target Whether to push a promotion in the last week of the month
Revenue growth, month over month This month ÷ last month − 1 Whether a change (price, channel, campaign) is working
Online average order value Online revenue ÷ online orders Whether bundles or shipping thresholds are worth testing
Online customer acquisition cost (CAC) Marketing spend ÷ new online customers Whether to keep, cut, or move ad spend
Wholesale share of revenue Wholesale revenue ÷ total revenue How exposed you are to one or two big accounts

If a number doesn't drive a decision (total page views, social followers, "engagement"), leave it off. You can still track it somewhere else.

Step 2: Set up three tabs

Tab Holds Rule
Data Raw rows, one record per row, headers in row 1 Paste or import only. No formulas, no notes.
Calc The selected month and one formula per KPI Every number on the dashboard is calculated here.
Dashboard Tiles and charts Only references to Calc, such as =Calc!B2.

This split means that when a tile looks wrong, there's one place to look.

Step 3: Worked example

Example data, made up for this post. A small business sells online and to a handful of wholesale accounts. Put this on the Data tab starting in A1. Enter Month as the first day of the month, as a real date.

Month Channel Revenue Orders Marketing spend New customers
2026-05-01 Online 18,400 230 2,100 96
2026-05-01 Wholesale 9,600 12 300 1
2026-06-01 Online 19,900 241 2,400 104
2026-06-01 Wholesale 9,100 11 300 0
2026-07-01 Online 17,200 219 2,600 88
2026-07-01 Wholesale 11,800 14 300 2
2026-08-01 Online 21,500 262 2,500 110
2026-08-01 Wholesale 10,200 12 300 1
2026-09-01 Online 22,800 270 2,900 101
2026-09-01 Wholesale 9,900 12 300 0
2026-10-01 Online 24,100 281 3,400 99
2026-10-01 Wholesale 12,600 15 300 2

The Calc tab

Put the month you're reporting on in Calc!B1 (2026-10-01) and the monthly target in Calc!B7 ($35,000). The formulas below work in both Excel and Google Sheets.

Cell KPI Formula Result
B2 Revenue =SUMIFS(Data!C:C,Data!A:A,B1) $36,700
B3 Last month's revenue =SUMIFS(Data!C:C,Data!A:A,EDATE(B1,-1)) $32,700
B4 Growth, month over month =B2/B3-1 12.2%
B5 Online AOV =SUMIFS(Data!C:C,Data!A:A,B1,Data!B:B,"Online")/SUMIFS(Data!D:D,Data!A:A,B1,Data!B:B,"Online") $85.77
B6 Online CAC =SUMIFS(Data!E:E,Data!A:A,B1,Data!B:B,"Online")/SUMIFS(Data!F:F,Data!A:A,B1,Data!B:B,"Online") $34.34
B8 Revenue vs target =B2/B7 104.9%
B9 Wholesale share =SUMIFS(Data!C:C,Data!A:A,B1,Data!B:B,"Wholesale")/B2 34.3%

Add a dropdown to B1 so you can change the month. In Excel, use Data → Data Validation → List. In Sheets, use Insert → Dropdown. Point the list at the month column. Every KPI recalculates when you change it.

The Dashboard tab

Make one tile per KPI. Each tile shows the value, plus last month's value in smaller text underneath. Add two charts:

  1. Revenue by month, with the target as a line. Make it a combo chart: columns for revenue, a line for the target.
  2. Online CAC by month. Use a line chart on its own axis. Don't add it to the revenue chart as a second axis, because two axes on one chart are easy to misread.

Step 4: Split blended averages before you trust them

Here's what the example data shows when you average across both channels:

Month Blended AOV Online AOV
May $115.70 $80.00
Jun $115.08 $82.57
Jul $124.46 $78.54
Aug $115.69 $82.06
Sep $115.96 $84.44
Oct $123.99 $85.77

The blended number jumps in July and October. Nothing changed online in those months. Wholesale simply had a few more orders, each worth hundreds of dollars. In fact, online AOV fell in July. The blended number told the opposite story.

Whenever one group's orders are much bigger or smaller than another's, report the KPI per group. Wholesale and retail, B2B and B2C, and new and returning customers are the usual splits.

Step 5: Read the dashboard, not just the tiles

October looks like a great month: record revenue, up 12.2%, and 104.9% of target. The CAC tile says something else:

  • Online CAC went from $21.88 in May to $34.34 in October. That's up 57% in five months.
  • October's online CAC alone is 20% higher than September's ($28.71).
  • New online customers were flat (96 in May, 99 in October) while marketing spend rose from $2,100 to $3,400.

The growth is coming from existing customers spending more and from wholesale, not from the ad budget. That's the decision this dashboard exists to surface: review the extra ad spend before adding more.

Step 6: Reconcile every time you refresh

Add one check cell to the Calc tab:

=SUM(Data!C:C)-SUMIFS(Data!C:C,Data!A:A,">="&DATE(2000,1,1))

It should return 0. The first part adds up all revenue. The second part adds up only the rows where Month is a real date. If they differ, some row has a month stored as text, and your monthly SUMIFS are quietly skipping it. This is the most common way a pasted export breaks a dashboard. In the example, both sides come to $187,100.

Add a second check for the selected month: the dashboard's revenue tile should equal the filtered total you get from filtering the Data tab by hand. It takes ten seconds and catches most refresh mistakes before anyone sees the dashboard.

When a spreadsheet dashboard stops being worth it

A spreadsheet dashboard is the right tool while the data arrives as one file a month and one person maintains it. Look for something else when you notice any of these:

  • You paste data from three or more sources and then fix the formats every time.
  • More than one person edits the formulas, and the numbers drift.
  • People ask follow-up questions ("why did CAC jump?") that need a new pivot each time.
  • You send screenshots because the sheet is too fragile to share.

That's the point where a tool built for it saves real hours. Chartlet.AI takes a CSV or Excel upload, profiles the columns, and builds the charts automatically. It also answers follow-up questions in plain English and gives you a shareable link instead of a screenshot. Try it on the Free plan (no credit card). Try it with your own spreadsheet.

FAQ

How many KPIs should a dashboard have?

Four to six for a single audience. If you need more, you probably need two dashboards for two audiences.

What's the difference between a KPI and a metric?

A metric is any number you measure. A KPI is a metric tied to a goal and a decision, with a threshold that triggers action.

Should I use a pivot table or SUMIFS?

Use pivot tables for exploring the data and SUMIFS for the dashboard. SUMIFS cells stay in fixed positions, so tiles and charts don't break when the data changes shape.

Can I build this in Google Sheets?

Yes. Every formula in this guide (SUMIFS, EDATE, and basic arithmetic) works in Google Sheets and in any recent Excel version.

What is the best KPI dashboard software for a small business?

Start with the spreadsheet you already have. Move to dedicated software when you hit the signs above: several sources, several editors, repeated follow-up questions, or fragile sharing.

Try it on your own file.

Add a spreadsheet, ask your question, and have your first chart in about a minute.

Free plan · No credit card · Cancel any time