Show at a glance whether a KPI beat its target with a symbol — ▲ above, ▼ below, ● on target. A nested IF returns the right indicator next to the number.
The example
Actual 112% of target.
| A | B | |
|---|---|---|
| 1 | KPI | Status |
| 2 | Sales vs goal | ▲ +12% |
The formula
The formula:
How it works
How it works:
- Nested
IFreturns ▲ when above target, ▼ below, ● equal. - Pair it with the variance:
actual/target - 1as a percent. - Use conditional formatting to color the symbol green/red by sign.
- Swap symbols for words (“On track”/“Behind”) or icon sets as you prefer.
Color the symbol, not just the text. A green ▲ and red ▼ read instantly on a dashboard. Drive the color with a conditional-formatting rule on the sign of actual - target, so the indicator and its color always agree. Keeping the logic in one IF and the color in one CF rule makes the whole scorecard consistent.
Try it: interactive demo
Actual and target.
Variations
With variance %
Symbol + number:
Words instead
Text status:
Within tolerance
Near = on target:
Pitfalls & errors
Symbols are text. Keep ▲/▼/● in quotes; insert them once and copy.
Color by rule. Use conditional formatting so the color matches the symbol.
Define “on target”. Exact equality is rare — consider a tolerance band.
Practice workbook
Frequently asked questions
How do I show a KPI status arrow in Excel?
How do I add the variance next to it?
How do I color the indicator?
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