KPI Dashboard

About this template

When to use it

A KPI sheet that keeps every measure on one page while the raw numbers accumulate behind it. Three sheets — Dashboard, Input data, Usage guide — and only Input data takes typing.

What the dashboard holds

Everything on the Dashboard is a formula. The top block, “Key metrics — summed automatically from the ‘Input data’ sheet”, holds four cells in B7:E7 — Rows entered, Actual total, Actual average, Actual max — and they update the moment you fill the Actual column, exactly as the note in B8 says. Under it, Summary by department (rows 12–16) splits the same figures across the five team names in column B, and Trend over time (rows 20–25) is hand-typed attainment with a total and an average beneath.

Filling order

1. Rows 2–21 of Input data take No., Metric, Department, Frequency, Actual, Target, Notes (columns A–G); column A is pre-numbered 1–20.
2. Column E must be numeric. The key metrics read only that column, so “3.2%” typed as text drops out — put the unit in the metric name, as the sample does with “Signup conversion rate (%)”, and store the bare number 3.2.
3. Keep Frequency to Weekly, Monthly or Quarterly, as note 6 of the Usage guide asks, and name measures exactly as they appear on the dashboard cards.
4. The department names in B12:B16 of the Dashboard (Service Planning Team, Marketing Team, Customer Support Team, Sales Team, Finance Team) are matched as text against column C. Rename a team and you must change it in both places, or its row reports zero.
5. Replace the trend rows B20:C23 (Jan, Apr, Jul, Oct attainment at 71 · 84 · 79 · 93) and rename the C19 header if the unit is ambiguous.

How the formulas calculate

• Rows entered =COUNT(‘Input data’!$E$2:$E$21), total =SUM(‘Input data’!$E$2:$E$21), average =IFERROR(AVERAGE(‘Input data’!$E$2:$E$21),0), max =IFERROR(MAX(‘Input data’!$E$2:$E$21),0); the IFERROR wrapper returns 0 on an empty sheet.
• Each department row pairs =COUNTIF(‘Input data’!$C$2:$C$21,$B12) with SUMIF and AVERAGEIF over the Actual column, and a share of =IFERROR(C12/COUNT(‘Input data’!$E$2:$E$21),0) — a share of the row count, not of the values.
• Below the trend table, =SUM(C20:C23) and =AVERAGE(C20:C23) — 327 and 81.75 with the shipped values.
• The ranges end at row 21, so row 22 onwards is invisible. Past 20 measures, copy the last row and widen the $E$21 and $C$21 references on the Dashboard.
• Nothing on the Dashboard reads the Target column. To automate an attainment rate, add something like =IFERROR(AVERAGE(‘Input data’!$E$2:$E$21)/AVERAGE(‘Input data’!$F$2:$F$21),0) yourself.

Examples

• Row 2 — 1 · Monthly active users (k) · Service Planning Team · Monthly · 128.4 · 140 · Up since the March redesign.
• Twenty actuals summing to 1,660 give a total of 1,660 and an average of 83.
• The shipped trend values return a total of 327 and an average of 81.75.

Common mistakes

• Typing a unit or a percent sign into the Actual column so COUNT and SUM skip the row.
• Leaving a team in B12:B16 that no longer appears in column C, so its row quietly shows zero.
• Overwriting B7:E7 and destroying the formulas — note 3 of the Usage guide forbids it.
• Inserting a trend row without widening the SUM and AVERAGE ranges.