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.
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!
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.
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!
没有评论:
发表评论