Excel’s AutoComplete feature suggests entries based on your previous typing in a column. This saves time but can show outdated or incorrect values. The suggestions are stored in a hidden list that does not automatically clear. This article explains how to permanently remove old suggestions from Excel’s memory.
Key Takeaways: Clearing Excel’s AutoComplete Memory
- File > Options > Advanced > Clear AutoComplete memory: Deletes the hidden list of suggestions for all columns in the active workbook.
- Deleting the .xlb file: Removes all custom toolbar and AutoComplete data for your Excel profile, forcing a fresh start.
- Using a new, blank workbook: AutoComplete suggestions are workbook-specific; starting fresh avoids old data.
How Excel’s AutoComplete Memory Works
AutoComplete works by creating a separate suggestion list for each column in your workbook. When you type in a cell, Excel checks the list for that column and shows matches from entries made above. This list is stored within the workbook file itself, not in a central Windows location. It persists when you save, close, and reopen the file.
The feature only activates for text entries. It ignores numbers, dates, times, and formulas. Suggestions appear only when you are typing in a vertical column, not across rows. A common cause of wrong suggestions is importing data or copying cells from another source. Old, deleted entries remain in the column’s memory until you clear it.
Limitations of the Feature
AutoComplete does not learn from every sheet. Its memory is limited to the specific column you are working in. It also has a character limit for each suggestion. If you have two similar entries, like “Project Alpha” and “Project Alpha Final,” typing may trigger the wrong one. The feature cannot be selectively edited; you must clear all suggestions for a column or workbook.
Steps to Clear AutoComplete History
Use these methods to remove incorrect suggestions. The first method clears data for the current workbook. The second method resets your entire Excel profile.
Method 1: Clear Memory via Excel Options
- Open the problematic workbook
Launch Excel and open the file where you see wrong AutoComplete values. - Navigate to Advanced Options
Click File > Options. In the Excel Options dialog, select the Advanced category from the left sidebar. - Find the editing settings
Scroll down to the Editing options section. Ensure the checkbox for Enable AutoComplete for cell values is selected. - Clear the memory
Click the button labeled Clear AutoComplete memory. Click OK to close the dialog. This action removes the suggestion history for all columns in this workbook.
Method 2: Delete the Excel Toolbar Settings File
- Close Excel completely
Ensure all Excel windows are closed. Check the system tray for any running Excel processes. - Open the Run dialog
Press the Windows key + R on your keyboard to open the Run command box. - Open the Excel startup folder
Type %appdata%\Microsoft\Excel and press Enter. This opens the folder containing your profile’s Excel settings. - Delete the .xlb file
Look for files with the .xlb extension, such as Excel16.xlb. Select the file and delete it. Windows and Excel will create a new default file when you restart the application, clearing old toolbar and AutoComplete data.
If AutoComplete Still Shows Wrong Values
Excel Suggests Deleted Cell Contents
Clearing the memory only affects future typing. Existing cells with the wrong value must be manually corrected. Try typing the correct entry in several cells at the top of the column. This teaches AutoComplete the new, correct value, which will then appear first in future suggestions.
AutoComplete Not Working After Clearing
If suggestions stop entirely, verify the feature is enabled. Go to File > Options > Advanced. Under Editing options, confirm Enable AutoComplete for cell values is checked. Also, ensure you are typing in a column with at least one other text entry above the current cell.
Wrong Values Appear in a Shared Workbook
AutoComplete history is saved with the workbook file. If you clear the memory and send the file to a colleague, their Excel will use the cleared list. However, if they have their own .xlb file with old data, it will not affect this specific workbook. The issue is confined to the file’s internal storage.
Clearing AutoComplete Memory vs. Other Methods
| Item | Clear AutoComplete Memory (File > Options) | Delete the .xlb File |
|---|---|---|
| Scope of Effect | Only the currently open workbook | All workbooks and Excel customization for your user profile |
| Data Removed | Column-based typing history within the file | Toolbar layouts, Quick Access Toolbar, and all AutoComplete lists |
| Best Use Case | Fixing one corrupted file or removing project-specific old data | Resetting Excel after persistent glitches or profile corruption |
| Speed and Risk | Fast, low risk, and easily repeatable | More thorough but resets all personalization |
You can now remove incorrect AutoComplete suggestions by clearing the workbook’s memory or resetting your Excel profile. For ongoing projects, use the Clear AutoComplete memory button in the Advanced options. If you experience other interface issues, deleting the .xlb file provides a clean start. For advanced control, use Data Validation lists to create predefined, accurate dropdowns instead of relying on typed history.