I am experiencing a minor annoyance is with the usually useful autofill.  The data in column A in my spreadsheet is either a date or the string Balance, the string Balance is fixed once at the top of column A in row 2 (rows 1 and 2 are frozen).  As Balance is entered into the lower rows of column A typing just B autofill will offer Balance, however the further down the rows the entry is made the more of the string needs to be typed before the full string is offered.  I cannot be exact but by the time about row 150 is reached Bal is needed and even lower down, say about row 200 I need to type  Balanc.  I don't remember this happening in earlier versions. How can I get the whole string "Balance" offered all the time?

Hi Malc1110,

It might be excel is getting overloaded comparing too many similar\ambiguious entries.

To my understanding, this auto-complete works on the prediction of unique word formed by the characters typed in a new cell.

Also there must not be blank cells between rows. Space character also counts.

Example:

ABC
 BE
ABCDEF
BE
MA
DA
DA
DA

In the above example DA,MA,BE is unique and hence the auto-complete will continue to work for them, (until overloaded).

But for A or AB or ABC, excel can find 2 matches ABC and ABCDEF hence, there is no single winner here, hence no auto-complete.

As you hit ABCD, excel finds that there is only one matching ABCDEF and hence suggests it.

Now lets add another word ABCF

ABC
 BE
ABCDEF
BE
MA
DA
DA
DA
ABCF

Now if you see the ABCD is still unique, where as ABC is not, hence hitting ABCD will still prompt ABCDEF.

But if you add any other word like ABCDJJKK, the uniqueness will be broken again.

• Its not much of a help if you have lots of similar looking data.
• If you are working in a large list, you may need to pause a moment to allow Excel time to complete an entry.

One tip, that I use is , if you are entering 'Balance' too frequently you may enter " Balance" or "-Balance" to vary the options.

And if Column B has only "Balance" as the String, I would suggest you to enter "Bal" only, later use replace option to replace everything at a go.

More detailed version below:

http://excel.tips.net/T002110_Understanding_AutoComplete.html

Microsoft Excel doesn't always complete my column entries.