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.

ScreenHunter_46 Jan. 17 08.57

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.


ScreenHunter_46 Jan. 17 08.59

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.

ScreenHunter_46 Jan. 17 09.01

In this window you can change pretty much everything about the rule.

ScreenHunter_47 Jan. 17 09.06

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:

ScreenHunter_47 Jan. 17 09.15

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.

ScreenHunter_47 Jan. 17 09.17

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.

ScreenHunter_47 Jan. 17 09.19

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.

ScreenHunter_47 Jan. 17 09.29

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;

ScreenHunter_47 Jan. 17 09.40

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.

ScreenHunter_47 Jan. 17 09.44

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.



Posted on January 17, 2013, in Conditional Formatting and tagged , , . Bookmark the permalink. Leave a comment.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: