Excel Drop-Down List With Color: Setup and Formatting

How-to basics · Updated November 20, 2026

To make a colored drop-down in Excel, create a list with Data Validation, then use Conditional Formatting to color the cell according to the selected value. Excel does not natively color each choice inside the open drop-down menu; this method colors the cell after someone selects an option. The steps below are for Microsoft Excel for Microsoft 365 on Windows.

What You’ll Need

  • An Excel workbook and the cells where you want users to choose an option.
  • The exact list of choices, such as Not started, In progress, Waiting, and Complete.
  • A few colors that you can distinguish easily. For example, use gray for Not started, blue for In progress, orange for Waiting, and green for Complete.

For a short list, you can type choices directly into Data Validation. For a list that may change or be reused, put the choices in worksheet cells and use those cells as the list source. This guide uses a source range on the same worksheet so the setup is easy to follow.

Step-by-Step

  1. Enter your list choices. In an unused area of the worksheet, type each option in a separate cell, one below another. For example, enter Not started in D2, In progress in D3, Waiting in D4, and Complete in D5. Keep the wording and punctuation consistent: the formatting rules will look for the same text that appears in the list.

  2. Select the cells that need the drop-down. For this example, select B2:B100, or select the actual range you expect people to fill in. You can select the full column if you need the list throughout it, but a defined range is often easier to manage and avoids applying formatting to thousands of unused rows. You can also select one cell first and extend the setup later.

  3. Open Data Validation. On the ribbon, choose Data > Data Validation. In the Data Validation dialog, stay on the Settings tab. Under Allow, choose List. Leave the In-cell dropdown box checked so the arrow appears when a user selects one of the validated cells. The Ignore blank setting can remain selected if empty cells are allowed.

  4. Set the list source. Click in the Source box, then select the four choices you entered in D2:D5. The source should appear as a worksheet reference, such as =$D$2:$D$5. The dollar signs keep the source range fixed. Click OK. If Excel does not accept the reference, check that the source cells are on the same worksheet and that you selected the cells rather than typing an invalid reference.

  5. Check that the drop-down works. Select B2 and click the arrow that appears at the right edge of the cell. Choose one of the options. Repeat in another cell in the selected range to confirm the validation applies to more than the first cell. The cell will not be colored yet; that comes from the next steps.

  6. Create the first color rule. Select the same target range, B2:B100. Choose Home > Conditional Formatting > New Rule, then choose Use a formula to determine which cells to format. With the target range B2:B100, enter =$B2="Complete" in the formula box. The dollar sign fixes the column, while the row number stays relative so the rule checks each cell in the range. Click Format, choose the Fill tab, select a green fill, and click OK. Click OK again to save the rule.

  7. Add a rule for each other choice. Keep B2:B100 selected and repeat Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format. For the range B2:B100, use =$B2="Not started" for a gray fill, =$B2="In progress" for a blue fill, and =$B2="Waiting" for an orange fill. For every rule, choose the fill color through Format, then confirm the dialogs. The formula text must match the drop-down choice exactly, including spaces.

  8. Test each choice and adjust the range if needed. Choose each status from a drop-down in B2 and check that it receives the expected fill. Test a lower cell, such as B20, too. If it stays uncolored, open Home > Conditional Formatting > Manage Rules, select each rule, and check that Applies to covers the intended cells, for example =$B$2:$B$100. You can also adjust the color or formula in this dialog.

  9. Make the source list easier to maintain, if appropriate. If the choices are in a different worksheet, Excel may not accept a direct reference to that sheet in the Source box. One practical option is to define a name for the source range: select the choices, choose Formulas > Define Name, enter a name such as StatusOptions, and confirm. Then set the Data Validation Source to =StatusOptions. A named range is also useful if you reuse the same choices in several places.

On Excel for Mac, the commands are broadly similar: use Data > Data Validation to create the list and Home > Conditional Formatting to create color rules. Ribbon layout and dialog wording can vary slightly by release. Excel for the web also supports drop-down lists and conditional formatting, but if a rule-creation option is missing or difficult to locate, open the workbook in the desktop app to complete the formula-based rules.

Common Mistakes

  • Expecting the open menu itself to show colored choices. Standard Data Validation lists display text options without individual fill colors. Conditional Formatting changes the worksheet cell after selection, not the appearance of each item in the menu.

  • Using slightly different text in a rule. A rule for Complete will not match Completed, or a value with an extra space at the end. Copy the wording from the source list when entering each rule.

  • Applying rules to the wrong range. A rule can be correct but do nothing if Applies to excludes the cells you are changing. Check the address in Manage Rules, especially after copying cells or extending the list.

  • Using an incorrect row reference in a formula rule. If the selected range starts at B2, a formula starting with =$B2 checks the right row as Excel evaluates the rule down the range. A fixed row reference such as =$B$2 makes every cell depend on B2 instead.

  • Typing a source list with the wrong separator. If you enter choices directly in the Source box, Excel uses your regional list separator, which may not be a comma. A cell range avoids this issue and is more convenient for a longer or frequently edited list.

Tips

  • Choose fill colors with enough contrast against the text. If a darker fill makes the existing font hard to read, change the font color in the rule’s Format dialog as well.

  • Use color as a visual cue, not the only way to communicate meaning. Keep the status words visible so the sheet remains understandable when printed in black and white or viewed by someone who has difficulty distinguishing colors.

  • Keep the source list in one place and edit it there when options change. If you add a new choice outside the original source range, update the Data Validation source too, or use a suitable named range that includes the new cell.

  • To copy the set-up to another area, copy a correctly configured cell and use Paste Special > Validation for the list settings. Conditional Formatting has its own rules, so inspect the destination’s rules after copying; you may need to copy the formatting or adjust Applies to separately.

  • Use the same color meaning throughout the workbook. For example, reserve green for completed work and orange for items needing attention instead of assigning those colors differently on another sheet.

Troubleshooting

The arrow 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 cell is merged or protected, those conditions can also interfere with editing or using validation; try an unmerged, unlocked cell in a test area.

The cell changes value but not color. First compare the selected value with the text in the rule. Then go to Home > Conditional Formatting > Manage Rules and set the Show formatting rules for option to This Worksheet if you cannot see the rule. Check its formula and Applies to range. For a range starting at B2, the rule should refer to B2, not to a different column or an unrelated row.

The wrong color appears. Open Manage Rules and check whether another conditional formatting rule also applies to the cell. Rules can overlap, and their order or Stop If True setting may affect the result. Remove duplicate or conflicting rules, or correct their ranges and conditions. Also confirm the fill color was selected in the rule’s Format dialog rather than applied manually to the cell.

Excel rejects the list source. For a basic same-sheet list, select the source cells with the dialog’s range picker rather than typing the address. If the source is on another sheet, define a workbook name for that range and use the name in the Source box. If you entered options separated by commas and Excel rejects them, use the local separator or switch to a source range.

New options do not appear. The validation source may still point only to the original cells. Extend the source reference or update the defined name so it includes the new entries, then test the arrow again. Adding a color rule alone does not add a choice to the drop-down; both the validation source and the corresponding conditional formatting rule must include the new status.

Frequently Asked Questions

Can I color the choices inside the drop-down menu?
No. Excel’s standard Data Validation drop-down does not apply individual colors to its open-menu options. You can color the selected cell with Conditional Formatting, which is the practical no-macro method.

Will the color update automatically when I select a different option?
Yes. When the cell value changes to another listed choice, Excel recalculates the conditional formatting and displays the fill associated with the matching rule.

Can I use this with several cells?
Yes. Apply Data Validation to the full target range and set the conditional formatting rules to the same range. Formula references should be written relative to the top-left cell of that range, as in =$B2 when the range starts at B2.

Can I use text, numbers, or dates as drop-down choices?
Yes. Data Validation lists can contain different types of values. For conditional formatting, make sure each rule compares the value in the same way it is stored and displayed. For dates or numbers, you can also use the built-in conditional formatting rules appropriate to those values.

Why does a blank cell have a color?
Check whether a rule’s condition can match blanks, whether the range includes cells with old formatting, and whether another rule applies. Clear unwanted manual fill with Home > Clear > Clear Formats, then review the rules in Manage Rules. Do not choose Clear All unless you also intend to remove the cell’s contents and other settings.

Do I need a macro to make the selected cell change color?
No. Data Validation and Conditional Formatting are enough for this effect, and the colors update as users choose values. A macro would only be needed for behavior beyond standard conditional formatting, such as trying to customize the drop-down menu itself.

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.