How to Add Color to a Dropdown List in Excel with Rules

To add color to a dropdown selection in Excel, keep the dropdown list as it is and use conditional formatting to color the cell after someone chooses an item. Excel’s standard Data Validation dropdown cannot display each menu option in a different color, but conditional formatting can apply a fill or font color to the selected value automatically.

The steps below are for Excel for Microsoft 365 on Windows. They work with an existing list or one you create first. The example colors a status cell red, yellow, or green depending on whether its value is Pending, In progress, or Complete.

What You’ll Need

An Excel workbook with the cells where people will make dropdown selections.
The list of permitted values, such as Pending, In progress, and Complete. You can type the values into the Data Validation box or keep them in worksheet cells.
A few minutes to create a conditional formatting rule for each value that needs its own color.

Step-by-Step

Choose the cells for the dropdowns. Select the cell or range where people will make choices. For the example, use D2:D100 for status entries. If you already have dropdowns, note their range and continue to step 4. Applying the rules to the full range now means new entries in those rows can be colored without repeating the setup.
Create the dropdown list if you need one. With the target cells selected, choose Data > Data Validation. In the Settings tab of the dialog box, set Allow to List. In Source, type the choices separated by commas, for example Pending,In progress,Complete, then make sure In-cell dropdown is checked. Select OK. If your values are already stored in a worksheet range, select that range in Source instead. A cell range is easier to maintain when the list changes, and it avoids editing the validation rule every time you add an option.
Check that the dropdown works. Select one of the target cells and open its dropdown arrow. Choose an item, or type a listed value if appropriate. Data Validation controls which values are offered; it does not set their colors. Keep the spelling and spacing of the list values consistent, because the formatting rules will look for exact text.
Select the cells that should change color. For the example, select D2:D100. If you want different columns or separate blocks to use the same rules, you can select nonadjacent ranges by holding Ctrl while selecting each range. For a simple first setup, applying the rules to one continuous range is less error-prone.
Open the formula-based conditional formatting rule. Choose Home > Conditional Formatting > New Rule. In the New Formatting Rule dialog, select Use a formula to determine which cells to format. This rule type checks the value in each cell and applies formatting when its formula returns TRUE.
Create the first color rule. Assuming the dropdown choices are in D2:D100 and the first cell in the selected range is D2, enter =$D2="Pending" in the formula box. Select Format, choose a red fill on the Fill tab, and select OK. The dollar sign before D keeps the rule looking in column D, while the row number can change as Excel evaluates each cell. Choose a font color as well if it improves readability.
Add a rule for the next choice. With D2:D100 selected, choose Home > Conditional Formatting > New Rule again and select the formula option. Assuming the same D2:D100 layout, enter =$D2="In progress". Select Format, choose a yellow or amber fill, and confirm. Use the exact text and spacing shown in the dropdown list.
Add the remaining choice rules. Create another formula-based rule for Complete, select a green fill, and confirm. Assuming the dropdown cells are D2:D100 and D2 is the first cell, use =$D2="Complete". Each rule should have its own format. You can use more than three values; create one rule for each value that needs a distinct appearance.
Test each option and save. Choose Pending, In progress, and Complete in several cells in the range. Confirm that each selection produces the intended fill and that blank cells remain unformatted. Change a selection to another value and check that the old color disappears. Save the workbook after the tests.

If you want to color the entire row rather than just the dropdown cell, select the row range first and write each rule to refer to the dropdown column. For example, assuming the dropdown is in D2:D100 and the rows to format are A2:F100, use =$D2="Pending" for the pending rule and set Applies to to A2:F100. The fixed column reference makes Excel check column D while coloring cells across each matching row.

Common Mistakes

Expecting the menu items themselves to be colored. Standard Excel Data Validation lists do not offer per-item font or fill colors in the dropdown menu. The selected cell can be colored once an option is chosen. If the menu itself must show colored entries, that requires a different custom solution, such as a form control or VBA, and is more involved than the built-in list.
Using a rule for the wrong cell or range. If the formula refers to D2 but the first cell in Applies to is D3, the formatting may be offset. Match the formula’s starting row to the top row of the range. Review the rule’s Applies to field if some cells do not respond.
Leaving out or misplacing dollar signs. For a vertical dropdown range, =$D2="Pending" fixes the column but lets the row adjust. Using =$D$2="Pending" makes every cell look only at D2, so the other dropdown cells may all take their color from the first one.
Using text that does not match exactly. Extra spaces, different punctuation, or a different label in the rule can prevent the expected match. Copy the value from the list source when possible, and keep the wording consistent.
Assuming data validation colors cells automatically. A list rule and a formatting rule do separate jobs. The first offers or restricts choices; the second changes appearance. Both must be in place for a color-coded result.

Tips

Pick colors with a clear meaning. For example, use red for an issue, amber for work in progress, and green for complete. Add readable text labels so the status is understandable even when color is hard to distinguish.
Use a worksheet range for a changing list. Put the allowed values in a small reference area and point Data Validation to that range. This is easier to update than a long comma-separated Source entry. If you use a range on another worksheet, set up a named range if Excel does not accept the direct reference in the Source box.
Apply formatting to future rows. Set the Applies to range to cover the rows you expect to use, such as D2:D500, rather than just the current entries. Avoid selecting an unnecessarily large area if it makes the workbook harder to manage.
Keep the rule formulas simple. For a direct text choice, an equality check is usually clearer than a more complicated formula. If your dropdown values are numeric or dates, create rules that match those actual values and test them with sample selections.
Review rules when copying cells. Copying and pasting can bring conditional formatting rules along with the cell. If unexpected colors appear, use Home > Conditional Formatting > Manage Rules to inspect the rules and their Applies to ranges. Set Show formatting rules for to the current selection or worksheet as needed.
Consider the audience and platform. The steps use Excel for Microsoft 365 on Windows. Excel for Mac has the same general Data Validation and Conditional Formatting features, though dialog layouts can differ. Excel for the web also supports dropdowns and conditional formatting; if a specific rule editor or formatting option is not available in your browser, open the workbook in the desktop app.

Troubleshooting

The selected cell does not change color. First check that conditional formatting is enabled for the cell: open Home > Conditional Formatting > Manage Rules and verify that the rule applies to the cell you changed. Confirm the formula starts with the first row of the Applies to range and that the quoted text matches a dropdown option. Also check that another rule is not applying a conflicting format.

Every dropdown cell shows the same color. Look for a fully fixed cell reference such as =$D$2="Pending". For a rule covering D2:D100, use a relative row reference such as =$D2="Pending" so Excel checks each row separately. Make sure the Applies to range begins at D2 if the formula uses row 2.

The wrong color appears. Open Manage Rules and check for overlapping rules, incorrect labels, or a range that includes cells you did not intend to format. Use Conditional Formatting > Clear Rules carefully if you want to remove rules; choose the option for the selected cells rather than the whole worksheet unless that is your intention. Recreate one rule and test it before adding the others.

The dropdown arrow is missing. Select the cell and open Data > Data Validation. Confirm Allow is set to List and In-cell dropdown is checked. If the setting is correct but the arrow is not visible, click the cell; Excel commonly displays the arrow only while the cell is selected. Also check whether the worksheet is protected or whether the cell was replaced by a pasted value.

The rule works for some entries but not others. Compare the actual cell contents with the list source. A trailing space or an alternate spelling can make a rule fail. Re-select the value from the dropdown rather than typing it, then confirm that the corresponding rule uses the same wording. If the source values are formulas, check that their displayed results match the text in the conditional formatting rule.

Frequently Asked Questions

Can I color each option inside the dropdown menu?

Not with Excel’s standard Data Validation dropdown. You can color the cell after a user selects an option by using conditional formatting. Coloring the choices inside the open menu requires a custom approach and is not a built-in setting for an ordinary cell dropdown.

Can I use more than three colors?

Yes. Add a separate conditional formatting rule for every list value that should have a distinct appearance. Choose clear, readable fills and check Manage Rules if the rules overlap or the results become difficult to maintain.

Will the colors update when I change the selection?

Yes. Conditional formatting recalculates when the cell value changes. When you replace one dropdown choice with another, the old rule should stop matching and the new choice’s format should appear.

Can I color a whole row based on one dropdown cell?

Yes. Select the row range you want to format, then make each rule refer to the dropdown column with a fixed column and relative row reference. For example, if the status is in column D, use a formula like =$D2="Complete" for a range beginning on row 2.

Can I use this with a dropdown list stored on another sheet?

Yes. You can store the allowed values on another worksheet and use a named range as the Data Validation source. The conditional formatting rule still checks the selected value in the dropdown cell, so it does not need to refer to the list’s source worksheet.

Does the method work in Excel for Mac or the web?

The same basic approach works in Excel for Mac and Excel for the web, though the controls and dialog layouts can differ from Windows. If you cannot find a particular rule or formatting option in the web interface, open the workbook in Excel for Microsoft 365 on desktop.

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.