Cell AutoComplete Stopped Working

November 08, 2022 - by Bill Jelen

Problem: I am typing data in column A. When I type the first few letters in a cell, Excel suggests the complete entry and I only have to press Enter. Suddenly, AutoComplete is not working for one customer.

Strategy: AutoComplete is cool. In Figure 809, you only need to type Q and Enter to fill the cell with Que.

But, in Figure 810, Excel won’t suggest an entry. Excel isn’t sure if you mean “Peachpit” or “Peachpit Press”.

This demonstrates how AutoComplete works. A1:A6 says Publisher, QUE, Sams, Peachpit, Holy Macro!, QUE. When you start to type Q in A7, the AutoComplete suggests QUE. All you need to do is press Enter or tab to accept the suggestion. — Figure 809. Excel suggests entries from the prior list.

It appears that AutoComplete stopped working. Cell A4 says PeachPit. Cell A8 says Peachpit Press. In A9, you've typed Peachp but AutoComplete has not suggested anything. That is because there are two different entries above that start with Peachp and Excel does not know which you want to use. — Figure 810. Excel stops using AutoComplete.

AutoComplete won’t suggest until you type enough characters to make the entry unique. When you have two names that are identical until near the very end, AutoComplete will barely save you any typing.

AutoComplete will be practically no use if the values you are typing are:
John Jacob Jingleheimer Schmidt Sr. and John Jacob Jingleheimer Schmidt Jr. The AutoComplete won't jump into action until you get to the final J or S, and at that point, you are only an r. away from the end of typing. — Figure 811. AutoComplete will save you very little typing in this case.

Another AutoComplete frustration: Let’s say you’ve entered Pearson Publishing and now you want to enter just Pearson. You will have to type Pearson, but if you press Enter, Excel will fill in Publishing.

Instead, press the delete key to erase the suggested characters. You can then press Enter.

You type Pearson, but AutoComplete has typed Publishing and highlighted the Publishing part. You can't press Enter here. or Excel will put Pearson Publishing in the cell. — Figure 812. You can’t press Enter now.

The final AutoComplete frustration is when you have some blank cells in your data. AutoComplete will not be able to find an entry that appears above the blank cell.

There are two workarounds for this.

Before you start doing data entry, select the range where you will be entering data, perhaps A1:A100. Type =”” and press Ctrl+Enter. The AutoComplete will work through the blank cells now.
Alternatively, insert a temporary column A that is non-blank. Fill A1:A100 with the number 1. You can now leave blanks in column B and AutoComplete will still work.

AutoComplete only looks at values in the "Current Region". With a blank cell in A6, typing Holy Macro in A7 and then Q in A8 will not trigger AutoComplete, because the last Que is on the other side of the blank cell, up in A5. — Figure 813. AutoComplete won’t offer values before the blank cell.

Blank cells are not a problem for AutoComplete if data in an adjacent column is not blank. If pressing Ctrl+Asterisk will select the matching cell, then AutoComplete will find it. — Figure 814. Non-blank cells in adjacent column prevent blank issue.

This article is an excerpt from Power Excel With MrExcel

Title photo by Towfiqu barbhuiya on Unsplash