A KPI tile reads faster with a signed label like +12.0% than with a bare number. TEXT formats the absolute variance as a percent and an IF chooses the plus or minus sign, so the label is always clear without custom number formats.
Actual sales of $112,000 against a $100,000 plan show +12.0% and On or above plan.
The example
Three worked examples. Change any yellow input in the practice workbook and the results recalculate.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Case | Actual | Plan | Variance | Status |
| 2 | Region 1 | $112,000 | $100,000 | +12.0% | On or above plan |
| 3 | Region 2 | $94,000 | $100,000 | -6.0% | Below plan |
| 4 | Region 3 | $100,000 | $100,000 | +0.0% | On or above plan |
The formula
Here is the formula, built directly from the inputs:
How it works
Here is the logic, one piece at a time:
B2/C2-1is the variance as a decimal.ABS(...)removes the sign.TEXT(...,"0.0%")formats it as a percent.IF(B2>=C2,"+","-")prepends the sign.
Keep every number in its own input cell so the formula never needs editing.
Try it: interactive demo
Enter the values below.
Variations
Arrow prefix
Add arrows.
Dollar variance
Signed dollar gap.
Pitfalls & errors
A plan of zero returns #DIV/0!; wrap in IF(C2=0,...).
Text labels cannot be summed; keep the numeric variance in another column.
Pair the label with conditional formatting for color.
Practice workbook
Frequently asked questions
Why not a custom number format?
Does 0 count as plus?
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