1. Conditional formatting of top and bottom values
In this chapter, we will be using some examples where highlighting variances over or under a certain amount will make the important aspects of the data stand out.
2. Variance analysis unformatted
A common use of conditional formatting is to show values that are greater than or less than a certain amount.
You may recognize the format of the data shown. The report is a comparative income statement exported from QuickBooks, a popular brand of accounting software.
We are trying to see what amounts changed significantly from the prior year. In this case, variances of greater than 10 percent are considered material.
There are a lot of numbers on this spreadsheet so the formatting will help the large variances stand out.
3. Greater than 10%
We'll be using the greater than option first. Since you are looking at percentages, type 10 percent in the input box below Greater than.
Keep in mind that 10 percent is really just 0 point 1 with a percentage value formatting applied to it. If you typed 0 point 1 in this box, it would work the same way.
Keep the formatting color set to green. We'll be using green most of the time in this course unless you are applying multiple rules to the same Sheet.
4. Variance analysis formatted
Now that we have applied the conditional formatting, the variances over 10 percent are obvious to the reader.
5. Less than -10%
When you are doing a financial variance analysis, highlighting the increases is only telling half of the story.
While increases are important, you probably want to know the decreases as well. We will do this on the same data by applying another rule to the same range.
Since we will be using a different rule, we can use the red color again.
6. Top and Bottom Variance Analysis
We will use the same process that we did to apply the first formatting rule. The range is the same, but choose less than instead of greater than and use negative 10 percent instead of positive. There are only six default formatting styles. Choose the red background color and click done.
7. Variance analysis greater than and less than
We have now completed a variance analysis that shows the income statement amounts that have fluctuated more than 10 percent or less than negative 10 percent over the year.
This method clearly points out which variances we want to look at further. But, maybe you don't want to be so binary. Maybe you want to indicate the degrees of variation instead of only the amounts over a threshold.
8. Color scale
The color scale option allows you to do this.
We have deleted the previous rules that applied to this range. Now we will go to the Color scale option, which is the other option at the top of the conditional formatting menu. Here we selected the default color scale at the bottom right, which will show the negative amounts in red and the positive in green.
9. Color scale applied
After applying this rule, you can see that a color scale results in a different message to the reader. While the greater than less than rules implied specific variances to focus on, a color scale will suggest varying degrees of interest based on the relative amounts.
10. Let's practice!
Now you know several different ways to emphasize variances to a user. We were using percentages, but the same techniques apply to numbers or dates. Let's try this out and see how it goes!