When to not use Autofill in Excel

Excel has its own autocomplete option that by default is switched on when you get Excel out the box. Depending on your task this feature can be a helpful one but also sometimes a hindrance so read on to find out a little more about this Excel feature and how to switch it off, or back on again.

What is Autocomplete?

The autocomplete option in Excel works a little like the predictive text function found on nearly all mobile phones and tablets these days. Excel will recognise similarities in what you are typing with other values in that column and effectively show autocompleted text for you to select by pressing the enter key:

00059_Excel Autocomplete Explained_01

As can be seen in the above image, Excel recognises the text “Peter” from cell A2 so as soon as you type the letter “P” into cell A5 it attempts to autocomplete with “Peter” for you.

Times when Autocomplete is helpful

Excels autocomplete can be helpful when managing large data-entry based spreadsheets. For example, if you have an Excel spreadsheet that you update every day with staff members latest sales and you are continually typing the same names then having to autocomplete switched on will become a time-saving benefit to you, especially when you have a team full of unfamiliar names.

Times when Autocomplete is not so helpful

The autocomplete feature does have some limitations and it is important to know these if you utilise this feature.

It will only work on continuous data which means if your spreadsheet is likely to contain blanks then autocomplete is rendered useless. For example if in the above spreadsheet you skip cell A5 and try to type “Peter” into cell A6 then you will have to type all of the text, Excel will not offer any autocomplete:

00059_Excel Autocomplete Explained_02

As can be seen in the above image, typing “P” into cell A6 shows Excel does not attempt to autocomplete the text because of the blank in cell A5.

Another limitation to the autocomplete feature is that it only works on single column based data-entry so effectively it only looks up to find similar values, never sideways. If you tried to enter a list of names across columns A to Z then Excel would offer no autocomplete options for you.

Turning Excels Autocomplete feature off

It is very much task dependant but there are times where you might prefer to have the autocomplete feature switched off. If you are entering a large amount of data into a spreadsheet and the values are rarely the same then you might find autocomplete becomes a bit of a nuisance, and in some cases it can also lead to errors as you might accidently press the enter key and select an autocompleted version of text instead of typing in the correct text…something I expect we are all too familiar with when using a mobile phone predictive text for sending an SMS message

To turn off the autocomplete feature in Excel 2010 first go to File;Options:

00059_Excel Autocomplete Explained_03

Next select the Advanced option and uncheck the box next to “Enable Autocomplete for cell values

Then press “OK” on the bottom left and that’s it, autocomplete is now switched off.

For users of Excel 2007 the process is almost the same, select the Office Button found in the top left of your Excel window, then select “Excel Options” which will bring up a similar window to the above where you can select the “Advanced” tab and uncheck the autocomplete box.

For more advanced Excel features, click here

Happy Excelling
Team Excelgoodies

Leave a Reply

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