Variance with Up/Down Arrows

Excel Formulas › Dashboards & Reporting

All versionsTEXT

Show period-over-period change as a signed percent with a direction arrow — ▲ +8%, ▼ −3%. One concatenated formula builds the indicator for any KPI on the dashboard.

Quick formula: variance with an arrow and sign:
=IF(curr>=prior, "▲ ", "▼ ") & TEXT(curr/prior-1, "+0%;-0%")
Pick the arrow by direction, then append the signed percent change with a TEXT format.

Functions used (tap for the full reference guide):

TEXTIF

The example

Up 8% vs last period.

AB
1KPIChange
2Revenue▲ +8%

The formula

The formula:

=IF(curr>=prior, "▲ ", "▼ ") & TEXT(curr/prior-1, "+0%;-0%") // arrow + signed percent

How it works

How it works:

  1. curr/prior - 1 is the percent change; IF picks the up or down arrow.
  2. The custom number format "+0%;-0%" forces a leading sign on positive and negative.
  3. Concatenate arrow + change into one cell for a compact dashboard indicator.
  4. Color it with conditional formatting on the sign of curr - prior.

Signed format reads instantly. The TEXT pattern "+0%;-0%" shows +8% and −3% with explicit signs, while a third section (;0%) can show 0% flat. Pair the arrow with a CF color rule and a reviewer sees direction, magnitude, and good/bad in a single glance — the essence of a KPI dashboard.

Try it: interactive demo

Live demo

Current and prior.

Indicator:

Variations

Absolute change

Dollar delta:

=IF(curr>=prior,"▲ ","▼ ") & TEXT(curr-prior, "+#,##0;-#,##0")

Flat band

Near zero = →:

=IF(ABS(curr/prior-1)<0.005,"→ ",IF(curr>prior,"▲ ","▼ ")) & TEXT(curr/prior-1,"+0%;-0%")

Percentage points

For rates:

=TEXT(curr-prior, "+0.0;-0.0") & " pts"

Pitfalls & errors

Signed format. Use "+0%;-0%" so positives show a plus.

Prior of zero. Percent change divides by prior — guard against 0.

Rates vs values. For percentages, show point change, not percent-of-percent.

Practice workbook

📊
Download the free Variance with Up/Down Arrows practice workbook
A variance-arrow sheet with the absolute, flat-band, and points variants, plus 4 challenges with answers. No sign-up required.

Frequently asked questions

How do I show variance with an arrow in Excel?
Concatenate: =IF(curr>=prior, "▲ ", "▼ ") & TEXT(curr/prior-1, "+0%;-0%") for a signed, arrowed change.
How do I force a + sign on positive change?
Use the custom TEXT format "+0%;-0%" — the first section adds the plus.
How do I show a flat indicator near zero?
Add a band: IF(ABS(change)<0.005, "→ ", ...) before the up/down test.

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

Related formulas: Percent change · KPI target indicator · Same period last year

Function references: TEXTIF