Analysts are constantly comparing actuals to budget. Because humans are better at processing colors than numbers, it’s often a good idea to add some conditional formatting to your variances.
A common way is to mark negative variances to budget in red and positive in black. This is a time-honored way of formatting numbers, as the sayings “in the red” and “black Friday” demonstrate.
Be sure to subscribe to the newsletter for more Excel tips and tricks.
It’s a little trickier to set up this formatting for percentages in Excel than numbers. Here’s how to do it.
Hitting Ctrl + 1 will launch the Format Cells menu. This lists all the ways a cell in Excel can be formatted.
At the bottom will be the “Custom” option.
Paste the argument:
0.00%;[Red]-0.00%
You may not realize that formatting numbers red vs black is conditional formatting, although Excel does this a little differently. Essentially you have just created an IF statement telling Excel to format negative percentages as red, otherwise black.
Leave a Reply