Quick answer: Select the cell and open Data > Data Validation. On the Settings tab, make sure Allow is set to List and In-cell dropdown is checked, then check that the Source box still points to your items. If the arrows have vanished from every cell at once, go to File > Options > Advanced and set For objects, show to All. Keep in mind that the arrow appears only next to the cell you’ve selected.
In this guide
- Match your symptom
- Fix 1: Turn In-cell dropdown back on
- Fix 2: Select the cell to see the arrow
- Fix 3: Show objects in the workbook again
- Fix 4: Finish editing, unprotect or stop sharing
- Fix 5: Repair a broken Source reference
- Fix 6: Point to a list on another sheet with a name
- Fix 7: Make the list pick up new items
- Fix 8: Remove blank entries from the list
- Fix 9: Check zoom, column width and recalculation
- Fix 10: Excel for the web and pasted cells
- Related guides
Applies to: Excel for Microsoft 365, Excel 2021, Excel 2019 and Excel 2016 on Windows. Excel for the web is covered briefly in Fix 10. Excel for Mac uses different menus in places and isn’t covered step by step.
A drop-down list in Excel is a data validation rule: the cell accepts entries from a list, and Excel draws a small arrow beside it so you can pick one. When the arrow is missing or the list opens empty, the rule itself, a workbook display setting, or the range the list reads from has changed. Start with the table below.
Match your symptom
| What you see | Possible cause | Go to |
|---|---|---|
| No arrow, even with the cell selected, but the cell still rejects other entries | In-cell dropdown is cleared | Fix 1 |
| The arrow shows only on the cell you click | This is how Excel displays drop-downs | Fix 2 |
| Arrows vanished from many cells, often after adding shapes, buttons or pictures | Objects are hidden in the workbook options | Fix 3 |
| The Data Validation button is unavailable | Cell in edit mode, protected sheet or shared workbook | Fix 4 |
| The list is empty, shows old items or the Source shows #REF! | The source range was deleted or moved | Fix 5 |
| The list lives on another sheet or file and stopped working | The reference to that sheet or file broke | Fix 6 |
| New items you add to the source never appear | The Source is a fixed range, not a table | Fix 7 |
| The list has empty lines | Blank cells inside the source range | Fix 8 |
| The arrow is tiny or entries are cut off | Zoom level or column width | Fix 9 |
| Works in the desktop app but not in a browser, or a pasted cell lost its list | Excel for the web limits, or validation overwritten | Fix 10 |
Fix 1: Turn In-cell dropdown back on
A data validation list can exist without an arrow. The In-cell dropdown box controls whether Excel draws it, and Microsoft’s own steps for creating a list include checking that box. If it’s cleared, the cell still refuses entries that aren’t in the list, but you have nothing to click.
- Select the cell, or every cell, that should have the drop-down.
- Go to Data > Data Validation.
- On the Settings tab, confirm that Allow shows List.
- Select the In-cell dropdown check box.
- If other cells use the same rule, select Apply these changes to all other cells with the same settings, then select OK.
If Allow shows Any value, the rule was removed, not just hidden. Set it back to List and fill in the Source box as described in Fix 5.
Fix 2: Select the cell to see the arrow
Excel doesn’t show a drop-down arrow on every validated cell all the time. In an answer on Microsoft’s community forum, the responder explains that the arrow generally appears only when you click the cell. That catches people out when they expect a form full of visible arrows, the way filter buttons sit on a header row.
To find out which cells have a list, go to Home > Find & Select > Data Validation. Microsoft describes this command as the way to locate cells with validation; Excel selects them all, so you can see where the lists are. If you want users to spot them without clicking, give those cells a fill or border, or add an Input Message on the Data Validation dialog so a hint appears when the cell is selected.
Filter arrows on a header row are a separate feature. If those are the arrows you’re missing, see Excel filter not working.
Fix 3: Show objects in the workbook again
When the arrows disappear from every list at once, check the workbook’s object display setting. The same community answer points to this option, and it’s easy to switch off without noticing, because it also hides shapes, pictures, charts and form controls.
- Go to File > Options.
- Select Advanced and scroll to Display options for this workbook.
- Under For objects, show, choose All.
- Select OK and click one of the list cells again.
The forum thread also notes that the problem began after extra fields and form controls were added to the sheet, and other users reported that arrows returned after they deleted some controls or images. If the setting is already on All, look for stray objects using Home > Find & Select > Selection Pane. Our guide to Excel cannot shift objects off sheet explains how to find and remove hidden objects safely.
Fix 4: Finish editing, unprotect or stop sharing
If you can’t even open the Data Validation dialog, Excel is blocking changes. Microsoft gives three reasons the command becomes unavailable:
- You’re still typing in a cell. Press Enter or Esc to leave edit mode, then try again.
- The sheet is protected. Select Review > Unprotect Sheet and enter the password if there is one. Microsoft notes that Excel can’t recover a lost password, so if you inherited the file, ask the person who protected it.
- The workbook is shared using the older shared workbook feature. Validation settings can’t be changed while sharing is on, so stop sharing, make the change and share again.
Microsoft also recommends setting up validation before you protect a sheet, and unlocking the validated cells first; otherwise people can’t type in them or pick from the list. If the file opens read-only because someone else has it open, see Excel file is locked for editing by another user.
Fix 5: Repair a broken Source reference
The Source box tells Excel where the items come from. If those cells were deleted, cleared or moved somewhere the reference doesn’t follow, the list can open empty, show the wrong items, or the Source can turn into a #REF! error. Microsoft also says Excel ignores validation while a validated cell contains an error such as #REF! or #DIV/0!.
- Select a cell with the drop-down and open Data > Data Validation.
- Look at the Source box. It should show either a list of items separated by commas, a range such as
=$A$2:$A$20, or a name such as=Departments. - Click in the box and select the cells that hold your items. Microsoft’s instructions say to leave out the header row.
- Select Apply these changes to all other cells with the same settings and then OK.
Before you change an unfamiliar range, note down what the Source box shows. Other cells or formulas might depend on the same items.
Fix 6: Point to a list on another sheet with a name
Keeping the items on a separate sheet is a good idea; Microsoft suggests you can then hide and protect that sheet so nobody edits the list by accident. For a list on another sheet, Microsoft’s data validation notes recommend naming the range first:
- Go to the sheet with the items and select them, without the header.
- Select Formulas > Define Name (in the Defined Names group) and give the range a name with no spaces, for example
Departments. - Back on the drop-down cell, open Data > Data Validation and type
=Departmentsin the Source box.
A list stored in a different workbook is less dependable. As general guidance, a reference to another file relies on that file staying in the same place, and it can stop working when the file is renamed, moved or closed. Copying the items into a hidden sheet in the same workbook removes that dependency.
Fix 7: Make the list pick up new items
If you add an item under the source list and it never shows up, the Source is pointing at a fixed range that ends before the new row. Microsoft recommends putting the items in an Excel table, so the drop-down updates by itself when you add or remove items.
- Select any cell in your source list and press Ctrl+T.
- Check the range, keep My table has headers selected if the list has a header, and select OK.
- Select the item cells (not the header), name them with Formulas > Define Name, and use that name in the Source box as in Fix 6.
Two exceptions: Microsoft says validation can’t be added to an Excel table linked to a SharePoint site, so unlink the table or convert it to a range first. And if the items come from formulas, Excel needs to calculate them. Microsoft points out that uncalculated cells can stop validation from working, so set Formulas > Calculation Options > Automatic. See Excel formulas not calculating if values still don’t refresh.
Fix 8: Remove blank entries from the list
When the Source range includes empty cells, those empty cells tend to appear as empty lines in the list, which makes it look broken or pushes items out of view. Tighten the range so it starts at the first item and ends at the last, or sort the items so the blanks fall below the range.
The Ignore blank check box does something different. According to Microsoft, it lets the validated cell be left empty; it doesn’t hide empty lines in the drop-down. Clearing it makes Excel reject an empty entry, but validation checks only what people type, so it won’t force anyone to fill in a cell they never select.
Fix 9: Check zoom, column width and recalculation
The arrow is drawn at the sheet’s zoom level, so at a very small zoom it can be hard to see or click. Set the zoom back to 100% with View > 100% or the slider at the bottom right of the window, then select the cell again.
Entries that look cut off are a width issue. Microsoft says the width of the drop-down follows the width of the cell that has the validation, so widen that column to show long entries. Microsoft’s setup steps also suggest checking column width and row height so every entry is visible.
Fix 10: Excel for the web and pasted cells
Microsoft’s data validation notes say that for workbooks opened in a browser, validation should be created in the desktop version of Excel first. That note names Excel Services and the Excel Web App, which predate today’s Excel for the web, and menus in Excel for the web change over time, so if a list misbehaves only in a browser, open the file in the desktop app with Open in Desktop App (where available) to check and edit the rule.
A drop-down can also disappear because the cell no longer has a rule. Pasting a whole copied cell over a list cell can replace its validation with whatever the copied cell had. Microsoft notes that validation messages don’t appear for pasted or filled data either. Use Home > Find & Select > Data Validation to see which cells still have lists, then reapply the rule to the rest with Fix 1. To paste values without touching the rule, use Paste Special and choose Values.
For more ways to speed up work with lists and tables, see our Excel tips and tricks.
Related guides
- Excel cannot shift objects off sheet: how to fix it
- Excel filter not working: how to fix it on Windows 11
- Excel formulas not calculating or updating: how to fix it
- Excel file is locked for editing by another user: how to fix it
- Excel tips and tricks: 10 ways to work faster in Excel
Leave a Reply