I started appreciating the wonders of conditional formatting when I was working in Ad Operations… Read more Read more Conditional Formatting Formula Basics
The beauty of using formulas in your conditional formatting is that you can write whatever formula you want - the world is your oyster. There’s basically only one rule, and that is that
your formula must return either TRUE or FALSE. Excel will format the cells where your formula results in TRUE.
You can think of the formula as being applied to an additional layer on top of the existing cells, and functioning the same way a formula would in any other cell. For example, if we were checking if any of the cells in B2:C6 were greater than 10, our formula would need to be “=B2>10” Conditional formatting will “copy” this formula across our range of cells, so cell B2 has “=B2>10,” cell B3 has “=B3>10”, cell C2 has “=C2>10” and so on. The cells with formulas that result in TRUE will be formatted, and the ones that result in FALSE will not be.
In B2, the value of the cell is 12 and the formula in our formatting layer is =B2>10, so the formula results in TRUE and this cell gets highlighted. In B3 the formula is B3>10, but the value of the cell is 3, so this formula results in FALSE and is not highlighted.
If you need help figuring out if your formula is going to give you the right results, a handy trick is to write the formula you have in mind in some adjacent cells. Make sure you copy the formula into a range the same size and shape as the one you want to format. Check which cells returned TRUE and match them up with the corresponding cells in your actual range - those are the cells that will be formatted.
Now for the fun stuff
Now that we know that conditional formatting formulas work the same as regular ol’ formulas, we can start getting creative. The examples above are simple conditional formatting examples that we can accomplish much easier using Excel’s built in conditional formatting tools. So let’s get into some more complicated examples where we need formulas to get the formats we want.
Formatting Full Rows or Columns
In our first example, the formula was “copied” across our range and the references changed as one would expect them to - the formula in B2 was =B2>10, and then in B3 it became =B3>10, and so on. But, we don’t have to use relative references in our conditional formatting formulas - we can use absolute references to achieve some more sophisticated formatting results.
Personally, I hate looking at a table with single cells highlighted, like they were in our first example. If a cell matches my criteria, I find highlighting the full row a lot more useful for most tasks. Let’s go back and look at my daily delivery report example from
post #1 - this was a giant report that showed how each currently live ad unit was pacing towards its goal and how much, if any, revenue was possibly at risk due to underdelivery.
Say I want to find the ad units that are badly underpacing, which I’ll define as those with a pacing index of less than 30%. I could easily highlight column G and apply a basic highlighting rule for any cell with a value less than 30%.
But then what? With the cells in column G highlighted, I’d have to trace the row back to look at which ad unit (column B) the low values corresponded to. Wouldn’t it be easier if I could highlight any
row where the value in column G was less than 30%?
Much better! Now I can easily identify the specific ad units that are underpacing.
To accomplish the above, we have to play around with absolute references. We can lock either the column reference or the row reference, so that as the formula gets copied across our range, the locked reference won’t change.
Using our smaller example data set from before, let’s say we wanted to highlight the rows where column C is greater than 10. We want the end result to look like so:
We need to think of a formula that would result in TRUE for all of the highlighted cells above. So, we need A2, B2 and C2 all to have the formula “=C2>10”, and then A3, B3 and C3 to have “=C3>10”, and so on. In short, we need every formula to have a reference to column C in it.
If we make the
column reference absolute ($C2 instead of C2), as the formula copies across our range, only the row reference will change, but the column reference will stay the same. Now each cell will have a formula with a reference to the cell in column C in their row, so whenever that cell is greater than 10, the formulas in all of the cells in that row will result in TRUE and be highlighted.
We can do the same thing for highlighting full columns, by making the row reference absolute (C$2 instead of C2). I haven’t ever come across a great use for that though (but if you have, tell me in the comments!)
To summarize, to highlight the whole row if a cell in one column matches your criteria you:
Select the full table as your range, excluding the header (ex: A2:C6 from above) Make sure the cell reference in your formula has a column absolute reference (ex: $A2)
As always, test out your formula in adjacent cells until you get the hang of it! And don’t forget about the handy
shortcut command+T on Macs or F4 on a PC to quickly cycle through absolute and relative reference options. Using Multiple Conditions
What if we wanted to highlight the rows where the cells in column B AND column C were greater than 10? We can do that too!
With a little help from our friends
AND() and OR(), we can easily make our formula consider multiple conditions. If we write our formula as such:
Each row will only be highlighted if both the cell in column B and the cell in column C are greater than 10.
As long as the formula you write results in a boolean value (true or false) you can put whatever the heck you want in there!
Bringing it all together
For my daily delivery report, we can use both of these techniques to help us pick out the most important data.
With all the craziness of the report, I want to highlight 3 different groups of varying priority:
Units ending in less than 1 week with more than $5,000 at risk (red) Units ending in the next 2 weeks that have a pacing index below 50% (orange) Units ending in the next 30 days with a pacing index below 50% (yellow)
For our top priority, we’ll create a rule with the following formula, to find rows where the End Date is between today and 7 days from now, and where Revenue at Risk is greater than 5000
Next, we’ll find the rows where the End Date is between now and 14 days from now, and the Pacing Index is less than .5
Lastly, we’ll look for rows where the End Date is between now and 30 days from now, and the Pacing Index is less than .5
Once we’ve created all of our rules, it’s important to make sure they’re in the correct priority order in the “Manage rules” window. There are likely some line items that meet more than one of our criteria (if something is ending within 7 days from now, it’s also technically ending within 14 days and 30 days from now) and if that happens, Excel will format it based off whichever rule comes first in the list. So we want to make sure the rules are ordered in the correct priority, so anything matching the first rule will turn red, not any other color, and anything matching the second rule will turn orange, and so on.
Now the report is highlighted based on my priority scale! I can look at this report now and see there are 3 line items I absolutely need to deal with today and a few that I should keep an eye on this week. And I can just ignore anything that isn’t highlighted because those are future Allison problems. Instead of spending time reviewing hundreds of rows, now I can focus on just a handful of the most important.
Once you get the hang of using formulas in conditional formatting (which you will), the possibilities are endless. Don’t let yourself be constrained by Excel’s built-in conditional formatting options, format the report of your dreams with formulas!