Excel 下拉式清單不顯示、出現空白或缺少專案

Tested in: The COUNTIF checks below were evaluated in Google Sheets on 12 November 2026. Excel 365 menu names are the current desktop labels; I did not click through Data Validation in Excel this session.

If you still need to create the list, start with How to Add a Drop-Down List in Excel. This page is about a list that should already be there and is not behaving.

The drop-down arrow never appears

Select the cell and open Data > Data Validation. If the dialog says “Any value,” this cell has no rule — you may have pasted values only, or applied the list to a neighboring column. Set Allow to List, enter the source, and leave In-cell dropdown checked.

If the rule is already a List but there is no arrow, In-cell dropdown is usually off. Turn it back on. A hidden arrow can also be a very narrow column or a cell covered by an object (a chart, a text box, a comment). Unhide the column and send shapes to the back.

In Google Sheets, the newer Data validation panel has a Display style (or “Show dropdown list in cell”) option. If that is set to a rejection-only warning with no chip, you will not see a list.

Sheet protection can block changes without hiding the arrow. If you can see the list but cannot pick an item, unprotect the sheet or unlock the cell.

The list opens but the first item is blank

The Source range almost always includes an empty cell. A typical mistake is =A1:A20 when A1 is the header “Region.” Change the source to =A2:A20. The same thing happens when the list was built with a blank row at the top “for spacing.”

A trailing blank at the bottom of a long range is less obvious: the list still opens, and you can scroll to an empty entry at the end. Tighten the range, or convert the source to a table and point at the table column so new rows are included without the blanks below them.

Items are missing, or a typed value is rejected

If the list is fixed at $A$2:$A$10 and you later added rows 11–15, those new items will not appear until you widen the source. Point at a table column or at a named range that already covers the extra rows.

When you type a value that looks identical to a list item and Excel still rejects it, the usual cause is a leading or trailing space in the source (or in what you typed). The cleanup in TRIM not working applies here. In the test workbook I checked membership with:

=COUNTIF({"East";"West";"North"},"East")
=COUNTIF({"East";"West";"North"},"South")

On 12 November 2026 those returned 1 and 0. Point COUNTIF at your real source column and at the cell you are typing into; a 0 means the two strings are not actually equal, even if they look the same on screen.

Merged cells in the input range, or in the source, also break lists in a way that looks like missing items. Unmerge first, then reapply the rule.

The list is on another sheet or depends on another drop-down

A source such as =Lists!A2:A20 works in current Excel 365 and in Google Sheets. Older Excel builds were stricter and wanted a named range. If the dialog refuses the reference, follow the named-range steps in list from another sheet.

A second drop-down that should change based on the first one is a different problem: that is an INDIRECT-based dependent list, not a broken single list. Get the first list working on its own before you wire the second.

Once the arrow appears and COUNTIF on the source returns 1 for a value you expect to be allowed, the list is fine — any remaining “it will not accept this” message is a mismatch in the text, not a missing dropdown.