Using Excel Conditional Formatting to highlight matches is easy when you team it up with a data validation list like this:
Download the Excel File
Enter your email address below to download the sample workbook.
Setup Data Validation
Before we can set up the Conditional Formatting we need to set up the data validation list. So, over in column K I have the list of Salesperson’s names.
I’ve also added an item to the list for ‘No Highlighting’. Since there is no Salesperson called ‘No Highlighting’ it simply won’t display and conditional formatting if you select it.
In cell C4 I’ve got my Data Validation list which references the Salesperson’s names in cells K7:K18:
If you don’t know how to insert a Data Validation, or Drop Down list as they’re sometimes known, then click this link to read the tutorial on Data Validation: https://www.myonlinetraininghub.com/excel-drop-down-lists
Setup Conditional Formatting
Now all we need to do is set up the Conditional Formatting to highlight rows that match the salesperson selected in the Data Validation list.
Step 1: Select all of the cells you want the Conditional Formatting to apply to. In my case it’s cells A7:G49.
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 =$C$4=$C7:
Note: The absolute referencing in this formula is the key to success. Get this wrong and you’ll be scratching your head trying to understand what is going on.
Let me explain:
Conditional Formatting formulas must evaluate to TRUE or FALSE. When the formula evaluates to TRUE the format is applied, and when it evaluates to FALSE the format isn’t applied.
$C$4: We absolute both the column and row reference here because this is the cell containing the Salesperson’s name selected in the Data Validation list and we want that cell tested for every row.
$C7: We reference the first cell in column C of the table, and we only absolute the column reference here because we want the Data Validation formula to move down through each row in column C to check if the salesperson matches the salesperson selected in the Data Validation list in cell C4.
If that’s hurting your head then I recommend you take a few minutes to read my tutorial on how Conditional Formatting formulas work. I think the images in that post will help you visualise what Conditional Formatting is doing in the background. Once you get this there’ll be no stopping you.
Step 4: Click the ‘Format’ button in the dialog box above and set your format:
Job done! The hardest part is getting your head around how to construct the formula.
This is amazing! I am wondering though, can the first value be multiple cells? I have a static list of people that I want to highlight a certain color, but I can’t get it to highlight anyone but the first name. I tried $Q$5:$Q$10, but only the name in Q5 highlights. Thanks!
Hi Bobbie,
Please post your question on our Excel forum where you can also upload a sample file and we can help you further.
Mynda
It is a best website for me as i am facing some problems in excel. So it will help me
Glad we can help, Ejaz!
Hi Mynda
Nice article. Is it possible to put data validation in another worksheet if I have a big list?
Absolutely. I just put it on the same page for teaching purposes.
Mynda
Awesome
Glad you liked it, Shakil 🙂
Mynda
I really like this tip. So simple and very impactful.
I have passed on to others.
You are the best.
David
Cheers, David! Glad you’ll find it useful and thanks for sharing it 🙂
Trying to think of a situation where this would be desired, as opposed to just filtering the column headers to filter to the salesperson you want to locate the data for?
Hi Kathy,
If you want to still see the other data for the purpose of comparisons is one scenario I can think of. Also, in big tables filtering on and off can be slow.
Mynda
Fabulous! Also, the How Conditional Formatting Formulas Work tutorial is gold. Thanks for this. I’ve struggled with this at times and this makes it crystal clear.
One upgrade to this is to make the table (range) a Table, i.e., Format as Table. The drawback of the current conditional formatting is that if you add a row to the bottom of the range the formatting doesn’t automatically apply (unless you copy the formatting). If you convert the range to a table, the conditional formatting now applies to the table (“Show formatting rules for: This Table” in the Conditional Formatting Rules Manager) and any new rows added will automatically get the new formatting.
Another small improvement is to name the cell with the validation; for example “NameToHighlight”. Then the cond. formatting formula becomes =NameToHighlight=$C7.
Now if we could only figure out why Excel doesn’t allow us to use table structured references in the conditional formatting formula; If I named my table “TableToFormat” the formula would be =TableToFormat[@Salesperson]=NameToHighlight. So much nicer to read….
BTW, I love tables now. I bought the book by Barresse and Jones I think because I saw it advertised on your site.
All the best.
Great ideas, Renny. Thanks for sharing them and I’m so pleased you’re a Tables fan now 🙂
I agree it would be great if we could use the Structured References in more places. Maybe Microsoft will fix that some day.
Mynda
Can this work for the Sort function or a Filter? So the data will only show the cells that are TRUE and filter/ Sort out the rest?
Hi Toba,
I’m not sure I understand the scenario. You could add a column to your data set that tests if a value/row meets a condition and return a TRUE or FALSE which you could then use for sorting or filtering. You’d need to write some VBA code to automate that though, otherwise you’d have to change the sort/filter manually.
Mynda
I found this tool to be extremely useful at finding pieces of data within large data sets quickly. Is there a way to have the data validation list auto-populate with the unique values within a column? Thus, instead of having a separate list, the data validation looks at the column and auto-populates all the names.
Hi Jim,
There are many solutions for data validation lists, that can remove the blanks from a column, or list the unique entries only. Please take a look at this article, a solution based on a pivot table might be the solution you need.
Catalin
Mynda, this tutorial is a little gem, as all your tutorials are.
Correct me if I am wrong but what you show here is a different way of filtering, isn’t it?
And if so, isn’t it just as easy to use the filter arrow in cell C6?
Hi Peter,
Thanks for your kind words 🙂
This doesn’t filter as such so you can still see the highlighted rows in the context of the whole data set. Maybe you want to still be able to see the other rows for reference, whereas with the Filter buttons you only see the selected data.
Horses for courses.
Kind regards,
Mynda