This page is an advertiser-supported excerpt of the book, Learn Excel 2007-2010 from MrExcel - 512 Excel Mysteries Solved. If you like this topic, please consider buying the entire e-book.
Cell AutoComplete Stopped Working
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 751, you only need to type Q and Enter to fill the cell with Que.
But, in Figure 752, Excel won’t suggest an entry. Excel isn’t sure if you mean “Peachpit" or “Peachpit Press".
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.
Figure 753 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.
Figure 754 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.
Figure 755 AutoComplete won’t offer values before the blank cell.
● 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.
Figure 756 Non-blank cells in adjacent column prevent blank issue.