How to apply Conditional Formatting in Microsoft Excel
This short article looks at the basic settings for creating conditional formatting rules in Excel.

Posted by Michael Allsop on 19 October 2011

You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

Basic Conditional Formatting in Excel 2007

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:

List of numbers in Excel

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...

Conditional Formatting dialog box

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...

Conditional formatting dialog box

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:

Conditional formatting dialog box

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?

Except from the Excel list

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:

Manage Rules dialog box

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:

Edit Rule dialog box

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

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

Conditional Formatting dialog box

The final settings for our conditional formatting

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

In earlier versions of Excel (2003 and before) you could only have up to three conditional formatting rules. In Excel 2007 and 2010, you can have as many as you like - as long as you don't need to save the file in 97-2003 format, that is!


This blog has 0 threads Add post