In this tutorial we’re going to look at how we can use Excel Conditional Formatting to highlight rows in a table where a field matches any item in a list.
Let’s look at an example, below is our table of data:
And we want to highlight the rows that contain any of the categories in this Table:
Like so:
Note: My list is in an Excel Table in cells I7:I9 and I’ve given it the Named Range: List. We’ll be using this name in the Conditional Formatting formula.
Download the Excel File
Enter your email address below to download the sample workbook.
Setup Conditional Formatting
Step 1: To set up the Conditional Formatting we first select the Table cells we want to highlight, in my case A5:G47.
Step 2: Home tab > Conditional Formatting > New Rule > select ‘Use a formula to determine which cells to format’ from the Rule Type list.
Step 3: Insert the formula
=MATCH($D5,List,0)
In the dialog box as shown below:
Now if you remember my post from a couple of weeks ago using conditional formatting to highlight matches, you’ll recall that I said Conditional Formatting formulas must always evaluate to TRUE or FALSE, or their numeric equivalents of 1 and 0.
And if you’re familiar with the MATCH Function you’ll know that it returns the position of a value in a list, and in this example that could be anything between 1 and 3. So you might be wondering how that MATCH formula works in Conditional Formatting.
Taking the formula above, it evaluates like so:
=MATCH($D5, List, 0)
=MATCH("Shirts", {"Shorts";"Shirts";"Skirts"}, 0)
=2
i.e. Shirts is the second item in the ‘List’.
So, the formula isn’t returning TRUE or FALSE, or their numeric equivalent of 1 and 0, yet the format is still applied. What?!
I discovered through experimenting that the conditional format will be applied as long as any (positive or negative) value other than zero is returned by the formula.
That means we could also use this formula to achieve the same results:
=COUNTIF(List, $D5)
Click here for a more thorough understanding of how Conditional Formatting formulas work.
Step 4: Click the ‘Format’ button in the dialog box above and set your format:
Thanks
A big thank you to Cliff Beacham for sharing his 'Excel Conditional Formatting Highlight Matches in List' tip and for teaching me something new.
Cliff has an Excel book coming out soon. Keep your eye out on Amazon for it.
Were you able to apply this to your whole table? Or did you have to duplicate the condition for every row?
Thanks!
Please ignore my question- I figured it out. I had my first cell locked! Thanks again for this post, it helped a lot!
This is great, it works! I have a question and I can’t find the answer. Instead of exact “match”, I would like to highlight cells which “contains” the words in my list. Do you know how to do?
Hi Mylene,
If you mean you have a list of substrings like :
Sh
Sk
Ja
and you want to check if these substrings appear within other strings you can change the CF formula rule to
=SUM(IFERROR(SEARCH(List,D5),0))
If that doesn’t work, please start a topic on our forum and attach your workbook.
Regards
Phil
I attempted this with the cell value named range in column A and the column to match against in column B. It worked for most values, but was not accurate in every case. For instance, A cell value of “0011I00000Op6ye” in columns A AND B were not highlighted by the conditional formatting.
Any thoughts….perhaps on how to make it case sensitive?
Hi Ryan,
Can you please post your question and sample Excel file on our forum so we can see how it is constructed and troubleshoot from there.
Mynda
If you use an IF statement and in the first argument you refer to is a positive or negative number then the formula will evaluate to TRUE (e.g., =If(1,TRUE,FALSE). A zero or empty cell will result in FALSE (e.g. =If(0,TRUE,FALSE). This is what is happening in the conditional formatting formula.
I read this somewhere else a long time ago but I don’t remember where.
Nice, thanks for sharing, Jonathan.
Mynda
Similarly, the condition for an IF formula can evaluate to any-number-but-zero for the TRUE option to be triggered
and ehan, I feel you pain, but all you need to do is define a suitably-named range to refer to your table column and it all works (like what Mynda did)
Gents , its not working with me as I followed this step and its highlight all the cells from A5:G47
Hi Mrefy,
I suspect you entered something not quite right. Please double check your formula etc. If you don’t find the problem please post your file and question in our Excel Forum so we can help you.
Mynda
Hi Mynda
From your example 1 in your workbook,if you use MATCH you need to press CTRL+ALT+F9 before the formatting updates. If you use COUNTIF it automatically update. This seems odd as example 2 works fine without CTRL+ALT+F9.
Sunny
Hi Sunny,
That’s really odd. I recall testing the first sheet example by changing an item in the list and it worked fine. I was playing around with which categories to use to get the best results for the examples. In fact, if you edit the conditonal formatting rule, but don’t change anything it starts working again! Odd.
Mynda
Hi Mynda
If you edit the CF, it will work at that point of time. If you save, close and then reopen the file, the CF will not work again when you change the categories.
It’s the named range not refreshing. If you just reference the cells in the Conditonal Formatting rule it updates immediately. This is why the COUNTIF version works, it doesn’t reference a named range.
It is frustrating that Conditional Formatting doesn’t recognize table references, so you cannot replace =$D6 in your formula with =Table2[@Category], not unless there is some strange syntax I have yet to stumble upon.
For me, I’d much rather say this conditional format applies to Table2 instead of $A$5:$H$47. Same with columns. Applying a format to =Table2[Sales Price] makes more sense to me than $F$5:$F$47
=MATCH($d5,INDIRECT(“Table2[Sales Price]”),0)