Google Sheets KPI Dashboard Template (Free) — Step‑by‑Step Guide
If you’ve ever been asked to “put together a quick dashboard” for a Monday morning standup, you know the drill: scramble through tabs, copy numbers into slides, and pray nothing breaks before you present. I built this KPI dashboard template after doing exactly that one too many times. It takes about 20 minutes to set up, and once it’s running, your numbers update themselves.
This guide walks you through the full setup — from copying the template to connecting your own data. No add-ons, no paid tools. Just Google Sheets.
What You’ll Get
The template gives you a single-page dashboard view with auto-updating charts, a data input sheet where you paste or import your raw numbers, and a model layer in between that does the calculations. Think of it as three layers working together: Data → Model → View.
Most dashboards I see people build in Sheets skip the model layer entirely. They put formulas right next to their raw data, which works fine until someone accidentally sorts a column and everything breaks. Keeping these three layers separate means your dashboard stays intact even when the underlying data changes shape.
How the Three Layers Work
Data is where your raw events or aggregate metrics live. This could be anything — daily revenue numbers exported from Stripe, weekly signups pulled from your CRM, or even manually entered figures. The only rule: keep it clean and consistent. One row per time period, one column per metric.
Model is the middle layer most people skip. This is where you standardize data types, calculate KPIs (like month-over-month growth or conversion rates), and prepare tidy tables that your charts can reference. I use ARRAYFORMULA heavily here so that new rows of data automatically flow through the calculations without you touching anything.
View is the dashboard itself — pivots, charts, and dropdown controls that let you filter by date range or category. This sheet only contains references to the Model layer, never raw data. That’s what makes it resilient.
Setting Up the Template (Step by Step)
Step 1: Copy the Template
Open the template link below and go to File → Make a copy. This gives you your own editable version. Rename it something useful — I usually go with [Team] KPI Dashboard — [Quarter].
→ Copy the KPI Dashboard Template
Step 2: Replace the Sample Data
Go to the Data tab. You’ll see sample rows with dates, revenue, signups, and churn numbers. Delete the sample data (keep the header row) and paste in your own. A few things to watch for:
- Dates should be in a consistent format. I recommend
YYYY-MM-DDbecause Sheets handles it reliably across locales. - Numbers should be plain numbers, not formatted as currency or percentages in the raw data. The Model layer handles formatting.
- No blank rows in the middle of your data — this trips up
ARRAYFORMULAand chart ranges.
Step 3: Adjust the Model Formulas
The Model tab has formulas pre-built for the sample metrics. If your column layout matches the sample, you’re done — the formulas will just work. If you’ve added or removed columns, you’ll need to update the cell references.
Here’s the core formula pattern used throughout the Model tab:
=ARRAYFORMULA(IF(Data!A2:A="","",Data!B2:B/Data!C2:C))This divides column B by column C for every row that has a date. Swap in your own columns as needed. The IF wrapper prevents #DIV/0! errors on empty rows.
For month-over-month growth, I use this pattern:
=ARRAYFORMULA(IF(A3:A="","",(B3:B-B2:B)/B2:B))Format the output column as a percentage. Now you have automatic growth rates that update as new data comes in.
Step 4: Customize the Dashboard Charts
Switch to the View tab. The charts are already linked to the Model layer. To change what’s displayed:
- Double-click any chart to open the editor.
- Under Data range, point it to the relevant columns in the Model tab.
- Adjust chart type if needed — I find line charts work best for trends over time, and bar charts work better for category comparisons.
Step 5: Add a Date Filter (Optional but Useful)
If you want to filter the dashboard by date range, add two cells at the top of the View tab for “Start Date” and “End Date.” Then wrap your chart data ranges with FILTER:
=FILTER(Model!A:B, Model!A:A >= $B$1, Model!A:A <= $B$2)This lets anyone viewing the dashboard adjust the time window without touching the underlying data.
Tips from Using This in Production
After using variations of this template across three different teams, a few lessons stand out:
Keep your Data tab append-only. Don’t edit historical rows. If you need to correct something, add a new row with an adjustment. This makes it easy to audit later.
Name your ranges. Instead of referencing Model!B2:B200, use Data → Named ranges to create names like monthly_revenue. Your formulas become self-documenting: =SUM(monthly_revenue) is much clearer than =SUM(Model!B2:B200).
Set up a weekly import. If your data comes from an external source, use Extensions → Apps Script to write a simple UrlFetchApp script that pulls fresh data on a schedule. Even a basic script that hits a CSV endpoint and pastes the results saves you 15 minutes a week.
Common Problems and Fixes
Charts show “No data”: Usually means the chart range is pointing to empty cells. Check that your Model formulas are populated and that the chart’s data range matches.
Numbers look wrong after pasting: Sheets sometimes interprets pasted numbers as text. Select the column, go to Format → Number → Number, and the formulas should pick them up.
Dashboard loads slowly: If you have thousands of rows, consider summarizing in the Model tab (monthly aggregates instead of daily rows) and pointing charts at the summary.
FAQ
Can I share this as a view-only link? Yes. Go to Share → Anyone with the link → Viewer. They’ll see the dashboard but can’t edit your data.
Does this work with Excel? The core structure works, but some formulas like ARRAYFORMULA and FILTER need to be replaced with their Excel equivalents (Ctrl+Shift+Enter arrays or FILTER in Excel 365). The logic is the same.
How often does the data refresh? Sheets recalculates formulas in real time whenever the Data tab changes. If you’re pulling from an external source via Apps Script, set the trigger to run hourly or daily depending on your needs.