If an Excel data validation drop-down is not showing, select the cell and check Data > Data Validation > Settings: Allow must be set to List, and In-cell dropdown must be checked. The arrow normally appears only while that validated cell is selected, so it will not remain visible when you click elsewhere.
How the Excel validation drop-down works
A data validation list is a rule attached to a cell. It can restrict entries to a set of values, and, when the in-cell dropdown option is enabled, let someone choose an item from an arrow beside the selected cell. The arrow is a temporary control in the cell, not a permanent icon displayed throughout the worksheet.
That distinction solves many apparent display problems. If you can see the arrow after selecting the cell, the feature is working as designed. If you expect to see arrows down an entire column while no cell is active, Excel does not display them that way. Click a different cell and the prior arrow disappears; select another cell with its own list rule and its arrow appears there.
The list is separate from the worksheet’s filter controls. A filter arrow appears in a table or filtered range’s header and filters records. A validation arrow offers permitted values for one selected cell. These features can look similar, but turning on a filter will not restore a missing validation list.
Check the rule and the in-cell dropdown setting
In Excel for Microsoft 365 on Windows, select one affected cell, then go to Data > Data Validation. On the Settings tab, look at Allow. It should say List for a list-based drop-down. If it says Any value, Decimal, Date, or another type, that cell does not currently have a list rule.
When Allow is set to List, make sure In-cell dropdown is checked. If it is cleared, Excel can still apply a list validation rule, but it will not show the convenient arrow for choosing a value. Check the source shown in the dialog as well, then select OK to save any correction.
If you selected several cells together, Excel may show a mixed or ambiguous state when their validation rules differ. Check a single cell at a time to avoid assuming that every selected cell has the same settings. Also confirm the exact cell address in the Name Box, to the left of the formula bar. It is easy to inspect a nearby cell that looks identical but has no rule.
- Select the cell where you expect the list.
- Open Data > Data Validation and review the Settings tab.
- Set Allow to List and enable In-cell dropdown.
- Verify the list source, then select OK and test the cell again.
Confirm that the rule is on the cell you are using
Validation belongs to particular cells. A rule on D5 does not automatically apply to D6, even if both cells have the same formatting or sit in the same column. If only some cells show an arrow, compare a working cell with a nonworking one using Data > Data Validation. Check the selected address and whether the dialog reports the same list settings.
Rules can be lost or changed when cells are copied, pasted, cleared, or replaced. Pasting a copied cell can bring its validation settings into the destination, so a paste from an ordinary cell may replace a destination’s list rule. Formatting alone is not proof that validation remains: cell color, borders, and number formats can look right even when the rule is gone.
To repair one cell, you can copy a known-good cell that has the intended rule and paste it to the affected cell, but consider whether you also want to copy its formatting and value. If you only need to restore validation, inspect Paste Special options in your Excel version rather than assuming a normal paste changes only the rule. For a larger range, reapply the rule deliberately and verify a few cells at the top, middle, and bottom.
Clearing the displayed value is not the same as fixing or removing validation. A blank cell can still have a working list rule, and an existing value that is no longer in the list does not by itself prove the drop-down is broken. The validation setting and its source determine whether the list control is available.
Inspect the list source and its contents
If the arrow appears but the menu is empty, incomplete, or populated with unexpected items, focus on the list source rather than the arrow setting. In the Data Validation dialog, inspect the Source field. It may refer to cells containing the permitted values or to a named range. Confirm that the referenced cells still contain the intended items and that the reference has not shifted after rows or columns were inserted or deleted.
For a simple example, a worksheet might store choices such as Pending, In progress, and Complete in H2:H4, with a validation list on a separate entry cell. If the source points to the wrong cells, the arrow can still open while showing blanks or unrelated text. A source range containing empty cells can produce blank-looking entries, so remove unintended blanks or adjust the range to cover only the options you want.
When the options are on another worksheet, Excel commonly uses a defined name for the source rather than a direct reference to a different sheet in the Source box. You can review names through Formulas > Name Manager. Check that the name exists, refers to the correct cells, and has not been scoped to a different worksheet than the one where the validation rule needs it. If a workbook was copied or renamed, verify these references rather than assuming they updated as intended.
A typed list is another source option. It is useful for a short, stable set of choices, but it is less convenient to maintain than a range when values change often. A range-based list also makes it easier for someone editing the workbook to see and update the available options. For lists that grow over time, consider a carefully maintained source range or a named range that includes the intended entries, and test the result after adding a new item.
Check worksheet layout, protection, and selection
Look for layout features that make the arrow hard to see or the cell difficult to select. A narrow column, a nearby shape, or a floating object placed over the cell can make the control appear absent even when the rule is intact. Click the cell itself and inspect the Name Box to confirm the selection. If a shape is covering the grid, move it temporarily or use the Selection Pane to locate it.
Merged cells can complicate selection and make it less obvious which cell owns a rule. If the area is merged, select it carefully and inspect the validation settings; consider whether unmerging and applying the rule to a straightforward cell range would make the workbook easier to use. Avoid changing the layout until you know whether the validation itself is missing, especially in a shared template.
Sheet protection does not automatically mean that a validation arrow has been removed. However, protection may prevent you from changing the validation settings, and it can limit which cells users can select. If you are authorized to edit the workbook, check Review > Unprotect Sheet or ask the workbook owner for access. Do not remove protection on a workbook you are not permitted to change. A protected sheet may be intentionally designed so people can select only certain unlocked cells.
Distinguish a missing arrow from a broken list
There are two different symptoms: no arrow appears, or an arrow appears but the list does not behave as expected. For no arrow, first check that the cell is selected and that In-cell dropdown is enabled. For a visible arrow with wrong options, inspect the source range or named range. For an arrow that opens normally but does not stop an unwanted entry, review the Error Alert tab in Data Validation; the alert can be turned off, and its style affects how Excel responds to invalid entries.
Also distinguish a validation list from a combo box inserted as a form control or ActiveX control. A combo box is an object placed on the worksheet and may have its own linked cell and input range. A data validation list is a setting on a cell and is configured through Data > Data Validation. If the control has resize handles when selected, or sits over the grid as an object, it may be a combo box rather than cell validation. The repair steps differ, so identify which feature the workbook uses before changing it.
Existing invalid values are another source of confusion. A validation rule can be added after data is already present, and those earlier entries are not necessarily erased just because they do not match the list. The arrow may still work. To check whether a value is invalid, compare it with the permitted options or use Data > Data Validation to inspect the rule; do not treat the displayed contents alone as a test of whether the arrow exists.
Excel for the web and Mac differences
In Excel for Microsoft 365 on Windows, the main repair path is Data > Data Validation. Excel for Mac also provides data validation from the Data tab, although the dialog layout and some ribbon details can differ from Windows. Look for Data Validation and the List settings, then verify the in-cell dropdown option. Keyboard shortcuts and dialog navigation can differ by platform, so using the ribbon is the clearest route for this check.
Excel for the web can display and use many data validation lists, but editing options and dialog behavior may differ from the desktop app and can change with the web interface. If the rule is present but you cannot find a needed setting in the browser, open the workbook in the desktop application if available. Before making changes to a shared workbook, confirm that you have edit permission and that your change will not disrupt other users.
If the file is in a format or workflow that does not preserve a feature as expected, use a copy to check whether the issue is specific to the workbook or the app. For example, a workbook that has been converted, imported, or edited by another spreadsheet program may have altered validation settings. Keep the original intact while investigating, particularly if the file is a template used by other people.
A reliable repair and prevention routine
Start with the smallest possible test: select one affected cell and open its validation settings. This avoids making broad changes before you know whether the problem is selection, configuration, or the source list. If one cell works and its neighbor does not, compare their rules. If none work, inspect a representative cell and check whether a protection or application issue is involved.
- Identify the exact cell and confirm it is selected.
- Check that Allow is List and In-cell dropdown is enabled.
- Verify the source cells or defined name and remove unintended blanks.
- Compare a working cell with a failing one to find inconsistent rules.
- Test the repaired cell by selecting it and opening the list; then test another cell in the affected range.
For prevention, keep the list source in a clearly labeled area or on a dedicated worksheet, and use a defined name when that makes the source easier to maintain. Protect the worksheet only after confirming that users can still select the intended entry cells. When pasting data into validated cells, use a method appropriate to the workbook and recheck the rule afterward. A brief test after editing can catch a lost validation setting before the workbook is shared.
Frequently Asked Questions
Why does the drop-down arrow disappear when I click another cell?
The arrow is shown for the currently selected cell, not permanently beside every validated cell. Select the cell with the list again to display its arrow.
Why is the arrow missing even though the cell has a list?
Open Data > Data Validation, select the cell, and make sure Allow is List and In-cell dropdown is checked. Also confirm that you are examining the exact cell with the rule.
Why does my Excel drop-down open but show blank options?
The source may include empty cells, point to the wrong range, or use a defined name that no longer refers to the intended options. Check the Source field and inspect the referenced cells or name.
Can I show every validation arrow all the time?
No. The normal in-cell validation arrow appears when its cell is selected. If users need a permanently visible control, a form or ActiveX combo box is a different feature and requires separate setup.
Does protecting a worksheet remove data validation?
Protection does not by itself erase the rule, but it can restrict selection or editing. If the arrow or settings are inaccessible, check which cells are selectable and ask the workbook owner about protection before changing it.