Most "dashboard tutorials" give you a colored table and call it done. This guide builds an actual dashboard with KPI summary cards, trend sparklines, interactive dropdowns, and charts, the kind of thing you'd show a client or present to your team.
We'll use a sales dataset as our example, but the patterns apply to any data: marketing, operations, HR, finance.
1 Structure Your Data
Before building the dashboard, your data needs to be clean and structured. Create a tab called _data (the underscore signals "don't touch this") with these rules:
- Row 1 = headers. Every column has a clear name: Date, Customer, Product, Amount, Region, Status.
- No merged cells. Merged cells break every formula.
- Consistent data types. Dates are dates (not text), numbers are numbers.
- No blank rows in the middle. Blank rows break QUERY and chart ranges.
2 Create the Dashboard Tab
Create a new tab called Dashboard. This tab will contain only formulas and charts, no raw data. Format it:
- Set column A width to 20px (margin)
- Hide gridlines: View > Show > Gridlines (uncheck)
- Set background to white
- Freeze row 1 for the header
3 Build KPI Cards
The top of your dashboard should show 4-6 key metrics at a glance. Each KPI needs: the number, a label, and optionally a comparison to last period.
=SUMIFS(_data!D:D,
_data!A:A, ">=" & DATE(YEAR(TODAY()), MONTH(TODAY()), 1)
)
=COUNTIFS(_data!A:A,
">=" & DATE(YEAR(TODAY()), MONTH(TODAY()), 1),
_data!F:F, "Closed Won"
)
=AVERAGEIFS(_data!D:D,
_data!A:A, ">=" & DATE(YEAR(TODAY()), MONTH(TODAY()), 1),
_data!F:F, "Closed Won"
)
=(This_Month_Revenue - Last_Month_Revenue)
/ Last_Month_Revenue
Format the MoM change as percentage with conditional formatting: green for positive, red for negative.
4 Add SPARKLINE Trends
SPARKLINE creates tiny inline charts inside a cell. Perfect for showing trends next to KPI numbers.
=SPARKLINE(
QUERY(_data!A:D,
"SELECT SUM(D)
WHERE A IS NOT NULL
GROUP BY MONTH(A)+1, YEAR(A)
ORDER BY YEAR(A), MONTH(A)+1
LABEL SUM(D) ''"),
{"charttype","line";
"linewidth",2;
"color","#0F9D58"}
)
=SPARKLINE(
{Wins, Total-Wins},
{"charttype","bar";
"color1","#0F9D58";
"color2","#E5E5E5"}
)
5 Add Interactive Filters
Use Data Validation dropdowns to let users filter the dashboard by region, time period, or status.
- Create a cell (e.g., B3) for the dropdown
- Data > Data validation > Criteria: List from range > point to your unique values
- Add an "All" option
=SORT(UNIQUE(_data!E2:E))
Then make your KPI formulas reference the dropdown:
=IF(B3="All",
SUMIFS(_data!D:D, _data!A:A, ">="&G1),
SUMIFS(_data!D:D, _data!A:A, ">="&G1, _data!E:E, B3)
)
6 Build Summary Tables with QUERY
=QUERY(_data!A:F,
"SELECT E, SUM(D), COUNT(A)
WHERE A IS NOT NULL
GROUP BY E
ORDER BY SUM(D) DESC
LABEL E 'Region', SUM(D) 'Revenue',
COUNT(A) 'Deals'", 1)
=QUERY(_data!A:F,
"SELECT B, SUM(D), COUNT(A)
WHERE F = 'Closed Won'
GROUP BY B
ORDER BY SUM(D) DESC
LIMIT 10
LABEL B 'Customer', SUM(D) 'Revenue',
COUNT(A) 'Deals'", 1)
7 Add Charts
Google Sheets charts can reference QUERY output ranges. Build your QUERY summary tables first (they can be in a hidden helper tab), then chart them:
- Revenue by month: Column or line chart from the monthly QUERY table
- Revenue by region: Pie or bar chart from the region QUERY table
- Pipeline funnel: Bar chart showing deals at each stage
Set chart background to transparent, remove borders, and use your brand colors. The chart should look like part of the dashboard, not a floating object.
8 Auto-Update with Apps Script
If your data comes from an external source (API, another spreadsheet, CSV export), automate the refresh with Apps Script:
function refreshData() {
const ss = SpreadsheetApp.getActive();
const sheet = ss.getSheetByName('_data');
// Clear old data (keep headers)
const lastRow = sheet.getLastRow();
if (lastRow > 1) {
sheet.getRange(2, 1, lastRow - 1,
sheet.getLastColumn()).clearContent();
}
// Fetch fresh data from your API
const res = UrlFetchApp.fetch('https://api.example.com/data', {
headers: { 'Authorization': 'Bearer ' + token }
});
const data = JSON.parse(res.getContentText());
// Write to sheet
const rows = data.map(item => [
item.date, item.customer, item.product,
item.amount, item.region, item.status
]);
sheet.getRange(2, 1, rows.length, rows[0].length)
.setValues(rows);
}
// Set up daily trigger at 6 AM
function createTrigger() {
ScriptApp.newTrigger('refreshData')
.timeBased()
.everyDays(1)
.atHour(6)
.create();
}
Design Tips for Professional Dashboards
- Use 2-3 colors max. One accent color (green, blue), black for text, gray for secondary. Don't rainbow your dashboard.
- Align everything. KPI cards should be evenly spaced. Charts should be the same height. Consistent column widths.
- Format numbers properly. Revenue:
$#,##0. Percentages:0.0%. Dates:MMM D, YYYY. Large numbers:$#,##0.0,,"M". - Hide the mechanics. Helper tabs, raw data, and intermediate calculations should be in hidden tabs. The dashboard tab should look clean.
- Protect the dashboard. Right-click the tab > Protect sheet. Let users view but not accidentally edit formulas.
A dashboard is only useful if people actually look at it. Make it clean, make it fast, put it where your team already is: Google Sheets.