Conditional Formatting A Drop Down List

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 the example below.

Key Takeaways

• Conditional formatting in dropdown lists serves as a beneficial tool in Excel to visually communicate the status of tasks or items, such as indicating a completed job or evaluating project risks. By changing cell colors based on selected items, users can quickly grasp task progress or data and make spreadsheets more efficient and user-friendly.
• Implementing conditional formatting to dropdown lists not only improves the presentation of data but also aids in preventing incorrect data entries by guiding users to select from predefined options.
• Adding conditional formatting to dropdown lists can be a quick way to put emphasis on your data.

Want to know all about Conditional Formatting from Beginner to Advanced?

*** Watch our video and step by step guide below with free downloadable Excel workbook to practice ***

Watch it on YouTube and give it a thumbs-up!

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

 

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

STEP 3: Select New Rule

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.

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.

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.

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

Now our table now has conditional formatting applied!

 

FAQ Section

Can I use different colors for each item in my Excel drop-down list?

Absolutely! Excel lets you personalize your drop-down list by applying different colors to each item. It’s a bit of a workaround using conditional formatting. Here’s how you can achieve it:

1. Define your data validation list as per usual.
2. Click on one of the cells in your drop-down list.
3. Navigate to the ‘Home’ tab and select ‘Conditional Formatting’.
4. Choose ‘New Rule’ and then ‘Use a formula to determine which cells to format’.
5. Enter the formula that corresponds to the item you’d like colored—something like =A1="YourItem".
6. Pick the formatting you desire and click ‘OK’.

You would need to repeat the conditional formatting steps for each item in the drop-down, changing the formula and colors as needed. While it takes a bit of initial setup, the result is a visually coordinated and user-friendly drop-down menu.

How to change cell color based on drop-down list?

Changing the cell color based on selections from a drop-down list is a neat trick to visually enhance your spreadsheet’s interactivity. Excel’s Conditional Formatting feature is your best friend here.

Follow these steps to start:

1. Click the cell that holds your drop-down list.
2. Go to ‘Home’, and click on ‘Conditional Formatting’, then ‘New Rule…’.
3. In the dialog box, select ‘Format only cells that contain’.
4. From the first drop-down menu, choose ‘Cell Value’ and from the second, choose ‘equal to’.
5. Type the value from the drop-down list that should trigger the color change in the next box.
6. Click the ‘Format…’ button, select the ‘Fill’ tab, and choose your color.
7. Click ‘OK’ twice to apply the rule.