How to Add Colour to Data Validation Cells in Excel

How-to basics · Updated November 18, 2026

To colour cells based on a data-validation dropdown in Excel, select the cells and use Home > Conditional Formatting > New Rule to apply a format when a chosen value appears. Data Validation creates and checks the dropdown; Conditional Formatting colours the cell after a selection is made. You can use a fill colour, font colour, or both.

The steps below are for Excel for Microsoft 365 on Windows. The same approach works in most recent desktop versions. One important limitation: Excel’s standard in-cell dropdown does not colour each option inside the open list. Instead, conditional formatting colours the cell after a person chooses an option.

What You’ll Need

  • An Excel workbook with a cell or range that already has a Data Validation list, or the values you want to use for a new list.
  • A short set of clear dropdown options, such as Pending, In progress, and Complete.
  • A colour plan that remains readable. For instance, use a light red fill for Pending and a light green fill for Complete, with dark text for contrast.

If you are starting from scratch, put the dropdown options in a spare part of the worksheet. For example, enter Pending in A2, In progress in A3, and Complete in A4. You can hide that source column later, but keep it available while setting up the list.

Step-by-Step

  1. Identify the dropdown cells and the exact option text. Click a cell with a dropdown arrow and note the choices, including spaces and punctuation. The conditional formatting rule must match the text Excel stores in the cell. If the list includes Complete, a rule looking for Completed will not match it. For the example below, the dropdown range is B2:B20 and the options are Pending, In progress, and Complete.
  2. Create the dropdown if you do not already have one. Select B2:B20, then choose Data > Data Validation. In the Settings tab, set Allow to List. In Source, enter =$A$2:$A$4, then select OK. If you type the choices directly instead, separate them with commas, such as Pending,In progress,Complete. A cell-range source is easier to maintain when options change. In Excel for the web, open Data > Data Validation and use the validation pane; the labels and layout can differ slightly from the desktop dialog.
  3. Select the full range you want to colour. Select B2:B20 before creating the rules. Applying the rule to the whole input range means that each row can change colour when its dropdown value changes. If you only want one cell coloured, select that cell instead. Avoid selecting the header or the source list unless those cells should also respond to the rule.
  4. Open the conditional formatting rule dialog. Choose Home > Conditional Formatting > New Rule. In the New Formatting Rule window, select Use a formula to determine which cells to format. Formula-based rules are useful here because they can test for an exact value, rather than matching a word that might appear inside a longer option.
  5. Make a rule for the first dropdown choice. The example layout for this rule is Pending, In progress, and Complete in B2:B20, with the rule applied to the same range. Enter =$B2="Pending" in the formula box. Select Format, open the Fill tab, choose a light red fill, and select OK. Select OK again to save the rule. The dollar sign fixes the column at B, while the row number remains relative, so Excel checks B2 for the first cell, B3 for the next, and so on.
  6. Add a rule for each remaining choice. Choose Home > Conditional Formatting > New Rule again and select the formula-based rule type. The example layout remains the same: options are in B2:B20 and each rule applies to B2:B20. For In progress, enter =$B2="In progress", then select a light amber or blue fill. For Complete, enter =$B2="Complete", then select a light green fill. Make each rule separately so you can choose a different format for each status.
  7. Check the range each rule applies to. Choose Home > Conditional Formatting > Manage Rules. In the rules manager, set Show formatting rules for to This Worksheet if needed. Confirm that each rule’s Applies to field covers =$B$2:$B$20. If a rule was created while only B2 was selected, edit its Applies to range so it covers all intended cells. Select Apply or OK to keep changes.
  8. Test every option in the worksheet. Select B2, choose Pending from its dropdown, and check that the cell turns light red. Try In progress and Complete in other rows, then change one selection to a different option. The colour should update immediately. Also test an empty cell in the range; it should remain uncoloured unless you deliberately create a rule for blanks.
  9. Extend the setup when you add more rows. If the list grows beyond row 20, update the validation range and each conditional formatting rule’s Applies to range. You can also apply the rules to a larger range in advance, such as B2:B200, if you expect the table to grow. Keep the formula’s row reference aligned with the first row in the Applies to range.

You can use a rule without a formula for simple cases: select the target cells and choose Home > Conditional Formatting > Highlight Cells Rules > Text that Contains, enter the option text, and choose a format. This is quick, but it can match part of a longer phrase. For example, a rule for In progress could also catch a value such as Not in progress. Prefer the exact-match formula rules when dropdown choices overlap or accuracy matters.

On Excel for Mac, the core workflow is similar: select the range, then use Home > Conditional Formatting > New Rule and choose a formula-based rule. Dialog wording or placement can vary by release. Excel for the web supports data validation and conditional formatting, but its rule editor may not show the same controls in the same order as the Windows desktop app.

Common Mistakes

  • Expecting the open dropdown menu itself to show coloured choices. Standard Data Validation lists display text options without individual colours. Conditional formatting changes the appearance of the cell after a value is selected.
  • Using a different spelling or spacing in the rule. Pending and Pending with a trailing space are different text values. Copy the option from the source list if you suspect a mismatch.
  • Applying the rule to the wrong range. A correct formula will appear not to work if Applies to includes only another cell, a header, or the source options. Check the rules manager.
  • Locking the row by mistake. For a rule covering B2:B20, =$B$2="Pending" checks B2 for every row. Usually you want =$B2="Pending", which keeps the column fixed but lets the row adjust.
  • Using similar colours that are hard to tell apart. Dark fills can make text unreadable, especially when the font stays black. Choose light fills, or set the font colour in the same Format dialog.

Tips

  • Make the dropdown values consistent. Store each option once in a source range and use that range for validation. This reduces spelling differences and makes later edits easier.
  • Use colour as a cue, not the only signal. Keep the actual status text visible so readers who cannot distinguish colours can still understand the cell. Use enough contrast for readability.
  • Keep your rule range manageable. If you know the list will stay small, apply formatting only to the relevant cells. If it is a growing table, apply rules to a sensible buffer of future rows and check the range when you expand it.
  • Review rule priority if you add more conditions. In Home > Conditional Formatting > Manage Rules, inspect the order of overlapping rules. If more than one rule can apply to a cell, review the order and any Stop If True setting so the result is predictable.
  • Choose formats that print and export clearly. A very pale fill may look different on another screen or in print. Test a sample and consider borders or font styles if the colour alone is too subtle.

Troubleshooting

The cell does not change colour. First confirm that the selected value exactly matches the text in the rule. Then open Manage Rules and check both the formula and Applies to range. For a B2:B20 range, a formula referring to B2 is appropriate when written with a relative row, as in the example. Also verify that the rule has a fill or font format selected; creating a rule without a format can produce no visible change.

Every row shows the same colour. Check that the formula does not fix the row number. If all cells are being evaluated against B2, remove the dollar sign before the row number. Keep the dollar sign before the column letter if the rule might be copied across columns.

The wrong cells are coloured. Inspect the Applies to field in Manage Rules. The range should start on the same row referenced in the formula. For example, if Applies to begins at B5, a formula referencing B2 may evaluate the wrong row. Adjust the formula’s row reference or the applied range so their starting rows line up.

One option triggers another option’s colour. This often happens with Text that Contains rules or overlapping values. Replace those rules with exact-match formulas. If you still see unexpected results, check the rule order and remove duplicate rules that apply to the same cells.

The dropdown works but the source list is not updating. Check the validation Source field and confirm it points to the current list range. If the options are stored on another worksheet, a direct sheet reference may not be accepted in the Source box. Define a name for the source range using Formulas > Define Name, then use that name as the validation source, for example =StatusOptions.

The formatting disappears after copying or editing cells. Pasting cells can replace existing conditional formatting. Reopen Manage Rules to see whether the rule still exists and whether its Applies to range changed. When copying data, use Paste Special options carefully; if formatting was overwritten, recreate the rule or restore the intended range.

Frequently Asked Questions

Can I colour each option inside an Excel dropdown?
No. A standard Data Validation dropdown does not support a different colour for each item in its open list. You can colour the cell after a choice is made by using conditional formatting.

Does Data Validation have a colour setting?
No. Data Validation controls which entries are allowed and can show an input message or error alert. Use Conditional Formatting to change a cell’s fill or font colour according to its value.

Can the cell colour change when I choose a different option?
Yes. Conditional formatting recalculates when the cell value changes. Set up one rule for each choice, then select a different dropdown item to see the corresponding format.

Can I use this method for numbers or dates instead of text?
Yes. Conditional formatting can test numbers, dates, or other values. Choose a suitable rule type or use a formula that compares the cell with the required value. Make sure the data type in the cell matches the kind of comparison you set up.

Will the colours work in Excel for the web or on Mac?
Conditional formatting is available in current Excel for the web and Mac versions, although the dialogs and menu layout may differ from Windows. If a particular rule editor option is unavailable in your version, open the workbook in desktop Excel for Microsoft 365 and manage the rules there.

Can I use icons instead of coloured fills?
Yes. Conditional Formatting includes icon sets and data bars, but they are designed for numeric values and comparisons, not as individual colours inside a text dropdown. For status words, coloured fills or font colours are usually clearer.

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.