When you work with worksheets, sometimes you will need to enter the same text multiple times. To automate this task, Microsoft introduced the AutoComplete feature.

To better illustrate how this tool works, take a look at the following example:

Here, you can see that name “John Smith” appears in cell B2. The Name “John Collins” in cell B3 and the name “Sophie Richardson” in cell B4. As soon as you start typing, Excel compares your input to the cells located above.

When you type the first letter “J”, Excel won’t give you any hints, because it doesn’t know whether you mean “John Smith” or “John Collins” or maybe a completely different name.

But when you type “John C” Excel will “know” that there is a good chance that you meant “John Collins”, because there is no other “John”, whose last name begins with the letter “C”.

If the text suggested by Excel is the one you want, you can go to the next cell, but if you meant a different one, then continue typing until Excel guesses what you are looking for.

Limitations of AutoComplete

The AutoComplete feature has several limitations:

  • It only works with text. It doesn’t work with numbers and dates.
  • It doesn’t check the data in other columns than those located above.
  • If there is a break in the form of an empty field between typed text and the values above, then Excel will treat it as another list, even though it is in the same column.

AutoComplete Options

If you want to disable AutoComplete, you can do it in FILE >> Options >> Advanced >> Editing Options >> Enable AutoComplete for cell values.

Tomasz Decker is an Excel specialist, skilled in data analysis and financial modeling.