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.
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 clickbutton 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:
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.
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.
You are guest
or post as a guest, but your post won't be published automatically.
- To post as a guest, your comment is unpublished.· 2 years agoTHANK YOU!!! I've been working trying to figure this rule out without applying it to each individual row.
- To post as a guest, your comment is unpublished.· 3 years agoI can get this to apply this to a single row, however I have over 3000. This would be very time consuming. Is there another way to apply this same funtion to all my rows easily without manually repeating this.
- To post as a guest, your comment is unpublished.· 3 years agoHi.I have tried this but it highlights my whole sheet ,why is this happening?help?
- To post as a guest, your comment is unpublished.· 2 months agoYes, when you choose the cell for the formula your Excel version makes it like this: =$E$2
HOWEVER you need to remove the second dollar sign for it to work just like in the example on this page. =$E2="Completed"
I have no idea what the difference is and why it happens though. I see you replied 3 years ago but maybe this will help someone else.
- To post as a guest, your comment is unpublished.· 3 years agoHello, Ash,
You can view the video to check the detailed information of the steps.
- To post as a guest, your comment is unpublished.· 3 years agoI did exactly what's told and on the demo but it highlights the whole selection. Not the row.
- To post as a guest, your comment is unpublished.· 3 years agoHi, Elle,
Would you mind to send your worksheet to my email account? Or you can insert your problem as a screenshot here.
- To post as a guest, your comment is unpublished.· 3 years agoI have tried this but it only highlights the drop down cell not the entire line. aM I MISSING SOMETHING??
- To post as a guest, your comment is unpublished.· 3 years agoHello, David,
After inserting the drop down list, first, you should select the the data range not the drop down list column only, and then when applying the formula in conditional formatting, please enter the formula: =$E2="Not Started", remember enter the $ sign before the cell reference.
Hope this can help you, thank you!
- To post as a guest, your comment is unpublished.· 4 years agoThank you! Just what I needed
- To post as a guest, your comment is unpublished.· 4 years agoYES! I couldn't remember how to do this and the solution is so simple (once you know it). Thank you! :)