Add Color to Excel Data Validation Lists

How-to basics · Updated November 20, 2026

To add color to an Excel data-validation list, create the drop-down first, then use conditional formatting to color a cell according to the value selected. Excel does not color individual choices inside the standard drop-down menu, but it can color the cell after a choice is made. The steps below are for Excel for Microsoft 365 on Windows.

What You’ll Need

  • An Excel workbook and the cells where people will make a selection.
  • A short, consistent list of choices, such as Pending, Approved, and Rejected.
  • A color plan, for example yellow for Pending, green for Approved, and red for Rejected.

You can type the choices directly into Data Validation or keep them in worksheet cells. A source range is easier to maintain if the list might change. If you store the source list on another sheet, define a named range for it and use that name as the validation source; Excel may reject a direct reference to another worksheet in the Source box.

Step-by-Step

  1. Choose the cells that need a drop-down. Select the entry cells, such as B2:B100, or select one cell and copy its validation to other cells later. Avoid selecting the header row unless you also want a drop-down in the heading. Decide whether the color should appear only in the selected cell or across the whole record; the instructions below start with coloring the selected cell.
  2. Create the list of choices. If you want to type a small, fixed list, select the entry cells and go to Data > Data Validation. On the Settings tab, set Allow to List, make sure In-cell dropdown is selected, and enter the options in Source separated by commas, for example Pending,Approved,Rejected. Click OK. If your Excel regional settings use a different list separator, or the typed source is awkward to maintain, use a cell range instead.
  3. Use a worksheet range for a longer or editable list. Put one choice in each cell, such as E2:E4, and use that range as the Source. The list should not contain blank cells unless you want a blank choice. If the options are on the same sheet, select the source range with the range picker when you edit Data Validation. For options on a different sheet, create a name through Formulas > Define Name, give it a clear name such as StatusOptions, and enter =StatusOptions in the Source box. Click OK, then test the drop-down arrow in one of the entry cells.
  4. Make a rule for the first value. Reselect the cells that have the drop-down, for example B2:B100. Go to Home > Conditional Formatting > New Rule. Choose Format only cells that contain. In the rule description, set the first menu to Cell Value, the next menu to equal to, and enter the exact list item, such as Pending. Select Format, open the Fill tab, choose a light yellow fill, and click OK. Click OK again to create the rule. The cell will turn yellow when it contains Pending, whether the value was picked from the list or entered another permitted way.
  5. Add one rule for each remaining choice. Keep the same cells selected and repeat the New Rule process. Set the value to Approved and choose a green fill; then create another rule for Rejected and choose a red fill. Use the same spelling and spacing as the validation options. You do not need to create a different validation list for each color: the validation controls the allowed entries, while conditional formatting applies the appearance.
  6. Check the rules and their range. Go to Home > Conditional Formatting > Manage Rules. In the Show formatting rules for menu, choose This Worksheet if needed. Confirm that each rule names the right value and that Applies to covers the intended cells, such as =$B$2:$B$100. If you created a rule while only one cell was selected, edit Applies to so it includes the full entry range. Click Apply, then OK.
  7. Test each outcome. Select a cell with a drop-down, choose Pending, and confirm that its fill changes to yellow. Repeat with Approved and Rejected. Also try clearing the cell or choosing a different value to check that the old color is replaced rather than left behind. Save the workbook after the rules behave as expected.

To color an entire record instead of just the drop-down cell, use a formula-based conditional-formatting rule. For example, if the status is in column B and the records occupy A2:D100, select A2:D100, choose Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format, and enter =$B2="Approved". Set the fill, then create similar rules for the other statuses. The dollar sign fixes the status column while the row number adjusts for each record. Set Applies to to =$A$2:$D$100. This makes each row’s formatting depend on its own status cell.

On Mac, the Conditional Formatting and Data Validation commands are also on the Data and Home tabs, though dialog layouts can differ slightly by Excel release. Excel for the web supports ordinary list validation and conditional formatting, but some advanced rule-editing options may be easier to access in the desktop app. If a command is missing or looks different, open the workbook in the Microsoft 365 desktop version.

Common Mistakes

  • Expecting the menu choices themselves to be colored. The standard in-cell validation menu is not a color-coded menu. Conditional formatting colors the cell after a value is present. If users need a visual legend, place colored labels beside the entry area or create a separate key.
  • Using different text in the rule and the list. A rule for Approved will not match a value such as Approved with a trailing space, or approval. Copy the exact wording from the source list and keep spelling consistent.
  • Applying a rule to the wrong cells. A rule may work in the first test cell but not in the rest of the column if Applies to is too short. Check the range in Manage Rules, especially after inserting rows or copying cells.
  • Choosing a hard-to-read fill. Very dark fills can make black text difficult to read, while similar colors may be hard to distinguish. Use clear, light fills and retain readable text; do not rely on color alone to communicate an important status.
  • Assuming validation prevents every invalid entry. Pasting data or using some automated methods can bypass or replace validation. A drop-down is helpful guidance, but it does not guarantee that every value in the cells is valid.

Tips

  • Use short labels and a consistent naming style. If a list item changes, update both the source and any conditional-formatting rule that refers to it.
  • Keep the source options in one place and consider converting the source list to an Excel table if it will grow. A table makes the list easier to manage, though you should verify that the validation source includes newly added options.
  • Choose fills with enough contrast and add text labels, symbols, or a nearby legend where users may not distinguish colors easily.
  • Use Home > Conditional Formatting > Manage Rules to review or edit the rules instead of layering new rules whenever a color needs changing.
  • For a one-off value, you can use the fill color button manually, but manual formatting will not reliably update when a different drop-down choice is selected. Conditional formatting is the better choice for repeatable behavior.

Troubleshooting

The cell does not change color: Check that the selected value exactly matches the rule, including spaces. Open Manage Rules and confirm that the rule is enabled and that Applies to includes the cell you changed. If several rules exist, edit or remove obsolete rules that could cause confusing results.

The drop-down arrow or list is missing: Select the cell and open Data > Data Validation. Confirm that Allow is set to List and In-cell dropdown is checked. If the Source points to a range, check that the range still contains the expected choices and that a named range has not been deleted or renamed.

Only some rows work: The conditional-formatting range may stop partway down the column, or the rule may refer to the wrong cell. In Manage Rules, expand Applies to. For a whole-row formula rule, confirm that the formula uses the status column from the first selected row, such as B2 for a range beginning at row 2, and that the column is fixed with a dollar sign.

A pasted value has no color or an unexpected color: Check for extra spaces, different punctuation, or a value that is not in the rule set. Paste Special can also bring formatting from elsewhere and alter the appearance. Re-select the intended value from the validation list, then inspect the rule and the cell’s formatting if the issue remains.

Frequently Asked Questions

Can I color each option inside the Excel drop-down menu?
No. Excel’s standard Data Validation list does not assign a separate font or fill color to each menu item. Use conditional formatting to color the cell after a choice is made, or provide a separate color-coded legend.

Does conditional formatting work if someone types the value instead of selecting it?
Yes, if the typed value exactly matches the text in the rule. Conditional formatting checks the cell contents, not how the value was entered. Data Validation’s error alert can discourage entries that are not on the list.

Can one rule color the cell for several choices?
Yes, but separate rules are usually clearer when each choice needs its own color. A single rule can apply one shared format to multiple values using a formula or other rule setup, but verify the result in Manage Rules before distributing the workbook.

Can I apply the colors to an entire row?
Yes. Select the row range and use a formula-based conditional-formatting rule that checks the status cell in each row. Keep the status column fixed in the reference and let the row reference change as the rule applies down the data.

Will the colors update if I change the drop-down choice?
Yes. With conditional formatting set up correctly, the cell’s appearance updates when its value changes. If an old color remains, check for manually applied fill or a competing conditional-formatting rule.

Can I use this in Excel for the web?
Basic list validation and conditional formatting are available in Excel for the web. For named ranges or more complex rule management, use the desktop app if the web interface does not expose the setting you need.

Related

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.