Excel – conditional Formatting: Using Formulas to Apply Rules
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.