Build a Simple KPI Summary Sheet in Excel

A KPI summary sheet is the simplest kind of KPI dashboard in Excel: one data table, plus a small block of formulas that shows revenue, deals won, win rate and progress to target. You build it with SUMIFS, COUNTIFS and a percent change formula, and you point everything at one input cell so the whole summary updates when you change the month. This guide walks through a complete build you can copy, then shows how to extend it.

What goes on a KPI summary sheet?#

A KPI (key performance indicator) is a number that tells you whether things are going well, such as revenue or win rate. A summary sheet shows a handful of them in one place, calculated from raw data.

The rule that keeps it reliable: data and KPIs live apart. The data table holds one row per deal and nothing else. The KPI block holds only formulas and one or two input cells. You never type a total by hand.

Most summaries answer the same four kinds of question:

Step 1: Set up the data table#

Start with a clean table: one header row, one row per record, no blank rows and no totals mixed in. Here are eight deals across two months:

In real work you would usually have a Date column instead of a Month label. The formulas are the same idea; you just use a date range as the criteria, which our guide to summing by date range or month explains.

Step 2: Add the input cells#

Input cells are the values people change; every KPI reads them. Put them at the top of the summary block:

Now nobody has to edit a formula to see a different month. They change G1 and G2, and everything below recalculates.

Step 3: Write the KPI formulas with SUMIFS and COUNTIFS#

SUMIFS adds the numbers that match every condition, and COUNTIFS counts the rows that match every condition. Lock the data ranges with $ so you can copy formulas between KPI cells safely (press F4 on Windows or ⌘T on a Mac).

How the revenue formula reads: add column D, where column A equals the month in G1 and column C says Won. In SUMIFS the range to add comes first, which is the opposite of SUMIF. The SUMIFS function guide and the COUNTIFS function guide cover each argument, and COUNTIFS and SUMIFS with multiple conditions has more examples.

The win rate divides deals won (3) by all October deals (4). Format G9 as a percentage with Ctrl+Shift+% on Windows or ⌃⇧% on a Mac.

Split a KPI by region#

Add one more pair to break revenue down. North's October revenue:

=SUMIFS($D$2:$D$9,$A$2:$A$9,G1,$B$2:$B$9,"North",$C$2:$C$9,"Won")

The result is 5,100; change "North" to "South" and you get 7,300. The two add up to 12,400, the total in G5. That is a quick check that no row was missed.

Step 4: Add percent change and progress to target#

Percent change compares this month with last month: (new - old) / old. Progress to target divides the actual by the goal.

The parentheses in G7 matter: Excel must subtract before it divides. Always divide by the old value, here last month. Our percent change formula guide explains why and how to handle a zero in the old value.

How to make the KPI sheet easier to read#

A KPI dashboard in Excel only works if people can read it in a few seconds. A few small touches help:

• Format numbers once: currency for money, percentages for rates, no decimals for counts. See format numbers as currency, percent and dates.
• Colour the status: a conditional formatting rule can turn "Behind" red and "On track" green. Conditional formatting in Excel shows the steps.
• Give the month a drop-down list so people can only pick months that exist in the data: create a drop-down list in Excel.
• Add a simple chart of revenue by month if trends matter more than single numbers: make a chart in Excel.
• Avoid #DIV/0! in a month with no deals: =IFERROR(G8/COUNTIFS($A$2:$A$9,G1),0).

How to extend it#

The same pattern grows with your data. Common next steps:

1. More rows: turn the data into an Excel table (Ctrl+T on Windows, or Insert > Table) so ranges grow with new rows. See Excel tables vs normal ranges.
2. Real dates: replace the Month column with dates and use ">="&DATE(2026,10,1) style criteria.
3. People KPIs: the same SUMIFS and COUNTIFS build headcount by department or hours by team. A weekly shift schedule counts coverage the same way, and an employee lookup sheet pulls one person's details by ID.
4. Role-specific numbers: recruiters track open roles and time to hire; sales teams track pipeline and conversion. Our guides to Excel skills for HR and recruiting and Excel skills for sales and operations list the reports each role builds.

Before you share the sheet, check one KPI by hand. Filter the data to October and Won, look at the status bar sum, and compare it with G5.

Practise it#

Write the revenue formula in the exercise on this page, then build the rest of the block yourself. To get fast at the two functions that do most of the work, take the COUNTIFS and SUMIFS lesson, and practise rates and growth in the percentages and percent change lesson.