Free Excel template

Free Excel KPI scorecard template

Q: How do I calculate % of target in Excel?

Divide actual by target: =D8/C8, formatted as a percentage. For KPIs where lower is better, divide target by actual instead, so 3.2 hours against a 4 hour target scores 125% rather than 80%.

For managers and owners who report a handful of key numbers every month and want one honest summary. Enter each KPI's target and actual and say whether higher or lower is better; the template scores every KPI against its target, labels it On track, At risk or Off track, and rolls everything up into a weighted overall score.

cellwise-kpi-scorecard-template.xlsxExcel workbook · 5.1 KB · works in Excel, Google Sheets, Numbers and LibreOffice · no signup

Download free template

Preview and try it

This is the template's sheet, calculated by the same engine as the Cellwise app. Change any number to see the formulas react before you download.

	A	B	C	D	E	F	G	H	I
1	KPI scorecard
2	Period	October 2026
3	On track at (% of target)		1
4	At risk at (% of target)		0.9
5	Score cap per KPI		1.2
6
7	KPI	Better when	Target	Actual	Variance	Score	Status	Weight	Weighted
8	Monthly revenue	Higher	250000	238400	-11600	0.9536	At risk	0.2	0.19072
9	Gross margin	Higher	0.62	0.635	0.015	1.0241935484	On track	0.1	0.10241935484
10	New customers	Higher	120	131	11	1.0916666667	On track	0.1	0.10916666667
11	Customer churn rate	Lower	0.02	0.026	0.006	0.76923076923	Off track	0.1	0.076923076923
12	Cost per lead	Lower	38	44.1	6.1	0.86167800454	Off track	0.05	0.043083900227
13	Website conversion rate	Higher	0.03	0.034	0.004	1.1333333333	On track	0.05	0.056666666667
14	On-time delivery	Higher	0.95	0.915	-0.035	0.96315789474	At risk	0.15	0.14447368421
15	First response time (hours)	Lower	4	3.2	-0.8	1.2	On track	0.1	0.12
16	Customer satisfaction (CSAT)	Higher	0.9	0.88	-0.02	0.97777777778	At risk	0.1	0.097777777778
17	Days sales outstanding	Lower	40	47	7	0.85106382979	Off track	0.05	0.042553191489
18	Overall					0.9837843188	At risk	1	0.9837843188
19
20	On track		4

How the formulas work

Score that works for lower-is-better KPIs

F8=IF(A8="","",IF(B8="Lower",IF(D8<=0,$C$5,MIN($C$5,C8/D8)),IF(C8<=0,0,MIN($C$5,D8/C8))))

For higher-is-better KPIs the score is actual divided by target. For lower-is-better ones (churn, cost per lead, response time) it is flipped to target divided by actual, so beating the target still scores above 100%. Churn of 2.6% against a 2% target scores 77%. A zero or negative actual on a lower-is-better KPI gets the maximum score.

Cap scores so one KPI cannot hide the others

F15=IF(A15="","",IF(B15="Lower",IF(D15<=0,$C$5,MIN($C$5,C15/D15)),IF(C15<=0,0,MIN($C$5,D15/C15))))

MIN with the cap in C5 stops any KPI from scoring more than 120%. Without it, a response time of 3.2 hours against a 4 hour target, or a small KPI that happens to triple, would pull the weighted total up and mask the misses elsewhere.

Status from editable thresholds

G8=IF(F8="","",IF(F8>=$C$3,"On track",IF(F8>=$C$4,"At risk","Off track")))

At or above 100% is On track, between 90% and 100% is At risk, below 90% is Off track. Change C3 and C4 to match how strict your business is. Revenue at 95% of target shows At risk in the sample.

Weighted overall score

F18=IF(SUM(H8:H17)>0,SUM(I8:I17)/SUM(H8:H17),0)

Each KPI's score times its weight, added up and divided by the total weight. Dividing by SUM(H8:H17) keeps the result right even if the weights do not add up to exactly 100%, and C24 tells you when they do not.

Name the weakest KPI

C23=INDEX(A8:A17,MATCH(MIN(F8:F17),F8:F17,0))

MIN finds the lowest score, MATCH finds its row, and INDEX returns that KPI's name: the first thing to discuss in the review. If two KPIs tie, the first one in the list is shown.

Make it yours

Color the Status column: select G8:G17, Home > Conditional Formatting > Highlight Cells Rules > Text that Contains, and add rules for On track (green), At risk (yellow) and Off track (red).
Each KPI row has its own number format (dollars, percent, count). When you change a KPI, set the format of its Target, Actual and Variance cells with Home > Number Format.
Variance is actual minus target. For lower-is-better KPIs a positive variance is bad news; read the Score and Status columns for a direction-aware answer.
Keep a monthly history: copy the sheet each month, or add one column per month of scores on a second sheet and chart it with Insert > Line chart.

FAQ

How do I calculate % of target in Excel?

Divide actual by target: =D8/C8, formatted as a percentage. For KPIs where lower is better, divide target by actual instead, so 3.2 hours against a 4 hour target scores 125% rather than 80%.

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

A scorecard compares a short list of KPIs against targets and gives each a status. A dashboard usually adds charts and trends over time. Many teams start with a scorecard like this one and chart its history later.

How many KPIs should a scorecard have?

Few enough that someone can read it in a minute, typically 5 to 12. If a number would not change any decision, leave it off and track it elsewhere.

Functions used

Related guides

More free templates

Updated November 2, 2026