Cellwise Start free

Excel guide · 5 min read

KPI formulas in Excel: how to calculate and score KPIs

Quick answerMost KPI sheets need five formulas: percent of target (=Actual/Target, or Target/Actual when lower is better), variance (=Actual-Target and =(Actual-Target)/ABS(Target)), a status such as =IF(score>=1,"Green",IF(score>=0.9,"Amber","Red")), the change from last period, and a weighted overall score with =SUMPRODUCT(weights,scores)/SUM(weights). Keep targets and thresholds in cells so you can change them without touching a formula.

Sample data

Every result on this page was calculated from this sheet by the same spreadsheet engine the Cellwise app uses.

RowABCDEFGHIJ
1KPIBetterTargetActualLast monthWeightScoreStatusGreen at
2Revenue ($)Higher1200001140001080000.30.95Amber1
3New customersHigher8092750.21.15GreenAmber at
4Churn rateLower0.020.0260.0240.20.769Red0.9
5Avg handle time (min)Lower65.56.30.151.091GreenScore cap
6CSATHigher0.850.880.860.151.035Green1.2

Download this sheet as an Excel practice file (.xlsx)

Percent of target

For a KPI where higher is better, divide actual by target. Revenue of $114,000 against a $120,000 target is 95%. Format the cell as Percent. Progress during the month (are we on pace?) and caps at 100% are covered in percent of goal; for sales teams the same idea is quota attainment.

=D2/C2

Result: 0.95 shows as 95% with Percent format

0.95: revenue is at 95% of target.

Lower-is-better KPIs: flip the ratio

Churn, handle time, cost per lead, defects and days to close are better when they are lower. Actual/Target would reward a worse result: churn of 2.6% against a 2% target would score 130%. Divide target by actual instead, so missing the target scores under 100% and beating it scores over 100%.

Column G does this for every row with one formula: =ROUND(MIN($J$6,IF(B2="Lower",C2/D2,D2/C2)),3). The Better column says which way each KPI points, and MIN caps any score at 120% (J6) so one runaway KPI cannot hide the misses in a weighted total. The KPI scorecard template uses the same rule.

=IF(B4="Lower",C4/D4,D4/C4)

Result: 0.76923076923 shows as 77% with Percent format

About 0.77: churn of 2.6% against a 2% target scores 77%.

Variance and variance %

Variance is actual minus target, in the KPI's own units: revenue is $6,000 short. Variance % divides by the target: =(D2-C2)/ABS(C2) gives -5%. ABS keeps the sign right when a target is negative (a planned loss, for example). For lower-is-better KPIs a positive variance is bad news, so read the score or status column for a direction-aware answer. Favorable and unfavorable labels for budgets are in budget vs actual variance.

Be careful with KPIs that are already percentages. CSAT going from 86% to 88% is a change of 2 percentage points, but a 2.3% increase. Say which one you mean in the report.

=(D2-C2)/ABS(C2)

Result: -0.05 shows as -5.0% with Percent format

-0.05: revenue is 5% below target.

RAG status (red, amber, green) with IF

A status word is what people read first. Compare the score with two thresholds kept in cells: green at 100% or more (J2), amber from 90% (J4), red below that. Check the highest threshold first; IF stops at the first test that is TRUE.

Column H holds this formula for every KPI, so =COUNTIF(H2:H6,"Red") counts the KPIs that need attention (1 here: churn). To color the cells, select H2:H6 and use Home > Conditional Formatting > Highlight Cells Rules > Text that Contains, one rule per status. More on nested conditions in nested IF and IFS vs nested IF.

=IF(G2>=$J$2,"Green",IF(G2>=$J$4,"Amber","Red"))

Result: Amber

Amber: revenue's score of 0.95 is between the 90% and 100% thresholds.

Trend vs last period

Being on target and getting better are different questions. The change from last month is (this month - last month) / last month: new customers rose from 75 to 92, up 22.7%. The formulas for negative and zero starting values are in percent change, and the year-on-year version is in year-over-year growth.

For a direction-aware word, test whether the move went the right way: =IF((D4<E4)=(B4="Lower"),"Improving","Worse") returns Worse for churn, because it went up from 2.4% to 2.6% and lower is better. Treat a tie separately if your KPIs often stay flat.

=(D3-E3)/E3

Result: 0.22666666667 shows as 22.7% with Percent format

About 0.227: new customers grew 22.7% from last month.

Weighted overall score

Multiply each KPI's score by its weight, add them up and divide by the total weight. Dividing by SUM(F2:F6) keeps the result right even when the weights do not add up to exactly 100%. Here the five KPIs combine to about 98.8%: amber overall, because churn at 77% pulls the total under 100% even though three KPIs are green. A single total hides which KPI is the problem, which is why the status column matters as much as the score.

Weights are a decision, not a calculation: agree them before the month starts. Scoring options side by side (a 1 to 5 scale, minimums, missing values) are in weighted score and weighted average.

=SUMPRODUCT(F2:F6,G2:G6)/SUM(F2:F6)

Result: 0.9877 shows as 98.8% with Percent format

About 0.988: the weighted score for the month (98.8%), with each KPI capped at 120%.

KPI formulas by job

Each of these guides calculates one KPI from realistic raw data, with the definition choices that change the number. Feed their results into a scorecard like the one above, or download the KPI scorecard template. For tracking over time, see the monthly sales tracker by rep, the marketing campaign performance tracker and the warehouse on-time delivery tracker.

Common mistakes

Using Actual/Target for a KPI where lower is better

It rewards getting worse. Use Target/Actual for churn, cost and time KPIs, or a Better column with IF so one formula handles both.

Averaging percentages from teams of different sizes

A team-level rate is total over total (for example all resolved contacts / all contacts), not the average of each team's rate. Small teams otherwise count as much as large ones.

Thresholds typed inside the IF formula

=IF(G2>=0.9,...) has to be edited in every row when the rule changes. Put the thresholds in cells and lock them with $ signs.

Letting one KPI dominate the weighted score

A KPI at 300% of a too-easy target can hide two misses. Cap each score with MIN (120% here) before weighting.

FAQ

How do I calculate a KPI percentage in Excel?

Divide actual by target (=D2/C2) and format as Percent. If lower is better, divide target by actual instead, so 2.6% churn against a 2% target scores 77% rather than 130%.

What is the Excel formula for RAG status?

=IF(score>=1,"Green",IF(score>=0.9,"Amber","Red")), with 1 and 0.9 better kept in cells. Test the highest threshold first, then color the results with conditional formatting.

How do I calculate a weighted KPI score in Excel?

=SUMPRODUCT(weights,scores)/SUM(weights). Score each KPI as a percent of target first, flipped for lower-is-better KPIs and capped, so they are on the same scale.

What is the difference between a KPI scorecard and a KPI dashboard?

A scorecard lists a few KPIs against targets with a status for each. A dashboard usually adds charts and trends over time. Many teams build the scorecard first and chart its monthly history later.

Functions used

Related guides

Updated