Excel Data Validation Drop-Down: Create and Edit Lists

To create a drop-down list in Excel for Microsoft 365 on Windows, select the cell or cells, choose Data > Data Validation > Data Validation, set Allow to List, and enter the choices or select a source range. Make sure In-cell dropdown is checked, then select OK. Before you start, decide which entries you want to allow and whether they should live directly in the rule or in a separate worksheet range.

What You’ll Need

An Excel workbook in which you can edit cells and data validation settings.
The list of allowed choices, such as High, Medium, and Low, or a range of worksheet cells that contains those choices.
If the choices are on another worksheet, a named range that points to the list. Excel’s validation Source box does not accept a direct reference to a different worksheet, so set up a name first.

Step-by-Step

Prepare the choices. For a short, fixed list, write down the items in the order you want them to appear. For a list you may update later, enter one choice per cell in a column, with no blank cells between items. For example, put Draft, Review, and Approved in A2:A4 on a sheet called Options. Keeping the source list in worksheet cells makes it easier to add, remove, or revise choices without rebuilding a long comma-separated entry.
Select where users will choose an item. Click one destination cell, or drag to select a range such as B2:B100 if every row should have the same drop-down. To apply it to a whole column, select the intended cells below the header rather than the header itself. Selecting the cells first ensures that the validation rule is applied to the correct area; you can also copy a correctly configured cell later, but selecting the full range is clearer.
Open Data Validation. On the Windows ribbon, choose Data > Data Validation > Data Validation. The Data Validation dialog opens. If the command appears unavailable, check that the workbook is not protected in a way that prevents changing the selected cells, and that you have selected worksheet cells rather than a chart or other object.
Choose the List setting. On the Settings tab, open the Allow menu and select List. Keep In-cell dropdown checked so an arrow can appear in the cell when it is selected. Ignore blank controls whether an empty destination cell is treated as acceptable; it does not add a blank choice to the list. Leave it checked if users may leave the cell empty, or clear it if the field should be completed.
Enter a short list directly, or point to a range. For a short list used in only this rule, type the choices in the Source box separated by commas, for example Draft,Review,Approved. Do not add quotation marks. For a worksheet list on the same sheet, select the Source box and then select the cells containing the choices; Excel inserts the reference. A direct typed source is limited to 255 characters, so use a range for longer lists or lists you expect to maintain.
Use a named range for a list on another worksheet. If the choices are on Options!A2:A4, select those source cells and choose Formulas > Define Name. Give the range a clear name such as StatusChoices, confirm the reference is the intended cells, and select OK. Return to the destination cells and open Data Validation. Set Allow to List and enter =StatusChoices in Source. The leading equals sign tells Excel to use the named range. Use a simple name without spaces, and verify that the name refers to the correct worksheet cells.
Set an input message if it will help. On the Input Message tab, you can check Show input message when cell is selected and add a brief title and instruction, such as Choose a status from the list. This message is optional. Keep it short and useful; it appears when someone selects a validated cell and can clarify what the field is for.
Choose how Excel handles invalid entries. On the Error Alert tab, keep Show error alert after invalid data is entered checked if you want Excel to respond when someone types a value that is not allowed. Stop blocks an invalid entry typed into the cell. Warning and Information display a message but let the user continue. Add a concise title and message that explains what to do, such as Select a value from the drop-down. Choose OK to apply the rule.
Test the result. Select one of the destination cells. The drop-down arrow normally appears when the cell is selected; click it and choose an item. Then try typing a value that is not on the list. With a Stop alert enabled, Excel should reject that typed value. Check a second destination cell too, especially if you applied the rule to a large range, and confirm the list contains the expected choices in the expected order.

In Excel for Mac, the Data Validation command is also on the Data tab, although the dialog layout and ribbon presentation can vary by release. Excel for the web supports drop-down lists and has a Data Validation command on the Data tab, but some dialog details may differ from the desktop app. If a control you need is missing or difficult to locate in the browser, open the workbook in the desktop version and make the change there.

Common Mistakes

Forgetting the in-cell option. The validation rule may exist even if the arrow is not visible. Reopen the Data Validation dialog and check In-cell dropdown. Also remember that the arrow is generally displayed only while the cell is selected.
Using the wrong separator. A directly typed list uses the list separator expected by your Excel and regional settings. Commas work in many installations, but some regional configurations use a different separator. If Excel treats the entire entry as one choice or reports an error, use a source range instead, or try the separator used by your local Excel settings.
Pointing to the wrong source cells. A header, blank cells, or unrelated values included in the source can show up as choices. Review the Source reference and keep the choices together in a clean range. If the range contains a blank, users may see an empty option.
Trying a direct cross-sheet reference. Typing a reference such as Options!A2:A4 directly into the Source box is not the dependable method for a list on another sheet. Define a name for the source range and use that name in the rule instead.
Expecting the rule to repair old data. Applying validation does not automatically replace entries already in the cells. Existing values can remain even if they are no longer on the list. Review existing entries separately, and use the circle invalid data feature from the Data Validation menu if available in your Excel version to help locate invalid values.
Assuming every way of entering data is blocked. The Stop alert is designed to prevent invalid values entered directly, but copying and pasting can bring in data that does not meet the rule. Validation is a useful entry aid, not a substitute for checking imported or pasted data.

Tips

Keep the source list easy to maintain. Put reusable choices in a clearly labeled area or a dedicated Options worksheet. Use one choice per cell and avoid inserting explanatory notes among the values, because those notes can accidentally become selectable items.
Use descriptive names for ranges. Names such as StatusChoices or DepartmentList are easier to recognize than a bare cell address when you revisit the rule. A named range is especially helpful when a list is stored on a different worksheet.
Apply one rule to the full input area. When creating the rule, select all the intended cells first. This reduces the chance that one row is left without a drop-down or has a different source. You can inspect a cell’s rule later by selecting it and opening Data > Data Validation.
Use an input message for unfamiliar fields. A short prompt can explain whether the user should choose a department, status, or priority. It helps prevent confusion without adding extra instructions to the worksheet itself.
Choose alert behavior deliberately. Use Stop for fields where only approved choices are acceptable. Use Warning or Information only when users may need to enter an exception. Explain that exception in the alert message so people know whether to cancel or continue.
Keep an editable source range when choices change. If the options are updated occasionally, changing the cells behind a named range is simpler than reopening every destination cell’s rule. After changing the source, test the drop-down to confirm the new values appear.

Troubleshooting

The arrow does not appear: Select the cell first; the arrow is normally shown only while the cell is active. If it still does not appear, open Data > Data Validation > Data Validation, confirm Allow is List, and check In-cell dropdown. If the cell is merged or the worksheet is protected, those conditions may limit how the cell behaves. Test an ordinary, unprotected cell to narrow down the cause.

Excel rejects the Source entry: Check that the source has no accidental spaces or unsupported reference. For a cross-sheet list, create a defined name and use the name with a leading equals sign, such as =StatusChoices. For a long list, put the values in cells rather than exceeding the 255-character limit for a directly typed list. If the separator in a typed list does not work, use a source range.

The drop-down shows the wrong choices: Reopen the rule and inspect the Source field. Confirm it points to the correct cells or name, and check that the defined name refers to the intended range. Look for an included header, blank cell, or extra value. If the source values were changed, close the dialog and reopen the drop-down to see whether the updated choices are present.

An invalid value still appears in a cell: First determine whether it was pasted or already present before the rule was applied. A Stop alert may block invalid typing but does not reliably prevent pasted data or clean old entries. Replace the value with a list item, check the alert setting, and review the affected range for other exceptions. If your Excel version offers Circle Invalid Data in the Data Validation menu, use it to highlight entries that fail the rule.

The rule is missing from some rows: Select a cell that works and a cell that does not, then open Data Validation for each to compare their settings. The rule may have been applied to only part of the range, or a later paste may have replaced validation. Reapply the rule to the complete intended range and test cells at the top, middle, and bottom.

Frequently Asked Questions

Can I make a drop-down list from cells on another worksheet?
Yes. Define a name for the source cells, then enter that name with a leading equals sign in the Data Validation Source box. For example, if the name is StatusChoices, use =StatusChoices.

Can the list update when I change the source choices?
Yes, when the rule points to the cells or named range that contain the choices, editing those source cells updates the choices shown. Check the defined name’s reference if the new items do not appear.

Can users type a choice instead of opening the arrow?
Yes. They can type an allowed value into the cell. If the Error Alert is set to Stop, Excel rejects a typed value that does not match an allowed choice. Pasting data may behave differently, so review pasted entries when accuracy matters.

How do I change or remove a drop-down?
Select the validated cell or range and choose Data > Data Validation > Data Validation. Edit the Source or other settings and select OK. To remove the rule, open the dialog and choose Clear All, then select OK. Clear All removes the validation settings; it does not clear the cell’s existing contents.

Why is my whole typed list showing as one option?
Excel may be using a different list separator because of regional settings, or the source may have been entered incorrectly. Try the separator expected by your installation. If you are unsure, place each choice in its own worksheet cell and use that range as the source.

Does a drop-down guarantee that every value in the workbook is valid?
No. Data validation helps guide normal entry, but pasted data and existing values may not follow the rule. Use validation together with review or other checks when the workbook needs reliable, clean data.

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.