Basic Conditional Formatting in Excel

Conditional formatting is a great way of adding at-a-glance analysis to your Excel spreadsheet, whether it be with traditional RAG (red, amber, green) status reporting or with the latest traffic light icons.

In this blog we shall look at a simple RAG system based on a set of numbers, but once you've mastered the principle, the limits are set only by your imagination and good taste!

Consider the set of numbers below:

On its own, this set of numbers isn't easy to analyse by value, especially if it is not appropriate to sort the list based on these cells.

Creating a Conditional Formatting Rule

To create a conditional formatting rule, start by selecting all the cells you might want to format, whether or not the value is currently within the range you are dealing with. So in our example, the range A1:A15 would be selected.

Then, on the Home tab, click on the Conditional Formatting button, hover over Highlight Cells Rules and move to the right to click on Greater Than...

Once the dialog box opens, put in the appropriate settings

Even before you close the dialog box you should see the changes take place in the background, but click on OK and then we can deal with the middle range of values.

Making sure that you've still got the whole block of cells selected, follow the above steps but this time click on Between...

This will cover values of 10 to 20 inclusive, more on that later!

Finally, we can deal with the low values, those that are Less than... 10:

Our final formatting rule for the lowest values

So far, so good! However one thing to remember with conditional formatting is to check which group your threshold values fall into - i.e., is 20 going to be a green or a yellow?

Currently, a value of 20 would show up as yellow rather than green - this is partly because the yellow formatting rule has a higher priority than the green.

Managing Rules

To check or modify your conditional formatting rules, click on Conditional Formatting and select Manage Rules... at the bottom of the menu:

NB: "Rule (applied in order shown)"

This shows that a value of 20 falls into the yellow category and that the green category starts above and not including 20.

First, reorder the rules with the up and down arrows in the dialog box so that green is at the top, yellow in the middle then red at the bottom. Then double click on the green rule and edit it as follows:

Fine tune your conditional formatting by using the Edit Rule... option

Click OK, and the conditional formatting dialog box should now resemble this:

The final settings for our conditional formatting

Click OK again on this dialog box, and congratulations, your conditional formatting should be set!