Like almost every other program, Excel comes with a few (?) annoyances, along with its great features. I butted heads with AutoComplete feature last week, and after a few attempts, we reached an agreement on how to work together nicely. Here's how you can stop unwanted AutoComplete in cells.
Please Don’t AutoComplete
I was working on a client’s workbook, and setting up a data entry sheet. On the worksheet, one of the columns is used to record the Approval status of an order. A popup comment shows you what the codes are, and you just have to type a letter into the column.
I ran into a minor aggravation though – if you typed an “A”, it automatically filled in the word “Approval”, because the AutoComplete feature was picking up that word from the heading cell.
Every time you typed the “A” code, you had to press Delete, to remove the unwanted letters, before pressing Enter.
AutoComplete is great in many places, because it makes data entry quicker and easier, filling in items that you’re entered previously. It was annoying in this column though, because we never wanted the full word, except in the heading.
So, I looked for the best way to prevent this from happening.
Turn Off AutoComplete
To get rid of this annoyance, I could hit Excel with a sledgehammer, and turn off AutoComplete everywhere.
- On the Ribbon, click the File tab
- Click Options, to open the Excel Options window
- Click the Advanced category, at the left
- Under Editing Options, remove the check mark for “Enable AutoComplete for Cell Values”
- Click OK, to close the window.
Now the AutoComplete won’t bother me in that column – but I won’t be able to use it anywhere else either.
So, I’ll turn that back on, and try something else.
Change the Heading
My goal was to prevent AutoComplete in the Approval column, but leave it on everywhere else. The heading started with “A”, and that’s why the “A” filled in, when I typed it in the column below.
If I changed the heading to “Status”, the problem would disappear, because the “S” wasn’t used as a code. However, I couldn’t change the heading, because “Approval” is what the client wanted.
But, what if the first character was a Space?
I typed a space character before the first letter in the heading, and tested the “A” code again. It worked! The AutoComplete didn’t kick in, because the heading no longer started with an “A”.
The heading still looks almost the same, and AutoComplete still works everywhere else in Excel.
It’s Not a Heading
I decided to test this in another workbook, before writing about it. So, I copied the headings and data to a new workbook, and removed the space character from the heading.
When I typed an “A” in the approval column, it stayed as is – the AutoComplete feature didn’t change it. That was strange!
Then I realized there was a difference between the new sheet and the old one. Can you spot the difference between this list, and the one in the screen shot above?
Excel saw the data in row 1 of the new list as headings – because there was nothing directly above that row. And, the good news is that Excel is smart enough to block the AutoComplete based on heading text.
In my client’s workbook, there is text in the row above the headings, so Excel treats that first row as the headings. As a result, our “heading” row was seen as part of the data, and the AutoComplete was filling in the “A” code.
To prevent that, I could have inserted a blank row above the headings, and hidden it, to prevent the AutoComplete for “A” entries.
So I now have two simple options for preventing the AutoComplete in that Approval column.