Excel Color-Coded Drop-Down Lists: Setup and Rules

To make a color-coded drop-down in Excel for Microsoft 365 on Windows, create a list with Data Validation, then use Conditional Formatting to color the cell according to the option selected. The drop-down menu itself does not color each option; the selected value in the worksheet changes color. You will need a few list items, a place to store them, and a color choice for each one.

What You’ll Need

An Excel workbook and a worksheet where you want the drop-down.
A short set of options that will appear in the list, such as Not started, In progress, and Done.
A spare area on the same worksheet to store those options. This keeps the example simple and avoids needing a named range for a list stored on another sheet.
A plan for which fill color should represent each option. Choose colors with readable contrast, especially if the sheet will be printed or shared.

Step-by-Step

Choose the drop-down cell or range. Decide where people will make a selection. In this example, column B is for task status, with a heading in B1 and drop-downs in B2:B50. The options will be stored in G2:G4 on the same worksheet. Using a range rather than a single cell lets you apply the same choices and color rules to several rows. If your worksheet has different headings or data, use the equivalent locations in your own workbook.
Enter the options in a helper range. Type Not started in G2, In progress in G3, and Done in G4, with one option per cell. These entries are the source for the drop-down, so make them clear and consistent. Avoid adding spaces before or after the words; a value with an extra space may not match the text used in a color rule. Keep the source cells on the same worksheet for this setup. You can label G1 as Status options to make the helper range easy to recognize later.
Create the drop-down with Data Validation. Select B2:B50. Open Data > Data Validation. In the Settings tab, set Allow to List. In Source, enter =$G$2:$G$4, and make sure In-cell dropdown is checked. The dollar signs keep the source range fixed if you later copy a cell. Select OK. Click a cell in B2:B50 and use its arrow to confirm that all three options appear. On some Excel builds, the Data Validation dialog may be displayed in a slightly different layout, but the Allow, List, and Source settings are the ones to look for.
Add a color rule for the first option. Select B2:B50 again, then choose Home > Conditional Formatting > Highlight Cells Rules > Equal To. Enter Not started in the value box. Open the formatting choice and select Custom Format if you want a particular fill color. In the Format Cells dialog, use the Fill tab to choose a pale red or another suitable color, then confirm the dialogs. The rule compares each cell in the selected range with the option text, so a matching selection receives the format. Use a fill that still allows the words to be read clearly.
Repeat the rule for the other options. Keep B2:B50 selected and add another Equal To rule for In progress, choosing a different fill, such as pale yellow. Add a third rule for Done and choose a contrasting fill, such as pale green. The text entered in each rule must match the list option exactly, including spaces and punctuation. Applying the rules to the same range is important: if a rule only covers B2, the other drop-down cells will not change color.
Test the finished setup. Select B2 and choose each option in turn. Check that Not started, In progress, and Done each show the intended fill. Repeat the test in B3 and a lower row, such as B50, to confirm that the rules cover the full range. Try replacing a selection with a different option and make sure the old color disappears. Conditional Formatting responds to the cell’s current value, so the color should update when the selection changes.

On Excel for Mac, the same approach works: use the Data tab to find Data Validation, then use the Home tab’s Conditional Formatting menu to create Equal To rules. Ribbon labels or dialog placement can differ slightly by release. Excel for the web supports common validation and conditional-formatting tasks, but if a specific dialog or formatting option is missing, open the workbook in the desktop app and finish the setup there.

Common Mistakes

Expecting the menu choices themselves to have colored backgrounds. Standard Data Validation lists do not assign a separate color to each option in the opened menu. Conditional Formatting colors the worksheet cell after a choice is made. If you need colored buttons or a specially formatted selection interface, a standard in-cell list is not that kind of control.
Applying rules to only one cell. If you selected B2 before creating a rule, Excel may apply the format only to B2. Select the full destination range first, or edit the rule afterward so it covers B2:B50.
Typing different wording in the rule and source list. For example, a rule for In progress will not match In-progress or In progress with a trailing space. Copy the option wording from the helper cells when creating each rule.
Putting the list source on another worksheet without preparing it. A direct reference to a different sheet may not be accepted as a Data Validation list source. This guide keeps the source on the same worksheet. If you prefer a separate list sheet, define a named range and use that name as the validation source.
Using colors that are hard to read. Dark fills behind dark text, or subtle colors that look alike, can make the status unclear. Pick distinct fills and check that the cell text remains legible.

Tips

Keep the options short and unambiguous. Status names such as Not started, In progress, and Done are easier to scan than long sentences. A clear label also makes it easier to create a matching Equal To rule.
Use a restrained palette. Three distinct light fills are often easier to read than saturated colors. Do not rely on color alone if the information is important; the words in the cell should still explain the status.
Plan for future rows. If you expect the list to grow beyond row 50, select a larger destination range when setting up both Data Validation and Conditional Formatting. Alternatively, extend each range when adding rows and verify that the new cells have the list and rules.
Keep helper values easy to maintain. Leave a note or heading near the source options so another editor knows what they control. If you hide the helper column, remember where the values are before editing the list later.
Review formatting rules if the result looks inconsistent. Go to Home > Conditional Formatting > Manage Rules to inspect the rules and the range they apply to. This is useful when a workbook already has other formatting rules on the same cells.

Troubleshooting

The drop-down arrow does not appear: Select a cell that should have the list and open Data > Data Validation. Check that Allow is set to List and In-cell dropdown is checked. Confirm that the selected cell is inside the range you configured. If the worksheet is protected, check whether protection settings prevent editing or using the validation control.
The list is empty or shows the wrong items: Reopen Data Validation and inspect Source. For this example, it should point to G2:G4 on the same sheet. Check that those cells contain the intended text and are not blank. If you moved the helper list, update the source reference to its new location.
The selection changes but no color appears: Open Home > Conditional Formatting > Manage Rules. Check that each rule is an Equal To rule with the right wording and that Applies to includes the destination cells, such as B2:B50. Also check that the cell value is identical to the rule’s value. A space or punctuation difference can prevent a match.
Only some rows change color: The Applies to range may be too short, or the rule may have been created while only one cell was selected. In Manage Rules, edit the range to cover all intended cells. Check the range for every color rule, not just the first one.
A color appears to override another format: Open Manage Rules and review rules that apply to the same cells. Remove an obsolete rule or adjust its range. If the workbook has several conditional formats, inspect their order and options rather than adding more rules blindly.
You need colors inside the open menu: That is a limitation of the standard Data Validation drop-down. Keep the color-coded selected cell, or consider a different interface if colored choices before selection are essential. Do not expect changing the cell’s conditional format to recolor the menu entries.

Frequently Asked Questions

Can each item in an Excel drop-down menu have its own color?
Not with the standard Data Validation list. You can use Conditional Formatting to color the cell after someone chooses an item, which is usually enough to make the selected status easy to spot.

Can I color the whole row based on the drop-down choice?
Yes. Create conditional-formatting rules for the row range and base them on the status cell in that row. For example, if status is in column B, select the row area you want formatted and use a formula-based rule that checks column B. Keep the column fixed and the row relative so each row checks its own status.

Can I use the same list for several columns?
Yes. Select the cells that should have the same choices and apply Data Validation to that selection. Make sure each Conditional Formatting rule covers every cell that should receive a color, or add separate rules for separate ranges.

Can the list options be stored on another worksheet?
Yes, but a named range is a reliable way to reference a list on another sheet. Define a name for the option cells, then use that name in the Data Validation Source box. For a quick setup, keeping the source range on the same worksheet avoids that extra step.

Will the color change if someone types a valid option instead of choosing it?
Yes, if the typed text exactly matches one of the options and the cell’s conditional-formatting rule covers that cell. Data Validation can also be configured to show an error alert for entries that are not on the list.

Does this work in older versions of Excel?
Data Validation lists and basic Conditional Formatting are available in many desktop Excel versions, although ribbon layouts can vary. These instructions target Excel for Microsoft 365 on Windows. If a command is in a different place in your version, look for Data Validation on the Data tab and Conditional Formatting on the Home tab.

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.