How to highlight rows based on drop down list in Excel?

This article will talk about how to highlight rows based on drop down list, take the following screenshot for example, when I select “In Progress” from the drop down list in column E, I need to highlight this row with red color, when I select “Completed” from the drop down list, I need to highlight this row with blue color, and when I select “Not Started”, a green color will be used to highlight the row.

Highlight rows with different colors based on drop down list by using Conditional Formatting

Highlight rows with different colors based on drop down list by using a useful feature

Highlight rows with different colors based on drop down list by using Conditional Formatting

Normally, the Conditional Formatting feature can help you to deal this task, please do as follows:

1. First, please insert the drop down list, select the cells where you want to insert the drop down list, and then click Data > Data Validation > Data Validation, see screenshot:

2. In the Data Validation dialog box, under the Settings tab, select List form the Allow dropdown, in the Source text box, please click button to select the values you want to use in the drop down list. See screenshot:

3. After inserting the drop down list, then apply the Conditional Formatting to the data range, please select the data range that you want to highlight the rows based on dropdown, see screenshot:

4. And then click Home > Conditional Formatting > New Rule, and in the New Formatting Rule dialog box, click Use a formula to determine which cells to format in the Select a Rule Type list box, and then enter this formula =$E2="Not Started" into the Format values where this formula is true text box, see screenshot:

Note: In this formula, E2 is the cell where the first drop down list is located, the text “Not Started” is the value in drop down list you have created.

5. Then click Format button to go to the Format Cells dialog, please choose one color that you want to highlight the specified rows when the value “Not Started” is displayed in the drop down list, see screenshot:

6. And then click OK > OK to close the dialogs.

7. Then repeat the above 4-6 steps to apply the conditional formatting to other drop down values, for instance, enter the formulas: =$E2="Completed" and =$E2="In Progress" for the Completed or In Progress rows, and specify the colors for each item individually as you need.

8. And now, all the drop down list values have been applied with the conditional formatting, when you select the item from the dropdown, the row will be highlighted with the color you specified. See screenshot:

Highlight rows with different colors based on drop down list by using a useful feature

The above method may be somewhat troublesome if there are multiple drop down list items needed to be colored, so, here, I will introduce an easy feature, Kutools for Excel's Colored Drop-down List, with this powerful feature, you can solve this task as soon as possible. Click to download Kutools for Excel!

After installing Kutools for Excel, please do as this:

1. First, you should create the drop down list that you want to use, see screenshot:

2. Then, click Kutools > Drop-down List > Colored Drop-down List, see screenshot:

3. In the Colored Drop-down list dialog box, please do the following operations:

• Select Row of data range option from the Apply to section;
• Then, select the drop down list cells and data range you want to highlight the rows;
• At last, specify the color for the drop down list items separately as you need.

4. And then, click OK to close the dialog box, now, when you select the item from the dropdown, the row will be highlighted with the color you specified.

Click to Download Kutools for Excel and free trial Now!

Demo: Highlight rows based on drop down list with Conditional Formatting

More relative articles: