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.
Conditional formatting to highlight every other or nth row / column:
With Kutools for Excel' Alternate Row / Column Shading feature, you can quickly highlight every other or nth row / column at once. Download and free trial Kutools for Excel now!
Kutools for Excel: with more than 200 handy Excel add-ins, free to try with no limitation in 60 days. Download and free trial Now!
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:
Recommended Productivity Tools
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
To post as a guest, your comment is unpublished.· 6 months 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.· 10 months 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.· 9 months agoHello, Ash,
You can view the video to check the detailed information of the steps.
To post as a guest, your comment is unpublished.
To post as a guest, your comment is unpublished.· 1 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.· 1 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.· 1 years agoThank you! Just what I needed
To post as a guest, your comment is unpublished.· 1 years agoYES! I couldn't remember how to do this and the solution is so simple (once you know it). Thank you! :)