BLOGS BY TOPIC
BLOGS BY AUTHOR
BLOGS BY YEAR
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
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.
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!
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!