Tip: Other languages are Google-Translated. You can visit the English version of this link.
Log in
x
or
x
x
Register
x

or

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 in Excel

Kutools for Excel's Colored Drop-down List utility can help you to apply different colors based on the drop down list. It means, when selecting the item from the dropdown, the row will be highlighted with a specific color. Click to download Kutools for Excel!

Kutools for Excel: with more than 300 handy Excel add-ins, free to try with no limitation in 30 days. Download and free trial Now!


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 Conditional Formatting

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.

Tips:To apply this Colored Drop-down List feature, firstly, you should download the Kutools for Excel, and then apply the feature quickly and easily.

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.

Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%

  • Reuse: Quickly insert complex formulas, charts and anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails...
  • Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
  • Merge Cells/Rows/Columns without losing Data; Split Cells Content; Combine Duplicate Rows/Columns... Prevent Duplicate Cells; Compare Ranges...
  • Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
  • Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
  • Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
  • Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
  • Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
  • More than 300 powerful features. Supports Office/Excel 2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features 30-day free trial.
kte tab 201905

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!
officetab bottom
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    Mark Maxwell · 2 months ago
    THANK 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.
    Kris · 1 years ago
    I 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.
    Ash · 1 years ago
    Hi.I have tried this but it highlights my whole sheet ,why is this happening?help?
    • To post as a guest, your comment is unpublished.
      skyyang · 1 years ago
      Hello, Ash,
      You can view the video to check the detailed information of the steps.
      • To post as a guest, your comment is unpublished.
        Elle · 1 years ago
        I 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.
          skyyang · 1 years ago
          Hi, 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.
    David Sheerin · 1 years ago
    I 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.
      skyyang · 1 years ago
      Hello, 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.
    Lynda M · 2 years ago
    Thank you! Just what I needed
  • To post as a guest, your comment is unpublished.
    Michelle · 2 years ago
    YES! I couldn't remember how to do this and the solution is so simple (once you know it). Thank you! :)