How to Add a Drop-Down in Excel with Custom Options

To add a drop-down in Excel, select the cells, go to Data > Data Validation, choose List under Allow, and enter your choices in Source or select a range that contains them. This creates a selectable list in each chosen cell, making entries more consistent and easier to fill in. Before starting, decide which options you want and whether to type them directly or keep them in worksheet cells.

What You’ll Need

An Excel workbook in Microsoft 365 for Windows. The steps also work in many recent desktop versions of Excel.
The cells where you want people to make a selection.
Your list of choices, either ready to enter in the validation window or written in a worksheet range.
Optional: a separate worksheet for keeping the list out of the way. If the list is on another sheet, use a defined name to refer to it.

Step-by-Step

Prepare the options. For a short list that is unlikely to change, you can type the choices directly in the Data Validation window. For a list you may edit later, put one option in each cell in a column or row. For example, enter Open, In progress, Waiting, and Complete in H2:H5. Do not include a heading in the range you plan to use as the list source. Avoid blank cells between choices, because they can create empty selections or make the list appear incomplete.
Select the destination cells. Click the first cell that should have the drop-down. To apply the same list to several cells, select the whole destination range before opening Data Validation. For example, select B2:B100 if every row in that area needs a status choice. You can also select non-adjacent cells by holding Ctrl while selecting them on Windows; on Mac, hold Command. Applying the rule to a range at once is usually simpler than creating it repeatedly.
Open Data Validation. On the Windows ribbon, choose Data > Data Tools > Data Validation > Data Validation. In some window sizes, the Data Validation button may appear directly on the Data tab rather than inside a visibly labeled group. On Mac, choose Data > Data Validation; the dialog layout can differ slightly. In Excel for the web, look for Data Validation on the Data tab. If an option is missing or the dialog is difficult to use in the web app, open the workbook in the desktop application.
Choose a list rule. In the Data Validation dialog, open the Settings tab. In the Allow menu, select List. Keep In-cell dropdown checked so Excel displays the arrow when a destination cell is selected. Ignore blank allows the destination cell to remain empty; it does not add a blank choice to the list. Leave it checked if users do not have to fill in every row.
Enter choices directly, if appropriate. In Source, type the options separated by commas, such as Open,In progress,Waiting,Complete. This is convenient for a small, stable set of choices. The typed source has a 255-character limit, and separator behavior can depend on your regional settings. If Excel does not split your choices as expected, use a cell range instead. Directly typed lists are also harder to maintain: changing an option means reopening the rule and editing the source.
Use a worksheet range for an editable list. If your options are in H2:H5 on the same worksheet as the destination cells, enter =$H$2:$H$5 in Source. This assumes H2 contains Open, H3 contains In progress, H4 contains Waiting, and H5 contains Complete; the drop-down will show those four choices in that order. The dollar signs keep the source range fixed when the validation rule is applied to other cells. You can also click the range-selector button beside Source, select the cells, and confirm the selection.
Use a defined name for a list on another sheet. A direct reference to a different worksheet may be rejected in the Source box. To avoid that, select the cells containing your options, then choose Formulas > Define Name. Give the range a short name such as Choices, check that the Refers to field points to the intended cells, and confirm. Return to Data Validation and enter =Choices in Source. The name must exist in the workbook and point to the option cells; spelling and scope matter. This approach also makes a long source reference easier to understand.
Choose how Excel responds to other entries. Open the Error Alert tab. Keep Show error alert after invalid data is entered checked if users should be warned when they type a value that is not in the list. Select Stop to block ordinary invalid typing, or choose Warning or Information if you want Excel to allow a user to continue after a prompt. You can write a short title and message, such as Select a listed status. The Input Message tab is optional; it can display instructions when a user selects the cell.
Confirm and test the rule. Click OK. Select one of the destination cells and click the arrow that appears at its right edge. Choose an option and check that the cell receives the expected text. With the example above, the list should contain Open, In progress, Waiting, and Complete. Try typing a value that is not on the list to confirm that the error behavior matches your settings. The arrow normally appears only while a cell with the rule is selected, not all the time.
Update the list when your choices change. If you used a typed list, reopen Data Validation and edit Source. If you used a range, change the values in the source cells to update the displayed options. If you add choices beyond the original range, edit the Source reference or adjust the defined name so it includes the new cells. A fixed reference such as H2:H5 will not automatically include a new option entered in H6.

Common Mistakes

Leaving the in-cell arrow unchecked: The rule may still exist, but the convenient arrow will not show. Reopen Data Validation, select the Settings tab, and check In-cell dropdown.

Including the list heading: If the source range starts with a label such as Status, that label becomes a selectable option. Keep headings outside the source cells.

Using the wrong source range: A range can accidentally include unrelated cells, blank rows, or only part of the list. Confirm the first and last cells in Source, then test every option in the resulting drop-down.

Expecting the list to expand automatically: A rule that points to H2:H5 continues to use those cells even after you add another option below them. Extend the reference or update the defined name. Do not assume that typing below the list updates an existing rule.

Assuming validation protects pasted data: Data Validation helps guide normal entry, but pasting or filling cells can sometimes bring in values that do not meet the rule. If data quality matters, periodically check the column for unexpected values rather than treating the drop-down as a complete security control.

Putting commas inside a typed option: Commas separate items in a directly typed source, so an option such as North, East may be split into two choices. Put the options in worksheet cells instead; a cell-range source can contain commas within an item.

Tips

Use a cell range when options might change, when the list is long, or when an option contains a comma. It is easier to review and maintain the values in cells than in a single Source entry. Keep the option cells in the same order you want users to see; Excel uses the range order and does not automatically sort the list alphabetically.

Put the list in a clearly labeled area of the workbook, or on a dedicated worksheet if you want to keep the working sheet uncluttered. If a list on another worksheet is needed, a defined name gives the validation rule a clean reference. Be careful when hiding or protecting sheets: make sure workbook maintainers can still find and edit the source values.

Choose short, distinct labels. For example, use Waiting for approval and Approved rather than two choices that both appear as Waiting. If people need to enter a value that is not offered, decide whether a list is appropriate or whether a separate Other option and follow-up field would work better.

Keep the Input Message brief and specific, such as Choose the current review stage. For an error alert, explain what to do rather than simply saying the entry is wrong. If you need to reverse an accidental edit, use Ctrl+Z on Windows or Command+Z on Mac; however, undo is not a substitute for keeping a backup before making major workbook changes.

To remove a drop-down, select the affected cells, open Data > Data Validation, and choose Clear All in the dialog, then confirm. This removes the validation rule from the selected cells; it does not erase values already entered in them.

Troubleshooting

The arrow is missing. Select the cell first, because Excel shows the arrow only when that cell is active. If it still does not appear, open Data Validation and make sure Allow is List and In-cell dropdown is checked. Also confirm that the cell actually has the rule; selecting a nearby cell does not prove the rule was applied to the intended range.

Excel says the Source is invalid. Check that the range exists and uses the correct sheet and cell addresses. For a list on another sheet, define a name and use that name as described above. If you typed choices directly, try a cell range instead, particularly if your regional settings use a different list separator or an option contains a comma.

Some choices are missing. Compare the Source range with the cells containing the full list. If new choices were added below the original source, extend the source or defined name. If the source includes blank cells, remove the blanks or point the rule to a clean range.

Users can still enter unwanted values. Check that the Error Alert tab has its alert enabled and that the alert style is Stop if invalid typing should be blocked. Remember that pasted data can bypass normal validation behavior. Review the affected cells and repair unexpected entries, then consider protecting the workbook or adding a separate data-checking process where appropriate.

The list appears in the wrong order or has duplicates. The drop-down follows the order of its source cells and repeats duplicate entries. Reorder the source values and remove duplicates there, then test the list again. Excel does not automatically clean or sort the options for a basic validation list.

Frequently Asked Questions

Can I add a drop-down to multiple cells at once?
Select the complete destination range before opening Data Validation, then create the List rule once. Check a few cells afterward to make sure the rule was applied across the intended range.

Can I use options stored on a different worksheet?
Yes. Create a defined name for the option cells using Formulas > Define Name, then use that name with a leading equals sign in the Source box. This avoids relying on a direct cross-sheet reference.

Can a user select more than one option in the same cell?
A standard Excel Data Validation drop-down allows one choice per cell. Selecting another option replaces the current value. Multi-select behavior requires a different design, such as separate cells or custom automation.

Can the choices update when I add a new item?
Only if the source used by the rule includes the new cell. A fixed source such as H2:H5 does not grow by itself. Extend the reference or update the defined name after adding an item outside that range.

Why does the arrow not stay visible?
That is normal. Excel displays the in-cell arrow when you select a cell with a list rule; it usually disappears when you select another cell. The drop-down is still available when you return to the validated cell.

Does this work in Excel for Mac or Excel for the web?
The core Data Validation list feature is available in current Excel for Mac and Excel for the web, though the ribbon layout and dialog options can differ. If you cannot find a setting or need to create a defined name, use the desktop app and check the workbook afterward in the version your users will use.

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.