Md. Sourov Hossain Mithun
Excel has some auto features which save a lot of time and give more speed while we are typing or inputting data. AutoComplete is one of them and we use it most. But after applying it you may want to edit the value or may want to edit while applying the AutoComplete feature. You will learn 4 tricky methods from this article to edit autocomplete in Excel.
Download Practice Workbook
You can download the free Excel workbook from here and practice independently.
What Is AutoComplete?
When we start typing in a cell, Excel shows duplicate suggestions from its connected cells, and without typing more we can get the duplicate values just by hitting ENTER then. It is called the AutoComplete feature.
4 Ways to Edit AutoComplete in Excel
First of all, get introduced to our dataset that represents some salesperson’s sales in different regions. Keep in mind, that there’s no direct way in Excel to edit the AutoComplete, Excel will work following its built-in way. But we can follow some alternative tricky ways to do this operation.
1. Using Pick from Drop-Down List Feature to Edit AutoComplete
In our very first method, we’ll apply the Pick from Drop-Down List Feature to edit AutoComplete in Excel. By using this, we’ll be able to choose a match from a list.
Steps:
- Right-click on the cell where you want to edit AutoComplete.
- Then select Pick From Drop-Down List from the context menu.
- Soon after, it will show the values from the connected cells, just select the required one. We selected California.
The cell is now filled with the selected one.
Read More: Autocomplete Data Validation Drop Down List in Excel (2 Methods)
2. Using Manual Way to Edit AutoComplete
We can edit the output of auto-complete manually. Like, if we want to insert New Mexico after New Jersey, Excel will show these two suggestions. But without typing New in AutoComplete, we can insert New Mexico using Flash Fill.
Steps:
- Select the cell.
- Next, double-click on the plus icon.
- Now the cell is filled with the same value.
- Select the last word- “Jersey” and type “Mexico”.
It will feel like we used AutoComplete. But it must have to be an adjacent cell.
Read More: How to Use AutoComplete in Excel (4 Easy Ways)
3. Using Custom AutoFill from List Range to Edit AutoComplete
We know, AutoComplete feature doesn’t work without connected cells. But we can edit the cells completed by a list taken from a non-connected location by using the Custom AutoFill option. Have a look at the image below, we created a separate list that we’ll insert.
Steps:
- Click on Options from the appeared menu.
- Then click on Advanced from the Excel Options window.
- After that, click on Edit Custom Lists from the General section.
- As we selected the list before so it will choose the range automatically, although you can set it from here too.
- Press the Import button.
- Now see, it’s showing the list. Just press OK now and you will get back to the previous window.
- Nothing to do more, hit the OK button.
- Here we’ll insert the list after Colorado. So, select it and drag down the Fill Handle icon.
Now see, the cells are completed with the list.
Read More: How to Populate a List Based on Cell Value in Excel (6 Ways)
4. Using Flash Fill Option to Edit AutoComplete
We can edit the AutoComplete with the format of its adjacent cell using the Flash Fill tool. Look at the dataset, Nevada is in left alignment but California is in center alignment. We’ll edit California’s format with Nevada’s format.
Steps:
- First, double-click on the Fill Handle icon of Nevada’s cell.
- Next, click on the AutoFill Options icon.
- Select Fill Formatting Only from the list.
The value was removed and only formats are remaining now.
How to Turn On or Off AutoComplete in Excel
Sometimes the AutoComplete feature bothers us because maybe we don’t want the matched values while typing. So, it’s better to keep it off. Let’s see how to turn it off.
Steps:
- Follow the 2nd and 3rd steps from the third method to open the Excel Options dialog box.
- Click on Advanced and unmark the Enable AutoComplete from cell values option from the Editing options section.
- Finally, just press OK.
Read More: How to Turn on Autocomplete in Excel (3 Easy Ways)
Important Notes
- There’s no direct way to edit the AutoComplete directly, Excel does it in its way.
- AutoComplete feature only works in connected cells.
Conclusion
That’s all for the article. I hope the procedures described above will be good enough to edit AutoComplete in Excel. Feel free to ask any questions in the comment section and give me feedback. Visit the site to explore more.
