Dashboard Secrets · ~1 min read

Excel Icon Sets: Why Zero Gets a Green Arrow

Add variance arrows from the keyboard, then see the percent-threshold trap that turns a zero green, and where to fix it.

Video link coming soon
What you will learn

Step by step

1. Ahead or behind

Are we ahead of target or behind? A dashboard should answer that with one glance, not with subtraction in your head.

Excel Icon Sets: Why Zero Gets a Green Arrow: Ahead or behind
Ahead or behind

2. The variance

First, the variance. In D1, type Variance. In D2, type equals B2 minus C2, actual minus target.

Fill it down to row four: North is three hundred ahead, South one hundred behind, and West exactly on target.

=B2-C2
Excel Icon Sets: Why Zero Gets a Green Arrow: The variance
The variance

3. Icon sets

Now the icons. Select the variances and press Alt, H, L, I. That is Conditional Formatting, Icon Sets.

Press Enter to take the first set: three coloured arrows.

Excel Icon Sets: Why Zero Gets a Green Arrow: Icon sets
Icon sets

4. The percent trap

Green up for North, red down for South. But look at West: exactly on target, a variance of zero, and it got a red arrow too.

That is the trap, and it gets stranger. Give North a bad month: type one thousand as its actual.

West did not change at all, still zero, but now its arrow is green, as if it were the star of the team.

Icon sets compare each value with the others, in percent of the range, not with zero. Zero was the worst third before, and now it is the best third.

The fix lives in Manage Rules: press Alt, H, L, R.

Edit the icon set rule and change its type from Percent to Number, with green from zero and above. Then the arrows follow the target, not each other.

Arrows, traffic lights or flags: which icons do you use in your reports? Tell me in the comments.

Excel Icon Sets: Why Zero Gets a Green Arrow: The percent trap
The percent trap
What do you want to learn next? Tell us in the comments on YouTube: your requests decide the next tutorials.

← Previous tutorial: One Excel Chart, Any Region: Drop-Down + FILTER
Next tutorial: Excel's Live Picture Trick for Dashboards →