Forum Discussion
I can't turn off autocomplete? (Latest Update)
Hello,
My excel has updated itself to the latest version, but the autocomplete won't turn off? I've unticked the 'enable autocomplete' box in Options/Advanced/Editing and also the autocorrect options under Proofing, but it is still autocompleting.
It's driving me mad! I'm doing a simple VLOOKUP from one work book to another and as soon as I hit enter, it fills the entire column all the way to row 7500+!
I've tried restarting and other workbooks.
Can anyone please help?
Thanks so much in advance :)
2 Replies
Thank you so much for your reply. Unfortunately, your suggestions have not worked.
I've found that when I press CTRL+end it jumps to row 1048576! So it clearly thinks there is an inordinate amount of data, when there isn't. I copied the data from both files into a new book using values only paste over 2 sheets and it still does it - even with the CTRL+Enter
I can only conclude there must be a bug in my update.
Thank you again.
You've already tried unchecking the 'Enable AutoComplete for cell values' option under File > Options > Advanced > Editing Options, which is the standard method to disable autocomplete. If this hasn't resolved the issue, consider the following steps:
- Check for Freeze Panes: Some users have reported that having freeze panes enabled can interfere with autocomplete functionality. To disable freeze panes:
- Go to the View tab.
- Click on Freeze Panes in the Window group.
- Select Unfreeze Panes.
Disabling freeze panes has resolved autocomplete issues for some users.
- Inspect Data Validation Settings: Autocomplete can be influenced by data validation settings. To check and clear these settings:
- Select the cells where autocomplete is problematic.
- Go to the Data tab.
- Click on Data Validation in the Data Tools group.
- In the Data Validation dialog, click Clear All.
This ensures no data validation rules are affecting autocomplete.
VLOOKUP Function Autofilling Entire Column:
With recent updates, Excel has introduced dynamic array functions, which can cause formulas to spill over into adjacent cells. This might be why your VLOOKUP formula fills the entire column upon pressing Enter. To control this behavior:
- Use Traditional Formula Entry:
- Instead of pressing Enter after typing your formula, press Ctrl + Enter. This will keep the formula confined to the selected cell without spilling over.
- Check for Table Formatting:
- If your data is formatted as a table, Excel might automatically fill formulas down the entire column. To prevent this:
- Click anywhere within your table.
- Go to the Table Design tab.
- Click Convert to Range in the Tools group.
This will remove table formatting and prevent automatic column filling.
My answers are voluntary and without guarantee!
I hope these suggestions help resolve the problems you're encountering.
