How to Add Color to a Drop-Down List in Excel

To add color to a drop-down list in Excel, create the list with Data Validation, then use conditional formatting to color the cell after someone selects an option. Excel’s standard in-cell drop-down does not let you assign a different color to each option inside the open menu; conditional formatting colors the cell that displays the chosen value.

The steps below are for Excel for Microsoft 365 on Windows. They work well for status fields such as Pending, In Progress, and Complete. You will first place the available choices in worksheet cells, set up the drop-down, and then create a separate color rule for each choice.

What You’ll Need

An Excel workbook with the cell or cells where you want the drop-down.
A list of the choices you want people to select. Keeping the choices in worksheet cells makes them easier to edit later.
A few minutes to create the validation list and conditional formatting rules.

Step-by-Step

Enter the choices in worksheet cells. Choose an unused area of the sheet, or create a separate sheet for reference values. For this example, enter Pending in H2, In Progress in H3, and Complete in H4. These cells are the source list. You can use different choices, but make a note of their exact spelling because the formatting rules will need to match them.
Select the cell or range that should have a drop-down. Click the first cell where a user will choose a status. To put the same drop-down in several cells, select the whole range before opening Data Validation. For instance, select B2:B50 if those are the status cells in your table. Avoid selecting the header row unless you also want a drop-down in the heading.
Set up the validation list. On the ribbon, choose Data > Data Validation. In the Data Validation dialog, open the Settings tab, set Allow to List, and make sure In-cell dropdown is selected. Click in Source, then select H2:H4 on the worksheet, or enter =$H$2:$H$4. The dollar signs keep the source range fixed if the validation is copied. Click OK. The selected cells should now show a drop-down arrow when selected. If the source cells are on another worksheet, Excel may not accept a direct cross-sheet reference in the Source box; use a named range for that setup, as described in Tips.
Confirm that the choices work before adding colors. Click a validated cell and open its arrow. Select Pending, In Progress, or Complete. Check that the selected value appears in the cell. This confirms the list is working independently of the color rules and makes it easier to spot whether a later problem is in validation or formatting.
Select the cells that should change color. Select the same range that has the drop-down, such as B2:B50. If you are applying formatting to just one cell, select that cell. Conditional formatting applies to the selected cells, so this step is important: creating rules while a different range is selected can leave the intended drop-down cells uncolored.
Create the first color rule. Choose Home > Conditional Formatting > Highlight Cells Rules > Equal To. Type Pending in the value box, choose a suggested format, or select Custom Format to choose a fill color and, if desired, a font color. Click OK to save the rule. Choose a light fill if the cell text needs to remain easy to read.
Add a rule for each remaining choice. With the same target range selected, repeat Home > Conditional Formatting > Highlight Cells Rules > Equal To. Create a rule for In Progress and pick a different format, then create another for Complete. Each rule should use the exact text in the source list. For example, you might use yellow for Pending, blue for In Progress, and green for Complete. The colors are your choice; consistent, distinguishable fills matter more than any particular color scheme.
Test all options and a blank cell. Open the drop-down in several target cells and try each choice. Confirm that each selected value gets the intended fill and that a blank cell stays uncolored. Also test a cell that was not included in the selected range. If that cell does not respond, it may simply be outside the rules’ applied range rather than a problem with the list.
Review the rules if a color is missing or applied to the wrong cells. Select a cell in the range and choose Home > Conditional Formatting > Manage Rules. Review the rule descriptions and the Applies to ranges. Make sure each rule refers to the intended value and covers all the drop-down cells. Correct the range or text, then click Apply and OK.

Conditional formatting responds to the value currently in each cell. If someone changes a selection from Pending to Complete, Excel removes the first matching appearance and displays the format for Complete. The color is therefore a visual cue, not a change to the drop-down choices or the underlying value.

Common Mistakes

Expecting the open menu options to have individual colors. Standard Data Validation lists display choices as text in the menu; they do not support a separate background color for each choice. The method here colors the cell after an item is selected. Coloring the cells that hold the source values does not make the menu options inherit those colors.
Using text that does not exactly match. If the list says In Progress but a rule looks for In progress or In-Progress, the rule may not produce the intended result. Copy the option text from the source cell when entering the rule. Extra spaces at the beginning or end can also cause confusion.
Applying conditional formatting to the wrong range. A rule created while only one cell is selected may apply to just that cell. Check Applies to in Manage Rules, especially if some rows color correctly while others do not.
Choosing a format that is difficult to read. A dark fill with dark text, or a very bright fill behind small text, can make status values hard to scan. Use readable contrast and check how the colors appear at normal zoom and when printed.
Assuming the fill is permanent. Conditional formatting is a rule-based appearance. If a value changes or no longer matches any rule, the associated color disappears. That is normally the desired behavior for a live status field.

Tips

Keep source choices in one place. A cell-based source list is easier to maintain than typing all choices into the Source box. Edit the source cells when you need to revise a label, and update the corresponding conditional formatting rule if its exact text changes.
Use a named range for choices on another sheet. Select the source cells, click the Name Box to the left of the formula bar, type a simple name such as StatusChoices, and press Enter. In Data Validation, set Allow to List and use =StatusChoices in Source. Avoid spaces in the range name. Named ranges are useful because a validation list can refer to them even when its source cells are on a different worksheet.
Make the rules easy to maintain. Use a small, consistent set of colors and keep each rule tied to one choice. If you rename an option, update both the source list and its formatting rule. For a long list, consider whether color is helping users distinguish meaningful groups or just adding visual noise.
Use color alongside text. Do not rely on color alone to communicate an important status. The cell still contains a readable word, so users can understand it in grayscale printouts or if they have difficulty distinguishing colors.
Copy validation carefully. If you need to extend the drop-down to new rows, copy a cell that already has the validation or select the expanded range and set up validation there. Check the conditional formatting Applies to range too; extending one feature does not always mean the other has been extended.

On Excel for Mac, the same approach is available: use the Data tab’s Data Validation command to create the list, then use the Home tab’s Conditional Formatting options to create the color rules. Ribbon labels or dialog layouts can vary slightly by release. Excel for the web also supports data validation and conditional formatting, though some rule-editing controls may be more limited or arranged differently. If a needed dialog option is unavailable in the browser, open the workbook in the desktop app.

Troubleshooting

The drop-down arrow is missing. Select the cell and open Data > Data Validation. Confirm that Allow is set to List and In-cell dropdown is checked. Also verify that the cell is not part of a protected sheet where editing is restricted. The arrow may appear only when the cell is selected.
The source range is rejected. Check that the source cells contain the choices and that the reference begins with an equals sign. If the list is on a different worksheet, create a named range and enter its name with an equals sign in the Source box. If you typed the choices directly, your Excel regional settings may require a different list separator; a cell range avoids that separator issue.
The selected value does not change color. Open Manage Rules and check that the rule uses the same text as the selected value and that its Applies to range includes the cell. Confirm that the rule was saved and that another conditional formatting rule is not taking precedence. Try selecting the value again from the drop-down rather than typing a similar-looking label manually.
Only some rows change color. Inspect the Applies to field for each rule. If the range stops at B10 but your data continues to B50, extend it to cover the additional rows. Also check that the formatted range and the validation range refer to the same intended cells.
The wrong color appears. In Manage Rules, check for duplicate rules or rules that match more than one value. If multiple rules can apply to the same cell, adjust the rules or their order. You can edit a rule’s format there without rebuilding the drop-down.
The colors disappear after editing a choice. Conditional formatting matches the current cell text. If you changed a source label, update the matching rule to use the new wording. If someone typed a value that is not one of the approved choices, no exact-match rule may apply; select a valid item from the list.

Frequently Asked Questions

Can I color each option inside the drop-down menu?
No. Excel’s standard Data Validation in-cell menu does not offer individual colors for its options. You can color the selected cell with conditional formatting, or explore a different interface if colored menu items are essential.

Will the color update when I choose a different item?
Yes. When the cell’s value changes to another option covered by a rule, Excel displays the corresponding conditional format. The cell keeps the selected text; only its appearance changes.

Can I use this with a drop-down that already exists?
Yes. Leave the existing Data Validation settings in place. Select the cells that contain the drop-down and create conditional formatting rules for the existing choices.

Can I color the whole row based on the drop-down value?
Yes. Select the full rows or table range you want to color and create conditional formatting rules based on the status cell in each row. For more complex row-wide rules, use a formula-based conditional formatting rule and make the status column reference absolute while leaving the row reference relative. The cell-only method in this guide is simpler when only the drop-down cell needs color.

Do I need VBA to add color?
No. Data Validation and Conditional Formatting handle this common task without a macro. VBA is only worth considering for specialized behavior that normal conditional formatting cannot provide, and macros require additional security and maintenance.

Does this work in older versions of Excel?
Data Validation lists and conditional formatting are available in many desktop versions of Excel. Ribbon wording and dialog layouts can differ, so if a command is not in the same place, look under the Data tab for validation and the Home tab for conditional formatting.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.