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
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: http://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.
If you liked this or know someone who could use it please click the buttons below to share it with your friends on LinkedIn, Google+, Facebook and Twitter.