Category Archives: Conditional Formatting
Hopefully you are familiar with applying conditional formatting to cells, and creating rules to apply more complex formatting rules. If not, check out my blog to see how you can apply conditional formats to cells.
However, when it comes to charts there is no in-built conditional formatting functionality where you can create some sort of rule and apply it to a series.
In order to create the “illusion” of conditional formatting on charts we have to create some dummy data based on our base data.
Let’s start with a simple table.
When converted into a chart it gives us this.
As you can see we have a range of products being sold and have different sales figures against each one. To make things stand out a bit more it would be nice to apply some colour coding to the bars of the chart to show which sales are good, bad or indifferent or simply to show on a colour scale how sales are performing.
For this example, let’s say I just want to show a RAG (Red/Amber/Green) system for each product.
First, decide what your break points are going to be i.e. what determines if something should be red, amber or green;
Amber <= 3500
Green > 3500
Once we have our break points we can set up our table to create the dummy data.
Add a couple of new lines above your table and if you want, label them something like Lower and Upper to represent the lower and upper bounds of your range of values e.g. 0 to 1500 for red.
It’s worth also adding some labels so that these appear in your legend when you create the chart so people understand the meaning of the colours. In this example, I used the formula = C2&” – “&C3 to build up the label for the red values.
Now we build up some IF statements to determine which category the actual value from the base data falls into i.e. Product A should be in the amber section. We will start by creating a nested IF statement in cell C5 (in this example) that will test if the sales value for Product A is between 0 and 1500. If it is, then display the number, otherwise show #NA. We need to have #NA as this prevents anything from being plotted in the chart. By fixing certain elements of the formula (see my blog on absolute vs. relative referencing if you are unsure about what I have done) so that the formula can be copied across the columns and then down the rows ensuring it works in all cases.
Once applied to all the cells in the modified table, each sales value for each product should only appear once in the new columns.
Now it’s time to replot the chart but this time using the dummy data.
Highlight all the data apart from the original values.
Use a stacked column chart to display your data and then apply whatever formatting you want to get your end chart.
Because of the way the data has been set up, if any of the values change, the formatting you have applied will automatically change based on the values.
When working with lists of information it is sometimes nice to be able to highlight rows of related information such as all rows for a specific product or salesperson. If you have the latest version of Excel then you have the benefit of SLICERS, but if not then you need a different approach to achieve this. By combining DATA VALIDATION and CONDITIONAL FORMATTING this can be done quite easily.
Let’s take this simple table showing regional sales by product line.
I could use a filter and select the information I need but using a combination of DATA VALIDATION and CONDITIONAL FORMATTING will display it in the table itself whilst still leaving other data visible.
Create a couple of drop down lists using DATA VALIDATION using the values from your list to generate the values to select from. I have left a blank cell at the top of each list so that I can have an option to select ‘nothing’.
Once I have those set up I can set about applying some CONDITIONAL FORMATTING based on the selections made in the two drop downs.
I have set up three rules, one for each drop down list and one to highlight values that meet both selections. Note the use of partial absolute referencing in the formulas to make sure that when the rules are copied across the three columns the reference points remain constant i.e. always look in columns A and/or C.
Formulas are as follows:
=AND($A6=$B$1,$C6=$B$2) to highlight when both product & region criteria match
=$C6=$B$2 to highlight where the product line is matched
=$C6=$B$1 to highlight where the region is matched
And there you have a simple way to highlight data in a list based on the user’s selections in drop down lists.
Now that you know how to create rules that can be applied directly to cell values and rules based on formulas looking to one or more cells, what happens if you want to change them?
Exactly the same as when we set up rules, we first need to highlight the cell or cells whose rules you want to change or add to.
Let’s go back to our earlier example. Instead of highlighting cells with values over 500 we now want to change to over 600. I could delete the existing rules and start again but no need.
Go to CONDITIONAL FORMATTING, then select MANAGE RULES.
We can now see the rules we initially set up. Through this window you can add, edit and delete any existing rules. Click on the rule you want to edit and click on EDIT RULE.
In this window you can change pretty much everything about the rule.
Make your choices and edit the value that will trigger the rule, then make any changes you want to the format and click on OK, then OK again.
However, there is one thing you need to be careful of – the order in which the rules are applied.
Let’s say I want the following rules applied to my table;
- Values greater than 0
- Values greater than 300
- Values greater than 600
Seems like a fair set of rules. BUT….computers being the logical things that they are, stop once a condition is met. So as it checks the cell value and finds it’s greater than 0, it formats it according to the rule. What it then does not do, is check its value against all the remaining rules ‘just in case’. Logically, a human would check further, but the computer is governed by different rules and once something is TRUE, it looks no further. So our table looks like this:
All the selected cells end up formatted the same way, as they are in fact all grater than 0. If you do this or find that your cells do not format as planned check the running order of your rules. Use the arrows to rearrange the order, click on APPLY and see if it works OK.
If your rules involve test on values LESS THAN, start with the lowest value first.
If you want to change the range of cells the rules apply to edit the range value sin the REFERS TO box.
Now that you know how to go in and edit a rule, how about editing the rules for DATA BARS, COLOR SCALES and ICON SETS?
If you remember from my earlier blog, the DATA BARS etc have a simple preset way of calculating how they work. Three picture ICON sets for instance work on rules of thirds by default. By going through our MANAGE RULES we can edit these to suit our own particular requirements.
Using an ICON set as an example, you can change the operator (>/>=), set your own specific cut off value and decide whether you want to base this on percentage of values, a number, a formula or a percentile. You can also change the icon set, although you have to work out what they are from a description rather than an image.
By default it is assumed that a high value is ’good’ and therefore green, but this may not be the case. You therefore have the option to REVERSE ICON ORDER. The final option you have here is SHOW ICON ONLY. This is quite neat as it allows you to show an icon in a cell by itself rather than have both the data and the icon which can look a little messy or crowded. Problem is you cannot use icons etc when applying a formula to your conditional format.
To get around this minor inconvenience, you can add a new column and create an IF statement that puts a value in the cell and you can then apply your icon set based on the values generated by the IF statement. Simple!
Let’s add a new column to our table called ‘Status’ and add a formula;
In this example I only want to highlight totals that are less than 1000 so if the statement returns a FALSE value it displays nothing. Copy this down to all rows in your table.
Now apply a conditional format so that if the value is 1, display a red ‘X’ icon only.
Voilà! By using this method, you avoid a lot of clutter in your spreadsheet and draw the viewer’s attention only to those values that require a closer look.
Try playing with the settings of the DATA BARS etc. Follow the same steps as above, apply a variety of values and see what effect it has on the appearance of the formatting.
And that is CONDITIONAL FORMATTING!
In the previous blog I showed you how to use the basics of CONDITIONAL FORMATTING to automatically change the appearance of cells or their contents based on criteria. The rules were applied directly to the cell and their contents. However, there are times when you want cells to change format based on something happening elsewhere or perhaps a combination of criteria which trigger the format change.
In order to achieve this we have to create formulas. It’s not that difficult, but you have to think a bit about what you are trying to do, and key to the formulas succeeding is the correct use of relative and absolute references in the formulas. If you are unsure about relative and absolute referencing check my blog (Relative vs. Absolute Referencing) first.
Let’s take a simple table tracking invoice payments.
On the far right is the payment status for each invoice. Let’s imagine for a moment that this column is over on the far right of the spreadsheet and every time you want to check the status of an invoice you have to scroll over to see it. But what if you could get the Order No to show up in red if an invoice is unpaid instead?
Well guess what? Using a formula in conditional formatting will do the trick.
Go to CONDITIONAL FORMATTING as before on your HOME tab, and click on NEW RULE.
This will open a new window with a fairly long list of options to choose from. Most of the options you can achieve using the buttons/icons I used in the previous blog. The one we are interested in this time is USE A FORMULA TO DETERMINE WHICH CELLS TO FORMAT.
Previously we created conditions or criteria based on the actual cell contents. This time I want an order number to change appearance but the order number itself will never change, so I can’t create a rule based on the contents of the order number cells, so I have to look elsewhere for changes to cause some sort of trigger.
First select all the cells you want to apply your conditional format to. In this example it will be cells B3 to B12. Make sure you select cells correctly: top to bottom or left to right. I’ll explain why a bit later…
Click on CONDITIONAL FORMATTING, then select NEW RULE, and finally click on USE A FORMULA TO DETERMINE WHICH CELLS TO FORMAT.
Click into the box under FORMAT VALUES WHERE THIS FORMULA IS TRUE and enter the following formula;
Note that any text values you are looking for must be in double speech marks in your formula.
Select a format by clicking on the FORMAT button, and in this example go to the FILL tab and pick one of the purples, and then click on OK. Note that when using formulas you don’t have the option to apply data bars etc as a format.
Trying to add some meaning to this formula…we are saying “if F3 has the word ‘No’ in it, then make the cell I am applying the format to (i.e. B3) change to purple”. We don’t mention the cell B3 in the formula, because by highlighting the cell(s) it is implicit that the formula relates to the selected cell(s).
Problem! If you click on the reference cell, in this example F3, Excel automatically turns this into an absolute reference ($F$3). If you leave it like this, it means that EVERY cell in that column you are applying the rule to will look to cell F3 and nowhere else.
So why does this happen? When you select a group of cells and apply a conditional format rule, you are effectively writing a formula for a single cell i.e. the first one in the selection. In this case it is B3. Because you have selected a block of cells, when you confirm your new rule, Excel copies the formula down (or across depending on the cells selected). So just like a standard formula you might write in a cell, the copied formula will modify its reference points depending on whether the formula cell reference is relative or absolute. This is why you have to select your cells in the correct order: left to right, top to bottom. If you do it the other way round Excel will look in completely the wrong place for its trigger.
So applying our new rule would give us this;
Then, if we update our data at any point it will automatically change according to the rules we have set up;
What if I want the whole line to change format rather than just the first column?
This is where we need to be a little more careful with our relative/absolute references.
So now, rather than select only the cells in column B, select the entire table. Follow all the same steps as above but you will need to make one small change to the formula. Remember that as formulas are copied down or across if the references are relative, the cells the formula looks to shift the same distance as the formula cell as it is copied. We therefore need to make sure that the formula always looks to column F which contains our “Yes/No” words that form part of our conditional statement.
Our formula should now look like this;
Our table will now be formatted properly with the entire row coloured in;
Now we still have one issue with this table and its formatting. If today is the 16th of January, for example, the last invoice is unpaid but it is not actually late. If you want to highlight invoices that are simply unpaid then this formatting is fine, but would it not be better to show up late payments?
For that we need a slightly more complex formula that performs a couple of tests:
- Is the invoice unpaid?
- Is today’s date beyond the due date?
If both these conditions are true then we want to highlight these in red so they stand out so we can chase up our customers!
So we can add a second rule to our table:
- First rule highlights any unpaid invoices
- Second rule flags any that are unpaid AND late
Our formula for this would therefore be;
By using the TODAY() function the formula will automatically adjust to the current date so you don’t have to!
Our finished table would now give us:
So there you have conditional formatting using rules. Of course, formulas can be as simple or as complex as your Excel skills allow. You just need to stop and think about what you are trying to achieve, which cells you are looking at and most importantly, setting the correct relative and absolute references in your formula to guarantee that it works properly.
My final blog on CONDITIONAL FORMATTING will look at editing existing rules and setting up formulas to take advantage of icons etc. rather than applying the rules to the cell contents.
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!