How to Make a Drop-Down List With Color in Excel

To make a drop-down list with color in Excel, you combine two features: a Data Validation list for the choices, and conditional formatting rules for the colors. Excel's drop-down can't store a color for each item, but a rule can color the cell as soon as someone picks "Done" or "Late". This guide builds a color-coded status column step by step, then colors whole rows and shows how to count each status.

How a colored drop-down list works#

A drop-down list only holds plain text. Conditional formatting is a rule that changes a cell's look when a condition is true, such as "the cell equals Done". Put the two together and the color follows the value.

The examples use a task tracker with a Status column in C2:C8:

Because the color comes from the value, it updates instantly when the status changes, and it works in every desktop version of Excel.

Step 1: Create the drop-down list#

First add the list of choices with Data Validation:

1. Select C2:C8.
2. Go to Data > Data Validation. On Windows, you can press Alt, A, V, V.
3. In Allow, choose List.
4. In Source, type Not started,In progress,Done, or select cells that hold the three options.
5. Click OK.

Each cell in C2:C8 now has an arrow with the three choices. For more options, such as error messages and lists that grow, see how to create a drop-down list in Excel. If your options live on another sheet, follow the guide to drop-downs from another sheet.

Step 2: Add a color for each option#

Next, add one conditional formatting rule per option:

1. Select C2:C8 again.
2. Go to Home > Conditional Formatting > Highlight Cells Rules > Equal To. On Windows, the keys Alt, H, L open the menu.
3. Type Done in the box.
4. In the with list, choose Green Fill with Dark Green Text, or pick Custom Format and set your own fill on the Fill tab.
5. Click OK.

Repeat for "In progress" with a yellow fill and "Not started" with a red fill. Pick a status from a drop-down and the cell changes color at once.

Tip: instead of typing Done in step 3, click the cell where your list options are stored, such as G4. The rule then reads =$G$4. If you later rename "Done" to "Complete" in G4, the rule keeps working.

Color the whole row based on the drop-down#

To color the entire task row, not just the status cell, use a formula rule:

1. Select A2:C8, starting from row 2.
2. Go to Home > Conditional Formatting > New Rule.
3. Choose Use a formula to determine which cells to format.
4. Enter this formula:

=$C2="Done"

5. Click Format, choose a fill on the Fill tab, and click OK twice.

The $ before C locks the column, so every cell in the row checks its own Status cell. The 2 has no $, so row 3 checks C3, row 4 checks C4, and so on. Without the $, only part of each row would change color. Add one formula rule for each status you want to color.

Manage, edit and order the rules#

All the rules live in one place: Home > Conditional Formatting > Manage Rules. Set Show formatting rules for to This Worksheet to see everything.

From there you can:

• Edit a rule's text or color.
• Change the range in Applies to, for example from =$C$2:$C$8 to =$C$2:$C$200 when the list grows.
• Reorder rules with the arrows. If two rules apply to the same cell and set the same thing, such as the fill, the rule higher in the list wins.

Turning your list into an Excel table (Ctrl+T on Windows, ⌘T on a Mac) also helps: new rows usually pick up the drop-down and the color rules automatically. For a list of options that grows on its own, see dynamic drop-down lists.

Filter and count by color#

Once statuses are colored, you'll want to filter and count them. Filter works with colors; formulas work with values.

To filter by color, turn on filters with Ctrl+Shift+L on Windows or ⌘+Shift+F on a Mac. Click the arrow in the Status header, choose Filter by Color, and pick a fill. You can also just filter by the text "Done", which gives the same rows.

To count each status, count the value instead of the color. Excel has no built-in function that counts fill colors, but the color comes from the value, so the count is the same:

=COUNTIF(C2:C8,"Done")

In the task tracker, that returns 3. Copy it for "In progress" (2) and "Not started" (2) to build a small summary.

Why the colors don't show#

When a color rule doesn't fire, the value usually doesn't match exactly:

• Extra spaces. "Done " with a trailing space doesn't equal "Done". Using the drop-down, rather than typing, prevents this.
• Pasted values. Pasting over cells can remove the drop-down. Use Paste Special > Values to keep it.
• Wrong Applies to range. New rows outside the rule's range stay uncolored. Extend the range or use a table.
• A missing $. In a whole-row rule, =C2="Done" instead of =$C2="Done" colors only some cells.
• Rules that clash. Two rules with different fills on the same cell: only the higher one shows.

For color scales and data bars on numbers rather than text, see data bars and color scales in Excel. If one list depends on another, the guide to dependent drop-down lists shows how to link them.

Practise it#

The color in a colored drop-down is pure conditional formatting, a skill many job tests check. Practise conditional formatting in a live spreadsheet: you'll highlight values, color whole rows with a $ formula rule and see the result instantly. Then practise filtering a table to show and count just the rows you need. Or start with the counting exercise on this page.