Excel autocomplete – Excelchat

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

Work-around:

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

Instant Connection to an Excel Expert

Most of the time, the problem you will need to solve will be more complex than a simple application of a formula or function. If you want to save hours of research and frustration, try our live Excelchat service! Our Excel Experts are available 24/7 to answer any Excel question you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.