Stop Unwanted AutoComplete in Cells

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.

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.

autocompletefix01

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.

autocompletefix03

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.

autocompletefix02

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.

autocompletefix04

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!

autocompletefix05

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.

autocompletefix06

So I now have two simple options for preventing the AutoComplete in that Approval column.

____________________

You may also like...

10 Responses

  1. Kyrre says:

    Exactly my problem too.,. Great!

  2. bill says:

    wow…….. so simple once you get IT. LOL.

    Thanks, Deb.

    This was a good thing to know.

    I have a similar issue… auto complete sometimes fills in KA (initials for someone).. sometimes tho it just fills in the “K” I haven’t figured that one out yet.

    :)

    /b

  3. Peter Li says:

    haha, interesting post. I just found if a row is bolded, Excel will treat it as a heading thus preventing autocomplete as well. So the key is let Excel believe the first row is indeed a heading.

  4. bill says:

    AHHHHHH. ok… Yeah that could be it..

  5. Ben says:

    Really helpful; I would however say that when I added the blank row I was no longer able to use Sort and filter.

  6. Uttam Prakash Manher says:

    What will I do if I want auto complete in a specific column and not in another column. Any suggestion?

  1. February 17, 2014

    […] heading text was filling in when I typed an "A" code in a column. See how I fixed the AutoComplete problem, and what caused […]

Leave a Reply to Debra Dalgleish Cancel reply

Your email address will not be published. Required fields are marked *