Leading the way in Microsoft Office Development

Step 1 - What is AutoComplete ?

This feature examines the contents of the active column in the data directly above and tries to anticipate what you are about to type.

AutoComplete is the automatic filling in of your text when you start typing and is switched on by default.

This will reduce the amount of text you have to enter by preventing you typing in repetitive words.

AutoComplete matches only exact cell entries, not individual words in a cell. This does not work when entering or editing formulas.

Make sure this feature is enabled (Tools > Options)(Edit tab, Enable AutoComplete for Cell Values). It should be checked by default.

This can be very useful if you are entering data into a table containing duplicate entries.

Whenever you enter text in a cell Excel searches just that column of the current region to see if the characters match an existing text string in the column. If it finds a matching text string it will automatically fill in the remaining characters.

Be aware that this can also lead to mistakes if you are entering data that is similar but slightly different.

You just need to type in the first letters or digits of an entry and any matching entries will be displayed automatically.

If the first few letters you type match another cell in the same column Excel will complete the text for you automatically.

Step 2 - Using AutoComplete

Make sure you can edit directly in cells. Check your (Tools > Options)(Edit tab, Edit directly in cell).

If you are unable to edit directly in cells then the entry will auto complete in the formula bar.

You can accept the suggestion by pressing either the (Enter) or (Tab) keys.

It is possible to also accept the suggestion by pressing any of the arrow keys or by selecting another cell.

You can remove the highlighted suggestion by pressing the (Delete) key.

Autocomplete is only displayed after enough characters have been entered in order to determine which value matches.

To ignore just keep typing. AutoComplete only matches complete cell entries and not individual words.

An alternative to using AutoComplete is to use the Pick from List drop-down list.

This can be displayed by press (Alt + Down Arrow) or using the Cell shortcut menu.

Step 3 - Pick from a List

An alternative way to enter values that have been entered before without re-typing the value is to use the Pick from List feature.

A quicker way to select an existing entry is to right mouse click and select “pick from List” on the shortcut menu. This will display a drop-down of all the possible values ?

Pick from list available when you right mouse click to display the Cell shortcut menu. The list contains all entries that have been previously keyed in.

Pick from list - If a column contains several words all starting with the same characters, it may be easier to select an entry from a drop-down list of choices.

This is available from the Cell shortcut menu. A shortcut menu is activated by pressing the Right mouse button as opposed to the Left.

Select the cell directly below and press the right mouse button. Select "Pick From List".

 Cell Shortcut menu

A quick way to display the Pick from List drop-down box is to press (Alt + Down Arrow).

This will display a drop-down list of the unique entries in that column from the data directly above.

The list will be automatically sorted into alphabetical order.

Just select the value you would like to enter.

Step 4 - Things to Remember

AutoComplete will only match on exact cell entries and not on individual words within a cell.

This feature is on by default although you can switch if off by changing your (Tools > Options)(Edit tab, Enable AutoComplete for Cell Values).

AutoComplete does not work on numbers or when editing any formulas.