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:
- Create Drop Down List With Hyperlinks In Excel
- In Excel, adding drop down list may help us to solve our work efficiently and easily, but, have you ever tried to create drop down list with hyperlinks, when you choose the URL address from the drop down list, it will be open the hyperlink automatically? This article, I will talk about how to create drop down list with activated hyperlinks in Excel.
- Create Drop Down List But Show Different Values In Excel
- In Excel worksheet, we can quickly create a drop down list with the Data Validation feature, but, have you ever tried to show a different value when you click the drop down list? For example, I have the following two column data in Column A and Column B, now, I need to create a drop down list with the values in Name column, but, when I select the name from the created drop down list, the corresponding value in Number column is displayed as following screenshot shown. This article will introduce the details to solve this task.
- Create Drop Down List With Images In Excel
- In Excel, we can quickly and easily create a drop down list with cell values, but, have you ever tried to create a drop down list with images, that is to say, when you click one value from the drop down list, its relative image will be displayed at the same time. In this article, I will talk about how to insert a drop down list with images in Excel.
- Increase Drop Down List Font Size In Excel
- Drop down list is a very helpful feature in Excel, when you create drop down list in your worksheet, have you ever tried to increase the font size of the drop down list to make the selected record larger and more readable as left screenshot shown? This article will talk about a workaround to solve it.
- Create Multi Level Dependent Drop Down List In Excel
- In Excel, you may create a dependent drop down list quickly and easily, but, have you ever tried to create multi-level dependent drop down list as following screenshot shown? This article, I will talk about how to create a multi-level dependent drop down list in Excel.
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
