Excel’s Conditional Formatting tool is diverse with loads of built in rules that you simply point and click to use, but I find more often than not that I need to use a formula based rule.
I can sympathise if you’ve ever tried to use formulas in your conditional formatting and ended up tearing your hair out in frustration.
Thankfully there are only 3 simple rules you need to know. Once you understand these rules you’ll never look back.
Watch the Video
Conditional Formatting with Formulas - Written Instructions
Rule 1 – the formula must evaluate to TRUE or FALSE*
Conditional formatting is looking for a true or false outcome, or their numeric equivalents 1 and 0. If the outcome is true or 1 it will apply the format, if it’s false or 0 it won’t. It’s black and white.
*A little known fact is formulas that evaluate to any positive or negative number will also be considered as 'TRUE' and the format will be applied. And any formulas that evaluate to zero will not have the formatting applied.
I like to test my rules in the worksheet first. Once I know they’re returning the correct result I can create my new rule and paste the formula into the conditional format rule description.
For example, let’s say we have loaned out some books and we want to flag when they are overdue by formatting them in red like this (by the way, my dates are formatted dd/mm/yyyy):
In cell E1 I have the current date, and in column D I’ll enter my test formula:
Then I can copy the formula down the column and check each row is evaluating as I’d expect.
Ok, now I’m happy with my formula I’ll edit cell D2 and copy the formula to my clipboard so it’s ready to paste into the Conditional Formatting rule description.
Rule 2 – Select Your Cells
Before you go and set up your conditional formatting rule you need to select all of the cells you want formatted.
Since I want to format from column A to C for each row, I’ll select my whole table like this:
Rule 3 – Absolute References
The next trick with Conditional Formatting formulas is when to use absolute references.
I’ll create my new rule:
And paste my formula into the 'Format values where this formula is true' field:
As I’m comparing the dates in column C to our current date, I need to absolute the column reference in my formula, but not the row reference.
And that’s because in the background Excel is applying that formula to every cell I selected (back in rule # 2) to test for a TRUE/FALSE outcome.
This is the way I remember this rule:
When you enter the formula in the 'Format values where this formula is true' field Excel is applying it to every cell you have selected.
When it does so, the cell references dynamically update, just as they would if you entered it in cell C2 and then copied and pasted it over your selected area, except instead of actually pasting it the Conditional Formatting tool does the calculation in the background.
I picture it as a separate layer of the workbook, like in the image below. The top layer is the Conditional Formatting formula and the bottom layer is my workbook.
Note how in the Conditional Formatting layer the formula in each row has dynamically updated to pick up the current row but the column reference remains with C. i.e. testing the due date in column C for each row.
It would then evaluate like this:
If I didn’t absolute column C Excel would think I wanted to do this:
See how the above formulas would be wrong since it’s testing every cell to see if it’s < or = to cell E1 not just column C for each row?
Ok, now that I’ve entered my formula I can set up my formatting and I’m done: