In a previous post I showed you how to Create a Drop Down List in a Table.   We are now going to take this concept one level further and apply some conditional formatting to the drop down data validation list.

This is useful if you want to highlight when a job is completed, check off items from a list or to evaluate risk in a project just like I have done in below´s example.

DOWNLOAD EXCEL WORKBOOK

STEP 1: Select the range that you want to apply the conditional formatting to.

Conditonal Format a Drop Down List

 

STEP 2: Go to Home > Styles > Conditional Formatting > Manage Rules

Conditonal Format a Drop Down List

STEP 3: Select New Rule

Conditonal Format a Drop Down List

STEP 4: Create the new rule for High values:

Select Use a formula to determine which cells to format

Type in the Formula =$A4=”high” 

This formula will ensure only the column is absolute.

Go to Format > Fill then select a color of your choosing. Click OK.

Conditonal Format a Drop Down List

Repeat the same steps for medium values. Click New Rule.

Select Use a formula to determine which cells to format

Type in the Formula =$A4=”medium” 

This formula will ensure only the column is absolute.

Go to Format > Fill then select a color of your choosing. Click OK.

Conditonal Format a Drop Down List

Repeat the same steps for low values. Click New Rule.

Select Use a formula to determine which cells to format

Type in the Formula =$A4=”low” 

This formula will ensure only the column is absolute.

Go to Format > Fill then select a color of your choosing. Click OK.

Conditonal Format a Drop Down List

This is how our new set of rules will look like:

Conditonal Format a Drop Down List

Now our table now has conditional formatting applied!

Conditonal Format a Drop Down List

 

How to Conditionally Formatting A Drop Down List

Data Validation & Cond Formatting

Helpful Resource:

101 Macros Book

 

 

If you like this Excel tip, please share itEmail this to someone

email

Pin on Pinterest

Pinterest

Share on Facebook

Facebook

Tweet about this on Twitter

Twitter

Share on LinkedIn

Linkedin