Excel – Editing & Customising Conditional Format Rules
Now that you know how to create rules that can be applied directly to cell values and rules based on formulas looking to one or more cells, what happens if you want to change them?
Exactly the same as when we set up rules, we first need to highlight the cell or cells whose rules you want to change or add to.
Let’s go back to our earlier example. Instead of highlighting cells with values over 500 we now want to change to over 600. I could delete the existing rules and start again but no need.
Go to CONDITIONAL FORMATTING, then select MANAGE RULES.
We can now see the rules we initially set up. Through this window you can add, edit and delete any existing rules. Click on the rule you want to edit and click on EDIT RULE.
In this window you can change pretty much everything about the rule.
Make your choices and edit the value that will trigger the rule, then make any changes you want to the format and click on OK, then OK again.
However, there is one thing you need to be careful of – the order in which the rules are applied.
Let’s say I want the following rules applied to my table;
- Values greater than 0
- Values greater than 300
- Values greater than 600
Seems like a fair set of rules. BUT….computers being the logical things that they are, stop once a condition is met. So as it checks the cell value and finds it’s greater than 0, it formats it according to the rule. What it then does not do, is check its value against all the remaining rules ‘just in case’. Logically, a human would check further, but the computer is governed by different rules and once something is TRUE, it looks no further. So our table looks like this:
All the selected cells end up formatted the same way, as they are in fact all grater than 0. If you do this or find that your cells do not format as planned check the running order of your rules. Use the arrows to rearrange the order, click on APPLY and see if it works OK.
If your rules involve test on values LESS THAN, start with the lowest value first.
If you want to change the range of cells the rules apply to edit the range value sin the REFERS TO box.
Now that you know how to go in and edit a rule, how about editing the rules for DATA BARS, COLOR SCALES and ICON SETS?
If you remember from my earlier blog, the DATA BARS etc have a simple preset way of calculating how they work. Three picture ICON sets for instance work on rules of thirds by default. By going through our MANAGE RULES we can edit these to suit our own particular requirements.
Using an ICON set as an example, you can change the operator (>/>=), set your own specific cut off value and decide whether you want to base this on percentage of values, a number, a formula or a percentile. You can also change the icon set, although you have to work out what they are from a description rather than an image.
By default it is assumed that a high value is ’good’ and therefore green, but this may not be the case. You therefore have the option to REVERSE ICON ORDER. The final option you have here is SHOW ICON ONLY. This is quite neat as it allows you to show an icon in a cell by itself rather than have both the data and the icon which can look a little messy or crowded. Problem is you cannot use icons etc when applying a formula to your conditional format.
To get around this minor inconvenience, you can add a new column and create an IF statement that puts a value in the cell and you can then apply your icon set based on the values generated by the IF statement. Simple!
Let’s add a new column to our table called ‘Status’ and add a formula;
In this example I only want to highlight totals that are less than 1000 so if the statement returns a FALSE value it displays nothing. Copy this down to all rows in your table.
Now apply a conditional format so that if the value is 1, display a red ‘X’ icon only.
Voilà! By using this method, you avoid a lot of clutter in your spreadsheet and draw the viewer’s attention only to those values that require a closer look.
Try playing with the settings of the DATA BARS etc. Follow the same steps as above, apply a variety of values and see what effect it has on the appearance of the formatting.
And that is CONDITIONAL FORMATTING!