Excel Data Validation List With Color

In Excel for Microsoft 365, a standard data validation dropdown cannot display a different color beside each option while the list is open. You can, however, color the cell after someone selects an option by applying conditional formatting; for example, a selected status can turn green, yellow, or red.

What “color in a validation list” can mean

People asking for a “data validation list with color” usually want one of two things: colored choices inside the dropdown, or a colored cell showing the choice after selection. Excel’s built-in Data Validation list supports the second approach when paired with conditional formatting, but it does not provide a setting to assign a fill color to each item in the open dropdown.

That distinction matters when planning a workbook. A dropdown is a way to control which text or values users enter. Conditional formatting is a separate feature that changes a cell’s appearance when its value meets a rule. The two features work together, but they do not turn the dropdown menu itself into a color-coded palette.

For example, a project tracker might offer Not started, In progress, and Complete. The user chooses one of those labels from a dropdown, and Excel colors the cell gray, yellow, or green. The stored value remains text, which means it can still be filtered, counted, or used in formulas.

Create the dropdown list

First decide where the choices will live and where users will make selections. In this example, the status choices are in H2:H4, and users select a status in D2:D100. Keeping the source values in cells is easier to maintain than typing a long list directly into the validation dialog.

Enter Not started, In progress, and Complete in H2:H4. You can place the source list on a separate worksheet if you prefer to keep it away from the entry area.
Select D2:D100, or select the cells where the dropdowns should appear.
On the Windows ribbon, choose Data > Data Validation. In the dialog, open the Settings tab.
Set Allow to List. Leave In-cell dropdown checked, then enter the source reference or a named range in Source.
Choose OK and test a cell in the selected range. Its dropdown arrow appears when the cell is selected.

For a source list on the same worksheet, the Source box can refer to the range directly. With the choices in H2:H4, enter =$H$2:$H$4. The dollar signs keep the source range fixed. If the source list is on a different worksheet, Excel’s validation Source box generally needs a defined name rather than a direct cross-sheet reference.

Use a named range for choices on another sheet

A defined name gives the list a readable label and avoids putting a cross-sheet address directly into Data Validation. To create one in desktop Excel, select the cells containing the options, choose Formulas > Define Name, type a name such as StatusOptions, check that the reference points to the intended cells, and choose OK. Names cannot contain spaces.

Return to the entry cells, open Data > Data Validation, choose List, and enter =StatusOptions in Source. This is the source expression for the example where the named range StatusOptions refers to the three status choices. If you change the name or move the source cells, check that the defined name still refers to the right range.

For a short list that will not change, you can type items separated by commas in the Source box instead. This is quick, but it is less convenient to update and has a 255-character limit for a directly typed list. A cell range or defined name is usually a better choice for a list maintained over time.

Color the selected value with conditional formatting

Once the dropdown works, apply a separate formatting rule to its cells. For the example, select D2:D100, then choose Home > Conditional Formatting > New Rule. Select Use a formula to determine which cells to format, enter a rule, choose Format, set a fill color on the Fill tab, and confirm the dialogs. Repeat for the other statuses, choosing a different fill for each.

With the status values in D2:D100 and the first cell of the selected range at D2, use this rule for the completed status:

=D2="Complete"

Choose a green fill for that rule. For the same D2:D100 layout, create the In progress rule with =D2="In progress" and choose a yellow or amber fill. Create the Not started rule with =D2="Not started" and choose a neutral gray fill. Each formula checks the value in the current row; because the column and row references are relative, Excel adjusts the row as it applies the rule down the range.

Make sure the conditional formatting applies to the same cells as the dropdowns. In the Conditional Formatting Rules Manager, review the Applies to range if colors appear only in some rows. You can open the manager from Home > Conditional Formatting > Manage Rules. If your first selected cell is not D2, adjust the row reference in the rule to match the top-left cell of the range.

Color an entire row instead of one cell

In a tracker, coloring only the status cell may be too subtle. You can apply a rule to the whole record row and have it check the status column. Suppose statuses are in D2:D100 and each record occupies A:F. Select A2:F100 before creating the rule. For the same layout, use this formula for completed rows:

=$D2="Complete"

The dollar sign before D locks the status column, while the row number remains relative. Set the fill you want, then add equivalent rules for the other statuses. Because the Applies to range covers A:F, the chosen color extends across each matching record. Keep fills light enough that text remains legible, and avoid using a strong color that makes the row difficult to scan.

Row coloring can make a large tracker easier to read, but it also gives color more visual weight than the underlying status cell. Consider whether users need to see multiple statuses at a glance, and whether the workbook’s other formatting already uses color to mean something different.

Limits, comparisons, and alternatives

Conditional formatting changes the appearance of the selected cell or range; it does not add colored swatches to the dropdown choices. Excel’s ordinary in-cell validation menu is not a rich, individually formatted list. If users need to see the colors before choosing, put a small legend beside the input cells, or show each allowed value in a nearby reference table with its corresponding color.

Other controls may suit a specialized interface, but they come with trade-offs. Form controls and ActiveX controls are not interchangeable with ordinary worksheet validation, and their availability and behavior vary by platform. They can also be less convenient for copying, filtering, or using the workbook on the web. For most shared workbooks, a standard validation list plus conditional formatting is simpler and more portable.

You can also use text labels that communicate meaning without color, such as “Complete,” rather than relying on a green fill alone. Color is a helpful cue, not a replacement for the value. A reader may have difficulty distinguishing particular colors, print the sheet in grayscale, or view it on a display where the fill is hard to see. Clear labels and adequate contrast make the workbook more usable.

Common problems and fixes

The dropdown works, but no color appears: Check that conditional formatting is applied to the entry cells and that the rule text matches the dropdown value exactly. Extra spaces or different spelling can prevent a match.
Only the first cell changes color: Review the rule’s Applies to range. It should cover all intended cells, such as D2:D100, rather than only D2.
The wrong row is colored: Match the formula’s starting row to the top row of the Applies to range. For row shading, lock the status column with a dollar sign, as in the example, but leave the row relative.
The list source gives an error: Confirm that the source range exists and that a cross-sheet list is referred to through a defined name. Also check for misspelled names and invalid entries in the Source box.
Someone can type a value that is not on the list: In Data Validation, review the Error Alert tab and keep Show error alert checked if you want Excel to warn or block invalid entries. Validation is not a security control; pasted data and other editing methods can sometimes bypass expected restrictions.
A new option has no matching color: Add a conditional formatting rule for the new value, and update the source list. A dropdown list and its color rules are maintained separately.

Frequently Asked Questions

Can I make each item in an Excel dropdown a different color?
Not with the standard Data Validation dropdown. You can color the selected cell after a choice is made by adding conditional formatting rules, or provide a nearby color-coded legend.

Can a dropdown choice color the whole row?
Yes. Apply conditional formatting to the full row range and use a formula that checks the status column. Lock the column reference, such as =$D2="Complete", while leaving the row number relative.

Can I use a list stored on another worksheet?
Yes. Define a name for the source cells using Formulas > Define Name, then use that name in the Data Validation Source box with a leading equals sign.

Why does my color rule not match the dropdown value?
The rule’s text must match the selected value, including spaces and punctuation. Also check that the rule applies to the correct cells and that its relative reference begins on the top row of that range.

Does conditional formatting change the value in the cell?
No. It changes the cell’s appearance, not its contents. The selected text remains available for sorting, filtering, and formulas.

Will the colors show in Excel for the web or on Mac?
Conditional formatting and data validation are available in current Excel versions on Mac and in Excel for the web, though dialogs and some editing options can differ from Windows. If a workbook is shared across platforms, test the dropdown and formatting in the versions your audience uses.

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.