The EZ guide to conditional formatting: Part 2
Objective: to make it easier to understand the significance of values in Excel worksheets and to help users focus on key information.
The first part of this series examined the range of different conditional formats available and how to apply them. This time, we'll move on to some practical examples of how to use conditional formatting.
As we saw last time, conditional formatting can be used to apply cell formatting, data bars or a range of icons, to highlight values that fall within certain ranges. It's simple enough to select a range of cells and have Excel apply a range of colours or icons to them, but this might not be the most effective way of directing someone's attention to key values.
In this example, we want to concentrate attention on expenses that exceed a particular threshold. Here we have just applied a default colour scale to the expenses cells:
We will look at an alternative method where we add a more subtle indication of those values that we need to worry about. First, we make a copy of our expenses values in the column immediately to the left of our existing values and apply one of the Icon Sets conditional formats:
We have also included a threshold value in cell C1 to be used with our format. As we saw last time, we can go to the Manage Rules option, select our rule and use the Edit Rule option to access the detailed options that control how the format works. Given that we are working with expenses, we might decide to use red rather than green as the high value highlight. We can do this most simply by just using the Reverse Icon Order command:
We are really only interested in highlighting the values above our threshold limit, so, from the 2010 version of Excel onwards, we can use the dropdown for each icon to set the amber and green icons to No Cell Icon:
Industry insightsView more
Next, we need to change our remaining icon so that, instead of applying to the top 33% of values, it uses the value in C1 as its threshold value. We use the Type dropdown to choose Formula rather than Percent, and then click in the Value text box and select cell C1:
When we accept our changes we can see that our highlight icons are now only shown for those cells that have a value greater than or equal to the value in our threshold cell:
However, we still have the actual values displayed in those cells, albeit the column is not wide enough to display them properly. Another detailed setting allows us to use the Show Icon Only option to get rid of the distracting values. We have also narrowed the column width to better fit the icons:
Although it probably comes down to personal preference, you might find that this method does a better job of directing attention to the key figures without reducing the clarity of the numbers themselves in any way.
Changing the threshold value
Because we have entered our threshold value in a cell, rather than including it directly in the conditional format, it's easy to change it. We could also change the basis of the calculation. For example, we could use column B to hold a formula that calculates the percentage of each expense to the expense total and then enter a threshold percentage in C1:
Here we have changed our formula to calculate the value of the individual expense divided by the Total expenses:
We have then changed the value in cell C1 to 15%. Because our conditional format is set up to highlight all values greater than or equal to the value in C1, we don't need to change the conditional format itself.
Detailed rule management
In the above example, we mentioned that the ability to control individual icons was added in Excel 2010. The following example uses some less obvious conditional formatting techniques that can address that issue for Excel 2007 users, and can also be useful for more advanced conditional formats in all versions.
So far, we have only included a single rule for our cells. We can include multiple rules. Where multiple conditional formats are applied to a single cell, or range of cells, additional options become relevant. First of all, we can say that, should a particular rule condition be met, no further rules should be processed. By including a rule that checks if the value in the cell is less than the threshold value, and stops processing any further rules if so, we can apply our icon rule only for values above the threshold – even if all icons are still turned on:
Although 'Stop If True' will intercept any values that would generate orange or green icons, it will also stop the Show Icon Only part of the Icon Sets rule being applied, so the numbers will reappear. We have set the format of our 'interception' rule to show white text on a white background to make those numbers invisible. This use of conditional formatting to make cell contents invisible can be used in a variety of ways, including building peril-sensitive formatting into a workbook.
It is also possible to change the order in which rules are applied. This is a common cause of confusion. It doesn't mean that Excel will apply all the rules from top to bottom. Instead it means that only rules that evaluate as True will be assessed, with the rule closest to the top taking priority. This example demonstrates how this works. If all rules were 'applied in order shown', all values over 200,000 would have a red fill. This is clearly not the case. If we take the value of 347,900 the bottom two rules both evaluate to True. Because the orange rule is above the red rule, the orange fill is applied.
EZ guides – where next?
As we embark upon the next set of EZ Guides, it would be very helpful to hear of any particular areas that you would like us to cover. Please add a comment with any suggestions that you may have.
Simon Hurst is the founder of technology training consultancy The Knowledge Base and is a past chairman of the ICAEW's IT Faculty.