Build a KPI dashboard in Google Sheets

To build a KPI dashboard in Google Sheets, keep the export, targets, calculations and display on separate tabs, all controlled by one selected month. Calculate current results, prior-month changes and target gaps before adding charts. A dashboard is ready for review when its figures reconcile to the reporting workbook that supplied them.

Start with the source rows and definitions

This example is a US distributor's monthly management report. Revenue is net of discounts and returns; cost of goods sold uses the same closed periods. Orders are completed orders counted once within their assigned region. Finance owns the revenue and margin definitions, while operations confirms the order counts.

Use four tabs named Data, Targets, Calc and Dashboard. For the equivalent workbook approach, see how to build a dashboard in Excel. Covirage is not affiliated with Google or Microsoft.

Paste this example into Data!A1:E7, with actual dates in column A. Each source row represents one region and month; the total row below is a reconciliation reference and must stay outside the data range.

Paste the approved targets into Targets!A1:D4. One row per month prevents a company target from repeating on every regional source row.

The KPI examples guide helps define other measures before you add them. Keep sales-specific layouts in the separate Google Sheets sales dashboard.

Add a Sheets month dropdown

Select Dashboard!B2, open Data > Data validation and choose Dropdown from a range, using Targets!A2:A4. Google's dropdown instructions document the range option and rejection of invalid entries. Keep a single selection and format the cell as mmm yyyy.

Choose September 2026. In Calc!B1, enter =Dashboard!B2; in B2, enter =EDATE(B1,-1). These are the selected and prior months. Using dates rather than typed month names keeps September in one year separate from September in another.

Calculate the selected and prior months

Use column A for metric names, B for current actual, C for prior actual, D for target, E for target gap and F for monthly change. Put Revenue on row 5, COGS on row 6, Gross profit on row 7, Gross margin on row 8 and Orders on row 9.

Enter this revenue formula in B5:

=SUMIFS(Data!C2:C7,Data!A2:A7,">="&$B$1,Data!A2:A7,"<"&EDATE($B$1,1))

Google's SUMIFS documentation describes summing rows that meet multiple criteria. The date boundaries include the selected month and exclude the next one.

For B6, use the same formula with Data!D2:D7 as the sum range. For B9, use Data!E2:E7. Calculate gross profit and margin:

B7: =B5-B6
B8: =IF(B5=0,NA(),B7/B5)

For the prior-month column, use this in C5, then replace its sum range with column D for C6 and column E for C9:

=SUMIFS(Data!C2:C7,Data!A2:A7,">="&$B$2,Data!A2:A7,"<"&EDATE($B$2,1))

Set C7 to =C5-C6 and C8 to =IF(C5=0,NA(),C7/C5). A missing prior period must appear as unavailable; a division error replaced with zero would manufacture a comparison.

Bring in targets and compute comparisons with these formulas:

D5: =SUMIFS(Targets!B2:B4,Targets!A2:A4,$B$1)
D8: =SUMIFS(Targets!C2:C4,Targets!A2:A4,$B$1)
D9: =SUMIFS(Targets!D2:D4,Targets!A2:A4,$B$1)
E5: =B5-D5
E8: =(B8-D8)*100
E9: =B9-D9
F5: =IF(C5=0,NA(),B5/C5-1)
F8: =(B8-C8)*100
F9: =IF(C9=0,NA(),B9/C9-1)

E8 and F8 are numeric percentage-point differences. Format them as numbers with a pp label, rather than applying percentage formatting again. Format F5 and F9 as percentages.

Display the results, then prove them

Make the dashboard cells direct references to Calc, such as =Calc!B5 for revenue. For September, the results should be:

September gross profit is 360,000 minus 217,500 = $142,500; August gross profit is 330,000 minus 200,700 = $129,300. Divide each by its own revenue to obtain the displayed margins. Monthly revenue change is 30,000 / 330,000, and order change is 200 / 2,600. Calculations retain full precision; the table rounds percentages and point gaps as shown.

Compare =SUM(Data!C2:C7) with the source control of $990,000, column D with $604,200 and column E with 7,900 orders. Every difference must be zero. Also verify each month has one target row; =COUNTIF(Targets!A2:A4,Calc!B1) must return 1.

For a trend chart, create a three-row monthly calculation table using the same date-boundary formulas. Select it and use Insert > Chart, following Google's chart setup instructions. Keep dollar and percentage measures on separate charts. The existing KPI dashboard Excel template offers another layout reference.

Where it goes wrong

• Rows outside formula ranges. Extend every referenced range when the export grows, then repeat the control-total check.
• Targets duplicated across regions. Keep company targets at their own grain. SUMIFS will add duplicate target rows without warning.
• Average regional margins. Total gross margin is summed gross profit divided by summed revenue; the regional percentages are not equally weighted.
• Incomplete month comparisons. Compare closed months or label partial periods explicitly. A partial September against full August misstates the movement.
• Color without a defined direction. Higher is favorable for these three measures. A cost or defect KPI needs the opposite status rule.

Explain the movements from your own file

Bring your reporting workbook or Google Sheet, including source rows and targets, to Covirage. Covirage maps the columns, and its tools compute and check the figures; the external AI model explains the movements with every figure traced to its rows. Create a free Covirage account, which takes about a minute, and bring a sample of your own data.

See Covirage for board reporting for the supporting business use.