You Need To Change How You Think When Using Formulas In Conditional Formatting!

I recently received a request to use Conditional Formatting when performing two different comparisons on two data ranges.  How I approached the task was WRONG! Read on to see how I finally got myself on the right track.

You can download the file here and follow along. If you get a preview, look for the download arrow in the upper right hand corner.

Here I have two sets of data and the requirements are:

Challenge #1) If Mark 1 & Mark 2 match on the same row, highlight in Bold Red if the X values are different; and

Challenge #2) Highlight the cells in Yellow for each item that is in Mark 1 and not in Mark 2, and vice versa.


Here should be the results of those Conditional Formatting rules:


Note that in Row 4, CCC matches, but the X values do not.  Same with Row 9.  Also, FFF & KKK are not in column D, and NNN is not in column A.

When I was first presented with this challenge, my mind went to ways that I might structure the formula when inserting a column of answers.  For example, for challenge #1, I thought about embedded IF statements, such as =IF(A2=D2,IF(B2<>E2,”Red”,””),””).  For challenge #2, I figures an INDEX/MATCH or VLOOKUP statement was the way to go.


Both assumptions were way off and I was making things too complicated for Conditional Formatting.  With the Conditional Formatting approach, your thought needs to be “what will produce a TRUE or FALSE answer, so that it will or won’t trigger the formatting”.

For Challenge #1, a simple AND statement worked perfectly.  I used:


The first criteria check is: does the data in A2 equal the data in D2.  The second criteria check is: does the data in B2 NOT equal the data in E2.  If both of these are TRUE, run the conditional formatting.  Bingo!

For Challenge #2, I didn’t need any type of LOOKUP function, but a simple COUNTIF would do.  The formula used for the data in Column A was:


And for Column D, similarly:


Here I’m asking the formula to look in Column A for each item in Column D.  If the result of that Count is Zero, meaning there is no match, run the formatting.  Note how I set up the Absolute/Relative cell references so that it will lock the range to look in ($A$2:$A$11), but allow the formula to compare each of the items in Column D ($D2).

Take a look at the video and watch the process of creating these formatting conditions if you need a refresher on using this Excel tool.

The key is the approach.  Don’t think about creating a formula to generate a specific result, but rather, what simple formula will give me a TRUE or FALSE answer.  That’s the key!

Happy Excelling!



Free Download!

Subscribe to Download Your FREE Copy of
"My 70+ Favorite Excel Keyboard Shortcuts" Today!

4 Responses to You Need To Change How You Think When Using Formulas In Conditional Formatting!

  1. Tom Garfin August 3, 2015 at 9:30 am #

    Hi Michael,

    I have shared common data that I configured for a supplier scorecard with all kinds of graphics and reporting. I can get the reporting I want to give to a specific supplier if I configure it for that supplier. Then I have to redo it for each supplier. I’d like to be able to just put the suppliers name in and have it generate a report for that specific supplier.

    Any thoughts on the best way to do this? I will be sending a report to each supplier each quarter so it is worth my time to figure how to do this elegantly.


    Tom Garfin

  2. jeff weir August 3, 2015 at 9:27 pm #

    Nice post, Michael.

Leave a Reply