An Excel drop-down list breaks in a few familiar ways: the arrow disappears, the list opens with the wrong choices, new items do not show, or the cell accepts anything people type. The fix starts with Data Validation, then moves through the source range, protection settings, and the workbook file itself. Use the steps below in order and stop when the list works again.
Turn the drop-down arrow back on
Select the cell or cells where the drop-down should appear, then open Data and choose Data Validation. Stay on the Settings tab and set Allow to List.
Turn on In-cell dropdown, then select OK. Click the cell again and test the arrow. Quick tip: when the arrow is hard to click, select the cell and press Alt plus Down Arrow in Excel for Windows.
Rebuild the list when the rule is missing
A drop-down list in Excel is a Data Validation rule. When that rule is gone, the cell behaves like a normal cell and accepts regular typing.
Put the valid choices in one column or one row with no blank cells. Select the target cell, open Data, choose Data Validation, and set Allow to List.
Use Source to select the list range or type the choices separated by commas. Keep In-cell dropdown turned on, use Ignore blank when blank entries are allowed, and select OK.
Fix a source range that no longer matches the list
Use this when the drop-down opens, but it shows old choices, misses new items, or points to the wrong cells.
Select a cell that already has the drop-down, then open Data and choose Data Validation. On the Settings tab, check Source. If Source points to a worksheet range, select the correct cells again.
If Source uses typed choices, edit the comma-separated values. Turn on Apply these changes to all other cells with the same settings when the same repair belongs on matching drop-downs. Then select OK.
Use an Excel Table for lists that keep growing
When your choices change often, a table source keeps the drop-down easier to maintain.
- 1.Put the list entries in an Excel Table.
- 2.Select the cell where the drop-down belongs.
- 3.Open Data, select Data Validation, and set Allow to List.
- 4.Set Source to the table or list cells, then select OK.
- 5.Add a new item at the end of the table later, or press Delete to remove one, and the associated drop-downs update automatically.
Repair a named range source
Select the drop-down cell, then open Data and choose Data Validation. Look at Source.
When Source uses a named range such as Departments instead of direct cells, open Formulas. Choose Name Manager, then select the name used by the drop-down.
Update Refers to with the correct list cells, then select Close. Choose Yes and test the drop-down again.
Excel for the web can use the workbook, but changing the named range itself requires desktop Excel.
Restore validation after paste removed it
The most common cause here is a normal paste that overwrote the cell's Data Validation rule.
Copy a nearby cell that still has the correct drop-down behavior. Select the target cells that lost the drop-down, then use Paste Special and choose Validation. Click one repaired cell and open the drop-down to confirm the list is back.
Remove a broken drop-down and create it again
When the settings are wrong and quick edits do not help, clearing the rule gives you a clean setup.
Select the affected cell or range, open Data, choose Data Validation, and select Clear All on the Settings tab. Select OK to remove the broken drop-down.
Create it again from Data, Data Validation, Settings, then set Allow to List and add the correct Source.
To find every cell with validation before clearing or repairing, press Ctrl plus G, choose Special, select Data Validation, then choose All or Same. On Mac, use Edit, Find, Go To, Special, then Data Validation.
If clearing and rebuilding fails, check sheet protection, Protected View, and workbook format next.
Unlock editing when Excel blocks Data Validation
Use these checks when Data Validation is unavailable or Excel opens the workbook without editing tools.
- 1.When a yellow message bar appears, select Enable Editing.
- 2.When a red message bar appears and you trust the file, select File, then Edit Anyway.
- 3.If the worksheet is protected, use File, Info, Protect, Unprotect Sheet, or go to Review, Changes, Unprotect Sheet.
- 4.Enter the password when Excel asks for it, then select OK.
- 5.For a work or school device that still stays in Protected View, ask the organization administrator to review the Protected View rules.
Check file format, corruption, and Office health
The most common cause at this stage is the workbook format, a damaged file, or the Office installation.
If the workbook is saved as an OpenDocument Spreadsheet, use File, then Save As, and choose an Excel workbook format such as .xlsx. Data Validation is only partially supported in .ods, so recreate or repair the validation rules after saving in Excel format.
When the Source field contains a broken reference such as #REF!, reselect the correct list range or repair the named range that feeds the list.
For a damaged workbook in desktop Excel for Windows, use File, Open, choose the location, select the workbook, open the arrow next to Open, choose Open and Repair, and select Repair. If Repair cannot recover the data, use Extract Data.
Update Excel when the problem affects multiple files. On Windows, open any Microsoft 365 app and go to File, Account, Product Information, Update Options, Update Now; on Mac, open Excel, use Help, Check for Updates, then choose Update or Update All.
Older guides point to the legacy Shared Workbook feature as a cause. Newer Excel hides those buttons and co-authoring replaced that older feature, so use the current repair paths above first.
Frequently Asked Questions
Why does my Excel drop-down arrow disappear until I click the cell?
Excel shows the in-cell drop-down arrow from the selected validated cell. Select the cell, then check Data Validation and turn on In-cell dropdown.
Why can people type values that are not in my drop-down list?
Turn on an Error Alert for the validated cells. In Data Validation, open the Error Alert tab and choose the Stop style. Stop makes people correct the entry before Excel accepts it. Warning and Information only flag the problem and still let the invalid value into the cell.
Can I fix Excel drop-down lists on my phone?
On iPhone, Excel can view and select existing validation choices. To add or update Data Validation rules, use Excel for the web or desktop Excel.
Why do new choices not appear in my Excel drop-down?
The Source range does not include the new cells. Edit the Source range in Data Validation, or use an Excel Table as the source so added items update automatically.