KPI Dashboard Template (Excel and Google Sheets)

A KPI dashboard template gives a team a repeatable page that shows its key measures against targets, with the same layout every month and formulas that update when new data is entered. This page describes a simple KPI dashboard you can build yourself in Excel or Google Sheets in about two hours: the tabs, the layout, the formulas and a worked example with checked arithmetic. We show the structure inline and do not offer a downloadable file here. It puts the ideas of business analytics for finance and operations into a working form, builds on the KPI tracking template and financial dashboards, and follows dashboard mistakes and real-time vs. batch reporting. The dashboard supports the monthly cycle in budgeting and forecasting.

Structure

The Dashboard tab is for presentation. All calculations live on the Calc tab, so the layout stays clean and the logic can be audited.

The data layout

On KPI_Data, use a flat table with these columns: Month (the first day of the month as a date), KPI, Actual, Target. Add one row for each KPI each month. A flat layout lets you add months without changing formulas, and it works with PivotTables. On KPI_List, record for each KPI its owner, unit (percent, dollars, days), direction ("Higher" or "Lower" is better) and the amber threshold (for example 95 percent of target). Name the data range as a table, such as tblKPI.

Dashboard layout

A workable layout has five zones.

1. Header: title, report month (chosen from a drop-down), the data refresh date and the check status.
2. Tiles: six to eight KPI tiles in a row or grid. Each shows the KPI name, the actual, the target, the variance and a status color.
3. Trend charts: two or three line charts for the most important KPIs, with actual and target lines over 12 months.
4. Summary table: all KPIs with actual, target, variance percent, status and owner, sorted by status.
5. Comments: a text area for the three issues that need decisions.

The formulas

Assume the report month is in cell B2 on the Calc tab.

Wrap divisions in IFERROR to avoid error values. Use conditional formatting on the status column and on the tile backgrounds so that the colors follow the status text.

A worked example

Suppose the report month data is as follows (hypothetical).

Check each attainment: 4.35 / 4.80 = 0.906; 38.2 / 39.0 = 0.979; 96.5 / 96.0 = 1.005; for the lower-is-better KPI, target divided by actual, 45 / 49 = 0.918; 83.0 / 85.0 = 0.976. With an amber threshold of 0.95, the first and fourth are red (below 0.95), the second and fifth are amber (between 0.95 and 1.0) and the third is green. The summary shows one green, two amber and two red, and sorting by status puts the two reds at the top for discussion. The tile for revenue would read: actual $4.35M, target $4.80M, variance -$0.45M (-9.4 percent), red.

Trend charts

Build a small table on the Calc tab with the last 12 months of actual and target for each charted KPI, using SUMIFS with EDATE($B$2, -n) for each month offset. Chart the table as a line chart with actual as a solid line and target as a dashed line. Link the chart title to a cell so that it states the finding, for example "Revenue has been below plan for three months." A formula such as =IF(COUNTIF(status_range_last3,"Red")=3, "Revenue below plan for three months", "") can generate it automatically.

The Checks tab

• Every KPI in KPI_List has a row for the report month.
• No actual or target is blank or text.
• No percentage is above 100 where that is impossible.
• The number of KPIs on the dashboard equals the number on the list.
• The report month is in the data.

Show "All checks passed" or "Check data" on the dashboard header, and make it red when any test fails.

How to use it

1. Enter the month's actuals and targets in KPI_Data by a fixed date.
2. Select the report month on the Dashboard.
3. Confirm that the Checks tab passes.
4. Add comments to every red and amber KPI.
5. Save a copy named with the month, and export a PDF if needed.
6. Once a year, review the KPI list and the targets.

Extending the template

Add a department filter, a year-to-date view, a second page for each department, sparklines in the summary table or a data connection that fills KPI_Data from your systems automatically. As the number of KPIs and users grows, move the data to a database and show the dashboard in a business intelligence tool. See KPI scorecards.

Want a working version?

If you would like a KPI dashboard built around your own measures and systems, with automatic data refresh and reconciliation checks, contact us. It is part of our Analytics and Planning Automation work, which includes Excel, Power BI and Tableau.