AutoComplete weirdness

[Mark Peterson]

I don't work in Excel much, but I was always under the assumption that
AutoComplete only worked if there were no gaps in the data in a given
column. However, I had a user come to me today and prove me wrong.
After some testing, here's what we discovered.

It turns out that there CAN be gaps in the data, as long as there no are
gaps in the data in an adjacent column.

For example, my user had a spreadsheet where column A was completely
filled - no gaps. Column B had gaps in the data - but AutoComplete still
worked.

Is this a bug, or just an undocumented "feature" in Excel? My user wants to
exploit this "feature" in another spreadsheet by inserting a dumny column
filled with dummy values and then hiding it - just so he can get
AutoComplete to work in an adjacent column.

- Mark

 

[Ken Johnson]

I don't work in Excel much, but I was always under the assumption that
AutoComplete only worked if there were no gaps in the data in a given
column. However, I had a user come to me today and prove me wrong.
After some testing, here's what we discovered.

It turns out that there CAN be gaps in the data, as long as there no are
gaps in the data in an adjacent column.

For example, my user had a spreadsheet where column A was completely
filled - no gaps. Column B had gaps in the data - but AutoComplete still
worked.

Is this a bug, or just an undocumented "feature" in Excel? My user wants to
exploit this "feature" in another spreadsheet by inserting a dumny column
filled with dummy values and then hiding it - just so he can get
AutoComplete to work in an adjacent column.

- Mark

Hi Mark,

Thanks for revealing that.
I tried it out and it works.
That's a very neat and useful trick.

Ken Johnson