When you type text in a cell and the first couple of letters match an existing entry in that column, Excel tries to be helpful and automatically completes the value for you. If you don’t like the feature you can switch it off or you can use Excel’s little-known Pick From drop-down list feature. Or better still, keep your hands on the keyboard and use its shortcut.
Here’s how:
1. Select the cell where you want to enter the text.
2. Press ALT + down arrow. (Or right-click and select Pick From Drop-down List.)
3. Use your arrows and press ENTER to add the text. (Or use your mouse and click on the relevant entry.)
You can use this feature in an Excel table or in “normal” workbooks. You can even use the ALT + down arrow trick if you want to select entries from a drop-down list you created using Data Validation. Which makes me realise I’ve never written a tip about that. Watch this space!
Finally, if you cannot bear AutoComplete you can turn it off.
Here’s how:
1. Select Options from the File tab.
2. Type an A (or click on Advanced if you must).
3. Under Editing options untick the Enable AutoComplete for cell values check box.
4. Press ENTER (or click OK).
Related tips
Autocomplete formulas including its open bracket
Quickly format and enable filtering of your data
Following guidance from the UK government on the coronavirus (COVID-19) pandemic, classroom training at our premises in Cambridge will be suspended until at least the end of May. We continue to offer 1-hour virtual training and support sessions. Please get in touch by email or call us on 01223 214177 to discuss your requirements. When the time is right, we look forward to welcoming you back at the Sun House.
11 May 2020
Karen Roem offers software training and support through her company Roem Ltd. Contact her by email (Karen@roem.co.uk) or visit her website at www.roem.co.uk