Excel – Conditional Formatting: Applying Rules Directly to Cells
You have a spreadsheet full of data that is regularly, if not continuously being updated and rather than have to scan all the records manually every day, you’d prefer to have some sort of flag or identifier that tells you certain records or cells are above/below a target figure, or perhaps payment of an invoice is late. If you only have a handful of lines to check you could get away with doing it manually. And let’s be honest here, who hasn’t manually coloured in cells to represent some status or other. But, of course this is unbelievably tedious when you have hundreds of records to update and a likely consequence of this is errors and ultimately loss of control over your data. The more records you have, the more likely you are to miss something – the worse it is likely to get over time.
So how can you get all this to happen automatically?
This is where CONDITIONAL FORMATTING comes to the rescue.
Conditional formatting is a set of rules you can create to automatically highlight cells by changing their colour based on their values or rules looking at other cells , or if you are using 2007 or later, colour bars and icons!
Let’s start with the easier of the 2 rules – applying them directly to the cells and their contents.
If you are using 2003 or earlier, unfortunately you are limited to 3 rules per cell. With later versions you pretty much have no limit although setting up lots of rules may get a bit confusing.
I’ll be using the 2007/2010 format as this gives you many more options to work with and is generally easier when applying rules directly to cells.
Let’s take a simple example….
We have a number of values and might want cells to be highlighted if certain conditions are met;
- Cells with values over 500
- Cells with values under 100
First of all highlight all the cells you want to apply a conditional format to. You could apply it to one cell to start with and then try and copy the format across, but personally I find it better to apply rules to a block in one go. At least like that I know that the rule has been applied to everything from the start rather than try to fill gaps later on.
Click on Conditional Formatting on the HOME tab.
You are then faced with an array of choices…..
This is less daunting than it looks. In fact they have simplified the whole process quite well for basic conditional formatting…just trust me on that one.
So the first rule we wanted to apply was to highlight cells with a value over 500.
Click on HIGHLIGHT CELL RULES, and then click on GREATER THAN.
Excel tries to be helpful and ‘suggests’ a value. Simply overwrite it with whatever value you want, or in this example – 500.
Pick a format from the left hand drop down list. There are a few presets you can use, or go to custom format and pick your own format style. Click OK.
And hey presto! There you have all cells with a value over 500 now shown in light red with dark red text. Simples!
Now apply the second rule. Do not click anywhere or you will lose your selected cells.
Go back to conditional formatting, but this time select LESS THAN. Enter the lower value, then make sure you pick a different format to the first rule…sounds obvious but you’d be surprised, so that you can clearly see the different conditions in your table.
Now, if any of the values change in the cells, depending on the condition they meet, the colours will automatically change to reflect that condition.
To remove any rules, go back to conditional formatting and click on CLEAR RULES, then select whether you want to clear rules on the selected cells only, or the entire sheet.
All the other rules under HIGHLIGHT CELL RULES are pretty self explanatory. Each one when selected has its own set of options e.g. A DATE OCCURING allows you to select from Yesterday/Today/Next Week etc.
One brilliant option that was added from 2007 is the DUPLICATE VALUES option. Previously you had to write a formula to identify duplicates, now you can choose to highlight duplicates or unique values. Simply pick a format and Excel does the rest.
Again, does what it says on the tin. You can pick out your top/bottom 10, top 5, top/bottom however many values you want, base your search on percentages or pick out values over or under the average of the selected cells. Couldn’t be simpler!
Also new since 2007 is the introduction of DATA BARS, COLOR SCALES (apologies for the spelling) or ICON SETS. Please note that if you use any of these and save the file in an earlier format you will lose all this formatting as it is not compatible with earlier versions of Excel. These are colourful, some would say distracting, some may be even more disparaging….however, your manager will love them. Pretty colours and icons that tell a thousand words and save anyone having to actually look at details (perish the thought!). Cynicism aside, they do stand out and can serve a purpose if used sensibly i.e. apply them to a single row or column rather than an entire spreadsheet with 1000s of cells.
All it does is look at the highest and lowest values in the data set and shades the cell based on where the cell’s value falls between those to points. Choose from a variety of preset colours.
Not my favourite as it is not as distinct as some of the other formats. Although it is a traffic light system of sorts it has multiple shades within each colour…but it’s up to you If you want to use it or not. Again, you have a number of colours to choose from.
This is the one that will probably make you your manager’s best buddy. Not particularly scientific how it works out which icon to apply – rule of thirds. Top third is green, middle third is amber and the bottom third is red (or whatever equivalent icon is used in the set). Where there are four icons in the set, each one represents 25% of the value range.
All settings for the data bars/colour scales/icons can be edited but I will show you how in another blog. For now, let’s just stick to some standard rules/settings.
So now you have the tools to make your spreadsheets spring to life and alert you automatically as data is changed. So if you have been manually colouring cells until now…..no need. Head straight for the CONDITIONAL FORMATTING button and set up those rules!