Excel – Highlight Data with Data Validation and Conditional Formatting
When working with lists of information it is sometimes nice to be able to highlight rows of related information such as all rows for a specific product or salesperson. If you have the latest version of Excel then you have the benefit of SLICERS, but if not then you need a different approach to achieve this. By combining DATA VALIDATION and CONDITIONAL FORMATTING this can be done quite easily.
Let’s take this simple table showing regional sales by product line.
I could use a filter and select the information I need but using a combination of DATA VALIDATION and CONDITIONAL FORMATTING will display it in the table itself whilst still leaving other data visible.
Create a couple of drop down lists using DATA VALIDATION using the values from your list to generate the values to select from. I have left a blank cell at the top of each list so that I can have an option to select ‘nothing’.
Once I have those set up I can set about applying some CONDITIONAL FORMATTING based on the selections made in the two drop downs.
I have set up three rules, one for each drop down list and one to highlight values that meet both selections. Note the use of partial absolute referencing in the formulas to make sure that when the rules are copied across the three columns the reference points remain constant i.e. always look in columns A and/or C.
Formulas are as follows:
=AND($A6=$B$1,$C6=$B$2) to highlight when both product & region criteria match
=$C6=$B$2 to highlight where the product line is matched
=$C6=$B$1 to highlight where the region is matched
And there you have a simple way to highlight data in a list based on the user’s selections in drop down lists.