Add a Drop-Down in Excel and Color Its Selected Cells

How-to basics · Updated November 20, 2026

To add a drop-down in Excel and color its selected cells, create a list with Data Validation, then add a separate Conditional Formatting rule for each choice. The drop-down lets people choose consistent entries; the formatting changes the cell’s appearance after a choice is made. In Microsoft Excel for Microsoft 365 on Windows, the list menu itself does not display a different color for each item, so this method colors the worksheet cell, not the menu options.

What You’ll Need

  • An Excel workbook and the cells where you want people to make selections.
  • The choices to include, such as Not started, In progress, and Complete. Keep the wording consistent because the color rules will match those entries.
  • A place to store the choices. A short list can be typed directly into Data Validation; a range of cells is easier to maintain if the list may change.

Step-by-Step

  1. Prepare the choices. For a small list, decide on distinct, concise labels such as Not started, In progress, and Complete. For a reusable list, enter each choice in its own cell, for example on a sheet named Lists in cells A1:A3. Avoid blank rows within the list. If a choice contains a comma, use a cell range rather than typing the entries into the Source box, because commas separate typed list entries.
  2. Select the cells that should have a drop-down. On the worksheet, select the target range, such as D2:D100. Selecting the whole intended range first means you only need to set up validation once. You can also create the drop-down in one cell and copy that cell later, but selecting the range directly makes it easier to confirm that all intended rows are covered.
  3. Open Data Validation and choose a list. On the Windows ribbon, go to Data > Data Validation. In the dialog, open the Settings tab, set Allow to List, and make sure In-cell dropdown is checked. If you want to prevent entries that are not on the list, leave the Error Alert enabled and use the Stop style. Excel may show a warning when a selected range contains existing values that do not match the list; review those values before continuing.
  4. Set the list source. If you are using a short typed list, enter the choices in the Source box separated by commas, for example Not started,In progress,Complete. If you used cells on the same worksheet, select the Source box and select the cells containing your choices. For a list on another worksheet, create a named range first: select the list cells, go to Formulas > Define Name, give the range a simple name such as StatusOptions, and confirm. Return to Data Validation and enter =StatusOptions in Source. A named range avoids the direct cross-worksheet reference problem in the Data Validation Source box.
  5. Confirm and check the drop-down. Click OK, then select one of the validated cells. A small arrow should appear at the cell’s right edge when the cell is active; click it to see the available choices. Select one item and check that it appears in the cell. If the arrow is missing, verify that In-cell dropdown is checked and that you have selected a cell within the validated range.
  6. Select the same cells for color formatting. Select D2:D100 again, or the range you used for the list. Conditional Formatting rules apply to the selected cells, so make sure the selection includes every row that should change color. It is fine if the cells are blank at this point; the rules will take effect when a matching value is selected.
  7. Create the first color rule. On the ribbon, choose Home > Conditional Formatting > Highlight Cells Rules > Equal To. Enter the exact list entry in the dialog, such as Complete. Choose a preset format or select Custom Format to choose a fill color on the Fill tab, then confirm the dialogs. The rule compares the cell value with the text you entered. Using Equal To rather than Text that Contains helps prevent a short label from coloring a different entry that happens to include the same words.
  8. Add a rule for each other choice. Keep the target range selected and repeat the Equal To process for each remaining choice. For example, set In progress to a yellow fill and Not started to a light gray fill, while Complete has a green fill. Choose colors with enough contrast to keep the text readable. Each choice needs its own rule and format; Data Validation does not assign these colors automatically.
  9. Test several entries. Use the drop-down in a few cells to choose each available value. Confirm that the cell fill changes to the intended color, that blank cells remain as you expect, and that a value from one row does not affect another. If you plan to add more rows, check that the conditional formatting range includes them or extend the range before relying on the sheet.

On Excel for Mac, the ribbon labels are largely the same: use Data > Data Validation for the list and Home > Conditional Formatting for the color rules. In Excel for the web, both features are available, though dialog layouts can differ. If you cannot find a particular option, check the selected range and the relevant ribbon tab before changing the workbook’s structure.

Common Mistakes

  • Expecting colored choices in the open menu. Standard Excel in-cell drop-down menus show the choices as text. Conditional Formatting colors a cell after a value is selected; it does not color individual menu items.
  • Using different wording in the rule and list. A rule for Complete will not reliably color a cell whose value is Completed or Complete with an extra space. Copy the text from the source list when creating rules.
  • Applying formatting to the wrong range. A rule can be correct but have no visible effect if it applies to a different column, just one cell, or too few rows. Confirm the target range before making or editing rules.
  • Typing comma-separated choices that contain commas. Commas separate entries in a typed Source list, so use a range of cells for items that include commas or for longer lists.
  • Assuming validation cleans up old data. Adding a list does not automatically replace existing values that are outside the list. Review and correct old entries, and remember that pasted data can sometimes bypass the usual entry prompt.

Tips

  • Store a changing list in cells instead of hard-coding it in the Source box. When you add or rename an item, update the source range or named range and make sure a matching Conditional Formatting rule exists.
  • Use short, unambiguous labels. Similar entries such as Review and Review needed are easy to confuse; an Equal To rule reduces partial-match problems, but clear names still help users choose correctly.
  • Pick fills that communicate meaning consistently, and do not rely on color alone. Keep the text label visible so the sheet remains understandable for people with color-vision differences and when printed without color.
  • To inspect or adjust existing rules, select an affected cell and choose Home > Conditional Formatting > Manage Rules. Check the Show formatting rules for setting and the Applies to range, especially if the worksheet has been copied or expanded.
  • If many cells need the same behavior, apply validation and formatting to the full working range up front. For a formatted Excel table, confirm that new rows inherit the rules in your workbook; if they do not, extend the Applies to range and test a newly added row.

Troubleshooting

The drop-down arrow is not visible: Click the validated cell; the arrow is normally shown only while the cell is active. If it still does not appear, open Data > Data Validation and confirm that Allow is List and In-cell dropdown is checked. Also verify that the cell is inside the range you selected when applying validation.

The list is empty or shows an error: Reopen the Data Validation dialog and inspect Source. Make sure a typed list uses commas between entries, or that a range-based source points to cells containing the choices. If the list is on another sheet, use a named range and enter its name with a leading equals sign, as described above. Check that the named range refers to the right cells in Formulas > Name Manager.

The value changes but the fill does not: Confirm that Conditional Formatting is applied to the cell you changed, and that the Equal To text exactly matches the selected choice. Open Manage Rules to inspect the Applies to range and the format. If there are several rules, make sure another rule is not overriding the fill; simplify overlapping rules or adjust their order and Stop If True settings where appropriate.

Only some rows change color: The conditional formatting range may cover fewer cells than the validation range. Select a cell that fails, open Manage Rules, and expand Applies to so it includes all intended rows. If the source labels were edited, update the corresponding Equal To rules as well.

Old or pasted entries do not behave as expected: Check whether the text is actually the same as a list item; leading or trailing spaces and spelling differences can prevent an exact match. Re-select the value from the drop-down or correct the source text. Data Validation and Conditional Formatting do not automatically standardize inconsistent values entered or pasted into the worksheet.

Frequently Asked Questions

Can I color each option inside the drop-down menu?
Not with Excel’s standard in-cell Data Validation list. The menu choices remain uncolored; you can use Conditional Formatting to color the worksheet cell after a person selects an option.

Can I use more than one color?
Yes. Create one Equal To Conditional Formatting rule for each list value and choose a different fill for each rule. Make sure every rule applies to the same intended cells.

Can the drop-down list come from another worksheet?
Yes. A practical approach is to define a named range for the cells on the other sheet, then use that name in the Data Validation Source box. This is easier to maintain than trying to enter a direct reference to a different sheet there.

Will the colors update when I change the selection?
Yes. When the cell value changes to a choice covered by a rule, Conditional Formatting updates its appearance. If you change the list wording, revise the corresponding rule so its match still reflects the new entry.

Can I add another choice later?
Yes. Add the choice to the list source, expand the source range or named range if needed, and create a matching color rule. Then test the new choice in a validated cell to confirm both the menu and color behavior.

Does this work in Excel for Mac and Excel for the web?
Both support data-validation lists and conditional formatting. Ribbon or dialog layouts may vary slightly, but the same approach applies: create a List validation rule, then add a matching conditional format for each value.

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.