To insert a drop-down in Excel and color its selected value, make a list with Data Validation, then add a separate Conditional Formatting rule for each choice. In Excel for Microsoft 365 on Windows, select the cells, choose Data > Data Validation, set Allow to List, and provide the list as the Source. The drop-down menu itself cannot show colored options with Excel’s standard list feature; conditional formatting colors the cell after someone selects an option.
What You’ll Need
- An Excel workbook and the cells where you want the drop-downs.
- The exact list of choices, with consistent spelling and spacing. For example: Not started, In progress, and Complete.
- A place to store the choices if you want to use a cell range as the source. Keeping the list on the same worksheet is simplest; a named range is a practical option if the source list is on another sheet.
- A color plan, such as gray for Not started, yellow for In progress, and green for Complete. Choose colors that remain easy to read and do not rely on color alone to communicate meaning.
Step-by-Step
- Prepare the choices. Enter each option in its own cell, with no heading included in the range you intend to use. For example, put Not started in D2, In progress in D3, and Complete in D4. You can use another location, but keep the source values together and make sure they match the text you want users to see. Avoid extra spaces before or after an option, because they can make matching and color rules harder to manage.
- Select the cells that need a drop-down. Click the first destination cell, such as B2. To apply the same list to a group, select the full range, such as B2:B100, before setting up validation. You can also select nonadjacent cells by holding Ctrl while clicking them on Windows. On a Mac, use Command while selecting additional cells. Applying the rule to a sensible range now saves you from repeating the setup cell by cell.
- Open Data Validation. On the Windows ribbon, choose Data > Data Validation. In the dialog’s Settings tab, open the Allow menu and choose List. Make sure In-cell dropdown is checked so the arrow appears when a destination cell is selected. Leave Ignore blank checked if users may leave a cell empty; clear it if a value must be entered. The dialog may appear slightly different in Excel for Mac, but the Data Validation command and List setting are the relevant controls.
- Set the list source. If the choices are in D2:D4 on the same sheet, click in the Source box and select those cells, or enter the range reference shown by Excel. Do not include a heading or blank cells unless you want them to be part of the list. For a short list, you can type values directly into Source separated by commas, such as Not started,In progress,Complete. A cell range is easier to maintain when the list may change. Excel’s standard validation source does not accept an ordinary direct reference to a different worksheet; for a list stored on another sheet, define a named range and use that name as the Source.
- Choose how invalid entries are handled. In the Data Validation dialog, open the Error Alert tab if you want Excel to warn users who type a value that is not on the list. Keep Show error alert after invalid data is entered checked, choose a Stop style to prevent the entry, and write a short message such as Select a status from the list. A warning or information style allows someone to continue after seeing the notice, so use Stop when matching the approved choices matters. Select OK to create the drop-down, then click a destination cell and test the arrow and choices.
- Create the first color rule. Select the destination range again, for example B2:B100. On the Home tab, choose Conditional Formatting > New Rule. In the New Formatting Rule dialog, choose Format only cells that contain. Set the rule to Cell Value, equal to, and enter the first option exactly as it appears in the list, such as Not started. Select Format, open the Fill tab, choose a fill color, and select OK. Select OK again to save the rule. Excel will apply the fill when a cell in the selected range contains that exact value.
- Repeat for every choice. With the destination range selected, add another rule through Home > Conditional Formatting > New Rule for In progress, and choose a different fill, such as yellow. Add a third rule for Complete and choose green. Match the rule text to the list exactly, including spaces and punctuation. Each option needs its own rule if it should have its own color. You can use a font color as well as a fill, but check that the text remains readable against the background.
- Check the rules and test several cells. Select a destination cell, pick each list option in turn, and confirm that the cell color changes as expected. Test more than one row to catch a rule that applies only to the first cell. If needed, open Home > Conditional Formatting > Manage Rules, set Show formatting rules for to This Worksheet or the relevant selection, and review the Applies to range for each rule. The range should cover the cells where people will use the drop-down. Click Cancel or undo a change if the result is wrong, then correct the rule before filling in real data.
The drop-down arrow is visible when a validated cell is selected. Excel does not color the individual choices inside the standard pop-up list. The color appears in the worksheet cell after you choose an item, which is useful for scanning a status column but is different from a menu with colored entries.
Common Mistakes
- Expecting color inside the opened menu. Data Validation creates a basic list, not a styled menu. Conditional Formatting changes the cell’s appearance after a choice is made. If colored entries within the actual menu are essential, the standard drop-down is not the feature that provides them.
- Using different wording in the rule and list. A rule for Complete will not match Completed, and an invisible trailing space can also prevent a match. Copy the option text from the source list into the rule, and keep the list wording consistent.
- Applying colors to only one cell. If you selected B2 before creating a rule, the Applies to range may be just B2. When the intended range is B2:B100, check Manage Rules and correct Applies to so all target rows are included.
- Including a heading or unwanted blank in the source range. If the source includes a column label, that label may appear as a selectable option. If the range includes empty cells, blank choices may appear. Select only the cells containing valid entries.
- Assuming existing entries will automatically become valid. Adding validation does not necessarily repair old values already in the cells. Check existing entries and use the drop-down to replace any that do not match the approved options.
Tips
- Keep the source list easy to maintain. A small, clearly labeled list on the same worksheet is straightforward. If you prefer to keep it on a separate sheet, select the source cells, create a named range through Formulas > Define Name, and enter that name in the validation Source box. Use a simple name without spaces, and verify that the name refers to the full choice list.
- Choose readable colors. Use a light fill with dark text or a dark fill with suitably contrasting text. Check the result at normal zoom and, if the workbook will be printed, in print preview. Red and green alone can be difficult for some readers to distinguish; adding clear text labels keeps the status understandable without color.
- Apply the same setup consistently. Put the drop-downs in one column, apply validation and conditional formatting to the same planned range, and keep the source values in one place. This reduces the chance that one row behaves differently from the rest.
- Make changes at the source when possible. If you rename an option, update the source list and the matching conditional formatting rule. For an existing list, replacing a choice in its source cell usually updates the displayed choice, but a color rule that still checks the old wording will need to be edited too.
- Keep a visual key if colors have meaning. A small legend near the table can explain the scheme, for example gray means Not started and green means Complete. Treat the words in the cells as the authoritative information; color is a visual aid rather than a replacement for the actual value.
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. The arrow normally appears only while the validated cell is selected. If the cell is part of a merged area, test the setup in an unmerged cell, because merged layouts can make selection and data-entry controls less predictable.
The list appears, but a choice does not change the color. Check that a Conditional Formatting rule exists for that exact choice and that its Applies to range includes the cell. Open Home > Conditional Formatting > Manage Rules and inspect the rule’s text and fill. Look for extra spaces, different punctuation, or a spelling difference between the list and the rule. Also check that another conditional formatting rule is not applying a competing format. If the cell was manually filled, clear that manual fill or test the rule in a clean cell.
The list is empty or shows the wrong options. Reopen Data Validation and inspect Source. Make sure the referenced cells contain the choices, the range points to the right sheet and cells, and the source does not include an unintended blank or heading. If you used a named range, check Formulas > Name Manager to confirm the name exists and refers to the correct cells. If a direct reference to another sheet was entered, replace it with a named range or move the source list to the same sheet.
Some rows work and others do not. Review the Applies to range for every color rule and the validated range in Data Validation. Extend either range to cover the missing rows. If you pasted content into the cells, the paste operation may also have replaced validation or formatting; copy a correctly set-up cell and use Paste Special options carefully, or reapply Data Validation and the rules to the affected cells.
The list allows typing an unlisted value. Reopen Data Validation, check the Error Alert tab, and make sure Show error alert after invalid data is entered is enabled. Choose Stop if Excel should reject entries outside the list. If the workbook is opened in another spreadsheet program, validation behavior may differ, so check it in Excel as well.
Excel for Mac has the same core approach: use Data Validation to create the list and Conditional Formatting to color matching cell values. Ribbon wording or dialog layout can vary slightly by release. Excel for the web also supports common validation lists and conditional formatting, although some dialog controls and rule-management options may differ from the Windows desktop app. If an option is unavailable in the web interface, open the workbook in desktop Excel to configure it.
Frequently Asked Questions
Can I color the options inside an Excel drop-down?
No, not with the standard Data Validation list. You can color the worksheet cell after an option is selected by adding Conditional Formatting rules. The choices in the opened list remain uncolored.
Can I use several colors for one drop-down?
Yes. Create one conditional formatting rule for each option and assign a different fill to each rule. For a three-item status list, for example, use separate rules for Not started, In progress, and Complete.
Will the color update when I change the selection?
Yes. Conditional Formatting responds to the current cell value. When you replace one valid choice with another, the matching rule applies and the previous option’s color no longer applies, provided the rules cover that cell.
Can the drop-down list come from another worksheet?
Yes, but use a named range rather than entering a normal direct reference to a different sheet in the Source box. Define the name for the cells containing the choices, then enter that name as the list source.
Can I apply the drop-down and colors to an entire column?
Yes. Select the intended cells in the column, such as B2:B1000, when you set up validation and conditional formatting. Avoid applying rules to a much larger range than needed if the workbook becomes slow or difficult to manage.
Does this work in Excel for Mac or Excel for the web?
The basic method is available in both, though menu locations and dialog layouts can differ by release. On Mac, look for Data Validation and Conditional Formatting on the ribbon. In the web app, use the corresponding Data and Home controls; use desktop Excel if a particular rule-management setting is missing.
Related
- Hide Gridlines in Excel: Keyboard Shortcut and Steps
- How to Hide Cells in Excel Without Deleting Data
- How to Hide Columns in Excel Using Keyboard Shortcuts
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- How to Delete Microsoft Excel Without Losing FilesTo delete Microsoft Excel from a Windows PC, open Settings > Apps > Installed apps, find Excel or…
- Excel Table: How to Flip Rows and ColumnsTo flip an Excel table so its rows become columns and its columns become rows, copy the range,…
- Excel vs Google Sheets: Features and DifferencesExcel and Google Sheets both handle everyday spreadsheet work, including formulas, charts, sorting, and pivot tables. Excel generally…
- How to Create a Brochure Template in Microsoft WordCreate a reusable brochure template in Word by setting up a landscape page, arranging its panels in a…
- Delete a Page in Word: Can You Remove One?Yes. In Word for Microsoft 365, you can select the contents of a page and delete them, but…
- How to Make a Fillable Form in Microsoft Word Step by StepTo make a fillable form in Microsoft Word, turn on the Developer tab, add content controls such as…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.