The AutoComplete feature in Excel automatically fills in data as we type. This can be a very useful tool when we have to enter repeating values in a column.
Figure 1. Final result: AutoComplete filling in the characters
In the above example, AutoComplete is enabled. The moment we type “M” into cell B6, Excel autofills the rest of the cell and displays “Mathematics”. We can opt to press the Enter key to accept the suggested string or continue typing the word or phrase.
How to enable AutoComplete
- Click the File tab > Options
- In the Excel Options window, select Advanced Under Editing options and select the checkbox for Enable AutoComplete for cell values
Figure 2. Enable AutoComplete option
AutoComplete not working
There are times when AutoComplete is not working even when it is enabled in Excel Options. The reason may be either of the following:
- The value we are trying to AutoComplete is entered in another column
Note that AutoComplete only works for values along the same column as the active cell. In the example below, “Mathematics” is present in column B, not column C. Hence, typing “M” into cell C5 won’t trigger AutoComplete to fill in.
Figure 3. AutoComplete not working in another column
- The column is not contiguous, meaning there are empty cells within the column
AutoComplete presents values when all cells above the active cell are filled with values. In the example below, B7 is empty. Thus, no suggestions are presented in B8.
Figure 4. AutoComplete not working after empty cells
We can do either of the following:
- Fill the empty cells above with a value, such as a period, any symbol or a simple space
Here we have inserted a space in cell B7 and “Mathematics” is automatically presented in B8.
Figure 5. Output: AutoComplete working after filling in empty cells
- Fill the adjacent column with values, which could be numbers, symbols or spaces
This method works even if we enter a value several cells below the last entered value.
Figure 6. Output: AutoComplete working after numbering adjacent column