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.

ScreenHunter_66 Apr. 19 09.38

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’.

ScreenHunter_67 Apr. 19 09.38

Once I have those set up I can set about applying some CONDITIONAL FORMATTING based on the selections made in the two drop downs.

ScreenHunter_67 Apr. 19 09.39

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

ScreenHunter_65 Apr. 19 09.38

And there you have a simple way to highlight data in a list based on the user’s selections in drop down lists.


Posted on April 19, 2013, in Conditional Formatting, Data Validation and tagged , , . Bookmark the permalink. Leave a comment.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: