How to highlight rows based on a drop-down list in Excel?
This article explains how to highlight an entire row according to the item selected from a drop-down list. In this example, selecting In Progress in column E highlights the row in red, Completed highlights it in blue, and Not Started highlights it in green.
- Highlight rows with Conditional Formatting
- Highlight rows with Kutools for Excel
- Video: Highlight rows with Conditional Formatting
- Frequently asked questions
Comparison of methods for highlighting rows based on a drop-down list
| Method | How it works | Best for |
|---|---|---|
| Conditional Formatting | Uses a separate formula and formatting rule for each drop-down value. | Users who prefer a built-in Excel solution and have only a few status values. |
| Kutools for Excel | Assigns colors to the list items and applies them to entire rows in one dialog box. | Quick setup, especially when the drop-down list contains several items. |
Highlight rows with Conditional Formatting
Excel's built-in Conditional Formatting feature can highlight each row according to the value selected in its drop-down cell. First create the drop-down list, and then add one formatting rule for each list item.
Create the drop-down list
- Select the cells where you want to insert the drop-down list, and then click Data > Data Validation > Data Validation.
- In the Data Validation dialog box, on the Settings tab, select List from the Allow drop-down list. In the Source box, click the selection button
and select the values you want to use in the drop-down list. 
and select the values you want to use in the drop-down list. 
Add the Conditional Formatting rules
- Select the entire data range whose rows you want to highlight. Include the drop-down cells in the selection.
- Click Home > Conditional Formatting > New Rule. In the New Formatting Rule dialog box, select Use a formula to determine which cells to format, and enter =$E2="Not Started" in the Format values where this formula is true box.
Note
In this formula, E is the column containing the drop-down lists, and row 2 is the first row of the selected data range. Keep the column reference absolute and the row reference relative. Replace these references if your list is in another column or begins on another row.
- Click Format to open the Format Cells dialog box, and choose the color that should highlight rows containing Not Started.
- Click OK twice to close both dialog boxes.
- Repeat the preceding rule-creation steps for the other drop-down values. For example, use =$E2="Completed" and =$E2="In Progress", and assign the desired color to each rule.
- Select an item from a drop-down list. The corresponding row is highlighted with the color you specified.
Highlight rows with Kutools for Excel
If the drop-down list contains multiple items, creating a separate Conditional Formatting rule for every item can take time. With the Colored Drop-down List feature in Kutools for Excel, you can assign colors to the items and apply those colors to entire rows in one dialog box. Download Kutools for Excel.
- Create the drop-down list you want to use.
- Click Kutools > Drop-down List > Colored Drop-down List.
- In the Colored Drop-down List dialog box:
- Select Row of data range in the Apply to section.
- Select the drop-down list cells and the data range whose rows you want to highlight.
- Assign a color to each drop-down list item.
- Click OK. When you select an item from the drop-down list, the entire row is highlighted with its assigned color.
Kutools for Excel - Supercharge Excel with over 300 essential tools, making your work faster and easier, and take advantage of AI features for smarter data processing and productivity. Get It Now
Video: Highlight rows based on a drop-down list
Frequently asked questions
Why is Conditional Formatting highlighting the wrong row?
Check that the row number in the formula matches the first row of the range selected in the rule's Applies to box. For data beginning in row 2 and drop-downs in column E, use =$E2="Status". The dollar sign should lock the column, not the row.
Can I use this method if the drop-down list is in another column?
Yes. Replace E in the formula with the column containing your drop-down list. For example, if the list is in column C, use a formula such as =$C2="Completed".
Why are newly added rows not highlighted?
The new rows may be outside the rule's Applies to range. Extend that range in Conditional Formatting Rules Manager, or format the source as an Excel Table so the formatting can expand with the data.
Can I highlight only the drop-down cell instead of the entire row?
Yes. For Conditional Formatting, apply the rule only to the drop-down cells. In Kutools, choose the option that applies color to the drop-down list cells rather than Row of data range.
Do blank drop-down cells receive a color?
Not with the exact-match formulas shown above. A blank cell does not equal Not Started, Completed, or In Progress, so no corresponding rule is applied.
Related articles
- Create a drop-down list with hyperlinks in Excel
- Create a drop-down list but show different values in Excel
- Create a drop-down list with images in Excel
- Increase the drop-down list font size in Excel
- Create a multilevel 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!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.
- All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license — set up in minutes (MSI-ready)
- Works better together — streamlined productivity across Office apps
- 30-day full-featured trial — no registration, no credit card
- Best value — save vs buying individual add-in