A KPI card shows one big number plus how it changed — “$84,200, up 12% vs last month.” Build it from a couple of formulas and a colored arrow, the building block of any dashboard.
The example
This month vs last, as a single KPI string.
| A | B | |
|---|---|---|
| 1 | Metric | Value |
| 2 | This month | $84,200 |
| 3 | Last month | $75,000 |
| 4 | KPI | $84,200 ▲12% |
The formula
Compose the card text:
How it works
A KPI card is text plus a conditional arrow:
- Show the headline number with
TEXT(value, "$#,##0")for clean formatting. - Compute the delta versus the prior period:
(current − prior) / prior. - Pick an arrow with IF:
IF(current>=prior, "▲", "▼"), then append the percent. - Make the cell big and bold; use conditional formatting to color it green when up, red when down.
Color the whole card: a CF rule =B1<B2 turns the cell red font, and the up-case stays green — so the card reads at a glance. Put several cards in a row for a clean dashboard header.
Try it: interactive demo
Set this period and last.
Variations
Dollar change
Absolute, not percent:
vs target
Compare to a goal:
Color rule
CF: red when down:
Pitfalls & errors
Guard divide-by-zero. A zero prior period makes the percent error — wrap with IFERROR.
Arrows are just characters. ▲/▼ are Unicode glyphs; they print fine but font support varies. Test in your environment.
TEXT returns text. The card string can’t be charted or summed — keep the underlying number in its own cell.
Practice workbook
Frequently asked questions
How do I build a KPI card in Excel?
How do I color a KPI green when up and red when down?
Why can't I chart my KPI card?
Stop fighting formulas. Learn them in a day.
This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.
See the Formulas & Functions Class