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

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!

We've got masses of blogs, videos and courses on Excel on our site - go to
our Excel training page for links.

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:

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!

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!