Conditional formatting is a great tool in Excel. It sounds fancy, but if you’ve ever color-coded anything before, you know how conditional formatting works. Given the rules you provide Excel, it will format things accordingly.
Many conditional formatting features have been added to recent editions of Excel. My favorite is the Color Scale. This feature allows you to look at numbers as colors — a much easier way for the brain to process data.
For our example we have a group of stores’ sales vs. their budget. This is a small data set, but it is still hard to read. The total variance is -4, but how do we account for it? The human brain can only juggle about six numbers or so at a time, so even in this example we are pushing it.
Let’s use color to help. Select the Variance column in your table by clicking on cell D3. This will highlight the whole cell. Then go to Conditional Formatting on your home ribbon. Select “Color Scales.” This gives you many options on how to color your data set. For us, a red-to-green set makes sense. Negative variances are bad, so make them red, and vice versa.
Notice how your data comes to live. You immediately see how the -4 grand total is happening. Store 9 and Store 1 are your outliers, with the rest of the stores cancelling each other out.
Even if you had been able to spot this without the conditional formatting — which I am sure some of you had — think how much easier this is to present to your boss or a store manager.
Leave a Reply