How to Add Colour to a Drop-Down List in Excel Automatically

To add colour based on a drop-down choice in Excel, select the cells with the list, then create a separate conditional formatting rule for each choice using Home > Conditional Formatting > Highlight Cells Rules > Equal To. This colours the worksheet cell after someone makes a selection. Excel’s standard Data Validation drop-down does not let you colour individual choices inside the open menu itself.

The method below is for Microsoft Excel for Microsoft 365 on Windows. It works well for status fields, priority levels, review outcomes and similar lists. You will first make a drop-down, then use conditional formatting to give each selected value its own fill colour. The formatting updates when the value changes, so people do not need to apply colours manually.

What You’ll Need

An Excel workbook you can edit. If it is shared or protected, you may need permission to change its validation and formatting.
A list of the choices you want people to select, such as Draft, In review and Complete.
The worksheet cells where users will make selections, and a few minutes to set up one formatting rule per choice.

You can put the choices in a spare area of the worksheet or type them directly into the Data Validation Source box. A source range is easier to maintain if you expect the options to change later. For the example below, the options are Draft, In review and Complete, and the drop-down cells are D2:D100.

Step-by-Step

Prepare the choices. For a range-based list, type Draft, In review and Complete into three adjacent cells, such as F2:F4. Keep the source list free of blank cells between choices. You can place it on another worksheet, but setting up a cross-sheet source may require a named range; for a first setup, use the same worksheet or type the options directly into the Source box. The source cells can be hidden later if appropriate, but do not delete them if the drop-down depends on them.
Select the cells that need a drop-down. Select D2:D100, or the actual range where users will enter values. If the sheet already contains data, make sure you do not include headings or unrelated cells. To apply the setup to a single cell first, select that cell; you can extend it to more cells afterward.
Open Data Validation. On the Windows ribbon, choose Data > Data Validation. In the dialog’s Settings tab, open the Allow menu and choose List. Leave In-cell dropdown checked so the arrow appears when a cell is selected. If you want to prevent users from typing values that are not on the list, leave the error alert enabled; its default behavior blocks invalid entries.
Set the list source. If your options are in F2:F4 on the same sheet, enter or select =$F$2:$F$4 in Source. The dollar signs keep the source fixed when the validation is applied across the selected cells. Alternatively, type Draft,In review,Complete directly into Source. Click OK. Select a cell in D2:D100 and confirm that its arrow displays the choices. If you used a source range, changing a source cell changes the corresponding choice shown in the list.
Select the same cells again. Select D2:D100 before creating the colour rules. Conditional formatting applies to the selected range, so checking the selection at this point helps avoid colouring the source list or an adjacent column by mistake. You can also start by selecting the range and then build the validation and formatting for it.
Create the first rule for Draft. Choose Home > Conditional Formatting > Highlight Cells Rules > Equal To. In the value box, enter Draft. Open the format choices and select Custom Format to choose a specific appearance. On the Fill tab, select a light colour, such as pale grey, and use the Font tab if you also want to change text colour or style. Click OK to close the formatting dialog, then OK again to create the rule. The selected cells will show this fill wherever their value is Draft.
Make a rule for each remaining choice. With D2:D100 selected, repeat Home > Conditional Formatting > Highlight Cells Rules > Equal To for In review and Complete. Choose a different fill for each, for example pale yellow for In review and pale green for Complete. Match each rule’s text exactly to its corresponding list choice, including spaces. The colour appears when a cell’s value matches that rule.
Test all the options. Choose Draft, In review and Complete in a few cells in the selected range. Confirm that each cell changes to the expected fill. Then change one selection to another value and check that its old colour is replaced by the new one. Test a blank cell too; it should remain uncoloured unless you have separately created a rule for blanks.
Check the rule range if the colours do not cover all entries. Select a formatted cell and open Home > Conditional Formatting > Manage Rules. In the Show formatting rules for menu, choose This Worksheet if needed. Check that every rule’s Applies to range includes all intended input cells, such as =$D$2:$D$100. Edit the range if you add more rows. Click Apply, then OK.

On Excel for Mac, the same general tools are available, although dialog layouts and labels can vary slightly by version. Use the Data tab’s Data Validation command to create the list, then Home > Conditional Formatting to add the equal-to rules. Excel for the web supports drop-down lists and conditional formatting, but some rule-management controls may be arranged differently or be more limited. If you cannot find a setting, open the workbook in the desktop app.

Common Mistakes

Expecting the open menu to show coloured options. Conditional formatting colours the worksheet cell after a value is selected. The normal Data Validation arrow menu remains a plain list; the fill colour does not appear beside each option inside that menu.
Using different text in the rule and the list. A rule for In review will not match In Review if the spelling differs in a way that changes the text, or if one version includes an extra space. Copy the choice from the source list when entering each rule.
Formatting the wrong range. If the Applies to field contains only D2, only that cell changes colour. If it points to F2:F4, the source list may be coloured instead of the entry cells. Check the range in Manage Rules.
Choosing a very dark fill with dark text. The value may become hard to read. Pick a light fill or set a contrasting font colour in the rule’s Custom Format options, and check readability on the actual screen or printout.
Assuming a cell’s original fill will be replaced in every situation. Conditional formatting can take precedence over ordinary manual formatting while its condition is true. Remove conflicting rules or revise them if the result is not what you expect.
Typing a value that is not permitted. With the validation error alert enabled, Excel may reject text that is not an exact list item. Select an option from the arrow, or update the source list if the new value should be allowed.

Tips

Use meaningful colours. Choose colours that support the text rather than relying on colour alone. For example, keep the words Draft, In review and Complete visible so the status remains understandable to people who cannot distinguish the fills.
Keep the list and rules aligned. When adding a new choice, update both the Data Validation source and the conditional formatting rules. A new option can appear in the arrow without receiving a colour until you add its matching rule.
Use a source range for lists that may change. It is simpler to edit a worksheet cell than to reopen Data Validation and revise a comma-separated string. If you need a list that expands automatically as you add entries, consider converting the source list to an Excel table and setting up an appropriate named range.
Apply rules to future rows. If users will keep adding records, select a generous range such as D2:D500 rather than formatting only the first few cells. Alternatively, extend the Applies to range when the worksheet grows. Avoid selecting whole columns without a reason, particularly in a busy workbook with many rules.
Keep colours consistent across the workbook. If green means Complete in one table, use the same meaning elsewhere. A small, consistent palette makes a sheet easier to scan than a different colour scheme for each list.

Troubleshooting

The drop-down arrow is missing: Select a cell that should contain the list and open Data > Data Validation. On the Settings tab, confirm that Allow is set to List and In-cell dropdown is checked. If the Data Validation command is unavailable, the worksheet may be protected or the workbook may be in a mode that prevents editing. Check the Review tab for protection controls, or ask the workbook owner for access.

The arrow appears, but the list is empty or incomplete: Reopen Data Validation and inspect Source. Confirm that the range points to the cells containing the options and does not include unintended blanks. For a typed list, confirm that commas separate the entries. If a source range is on another sheet, Excel may reject a direct reference; define a named range for the options and use that name as the Source instead. Be mindful that separators can vary with regional settings.

A choice is selected but the cell stays uncoloured: Open Conditional Formatting > Manage Rules and check that the rule applies to the selected cell. Compare the rule’s Equal To value with the selected text, then use the same spelling and spacing. Also check whether the rule has a fill set under Custom Format. A rule can exist without a visible fill if its format was not configured as intended.

Every cell has the same colour, or one colour seems to win: In Manage Rules, inspect each condition and its Applies to range. Make sure each rule uses a different exact list value, rather than the same value repeated. If you have added other conditional formatting rules, review their order and settings; overlapping rules may affect the final appearance. Remove obsolete rules rather than adding more rules on top of them.

The formatting works in the desktop app but looks different online or when printed: Reopen the workbook in Excel for Microsoft 365 and verify the rule settings. Web and desktop layouts can display controls differently, and printer settings can alter how colours appear. If the sheet will be printed in black and white, retain clear text labels and consider adding a separate indicator that does not depend on fill colour.

Frequently Asked Questions

Can I colour each option inside the drop-down menu?
Not with Excel’s standard Data Validation list. The menu itself does not support individual font or fill colours. You can colour the cell after a person selects an option by using conditional formatting, or use a different control or custom solution if coloured menu entries are essential.

Does the selected cell change colour automatically?
Yes. Once the conditional formatting rules are set up, Excel checks the cell’s value and displays the corresponding format. When the selection changes to another listed value, the matching rule supplies the new colour.

Can I use more than three colours?
Yes. Create one Equal To conditional formatting rule for each choice you want to distinguish. Keep the palette readable and avoid assigning very similar colours to choices that users need to tell apart quickly.

Will the colour copy when I copy the cell?
Copying a cell can copy its validation and formatting as well as its value, depending on how you paste. If you only want the value, use Paste Special > Values. After copying, check the destination’s validation and conditional formatting if it behaves differently from the original range.

Can I change a colour later without rebuilding the drop-down?
Yes. Open Home > Conditional Formatting > Manage Rules, select the rule, choose Edit Rule, and adjust its format. This changes the appearance while leaving the Data Validation choices intact.

Why does a new list option have no colour?
The source list and the colour rules are separate. Add the new choice to the validation source, then create a matching conditional formatting rule and apply it to the entry range. Otherwise, the new text can appear in the menu but will not trigger a specific colour.

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.