AutoComplete/Pick From List Doesn't Use All Data in Column (130047)

SYMPTOMS

In Microsoft Excel, when you display the Pick From list, the list may not contain all of the entries in the column you are editing as expected. Additionally, when you enter data in a cell on your worksheet with the AutoComplete feature enabled, if the first few letters you type match an existing entry in the column, the existing entry may not be automatically entered in the cell as expected.

CAUSE

This behavior occurs if the values in the column in which you are entering data are not within the "current region" that you are entering data in. This behavior is by design of Microsoft Excel. The AutoComplete feature and the Pick From list use only the entries in the active column in the current region. The "current region" is bounded by any combination of blank rows and blank columns. For example, when you select cell A6 below, the current region is A4:A6, and the Pick List only includes the values east and west:

   A1: north
   A2: south
   A3:
   A4: east
   A5: west
   A6:

The Pick List may not contain all of the values in the column if the column contains blank cells.

RESOLUTION

To include more values from the column in the Pick From list and the AutoComplete list, avoid having blank cells in the column in which you are entering data.

MORE INFORMATION

To locate the current region on your worksheet, do the following:

On the Edit menu, click Go To.
In the Go To dialog box, click Special.
In the Go To Special dialog box, click the Current Region option, and then click OK.

The current region on the worksheet is selected.