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 Fig 774, you only need to type Q and Enter to fill the cell with Que.
But, in Fig 775, Excel won't suggest an entry. Excel isn't sure if you mean "Peachpit" or "Peachpit Press".
-
Excel suggests entries from the prior list.
-
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 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 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 won't offer values before the blank cell.
- Non-blank cells in adjacent column prevent blank issue.