# 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.

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.

WRONG!

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:

=AND(\$A2=\$D2,\$B2<>\$E2).

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:

=COUNTIF(\$D\$2:\$D\$11,\$A2)=0

And for Column D, similarly:

=COUNTIF(\$A\$2:\$A\$11,\$D2)=0

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!

### 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.

Thanks!

Tom Garfin
tom.g@kascomarine.com

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

Nice post, Michael.

• Michael Rempel August 3, 2015 at 10:04 pm #

Thanks, Jeff! Glad you liked it!