How to Edit AutoComplete in Excel (4 Easy Methods)

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.

how to edit autocomplete in excel


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.

Using Pick from Drop-Down List Feature to Edit AutoComplete

  • 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.

Using Manual Way to Edit AutoComplete

  • 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:

  • Select the range of the list.
  • Later, click on the File tab.

Using Custom AutoFill List Range to Edit AutoComplete

  • Click on Options from the appeared menu.

Select Options to Edit AutoComplete

  • Then click on Advanced from the Excel Options window.
  • After that, click on Edit Custom Lists from the General section.

Go to Advanced Section to Edit AutoComplete

  • 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.

Import List to Edit AutoComplete

  • 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.

Using Flash Fill Menu to Edit AutoComplete

Steps:

  • First, double-click on the Fill Handle icon of Nevada’s cell.

Apply Fill Handle tool to Edit AutoComplete

  • 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:

How to On or Off AutoComplete in Excel

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.


Related Articles

Mithun

Mithun

Hello! I am Md. Sourov Hossain Mithun. Welcome to my profile. Currently, I am working at Exceldemy as an Excel and VBA Content Developer. Excel is amazing software. Here I will post excel related useful articles. I am a graduate of Bangladesh University of Engineering and Technology. I love to learn new things and work with them. Thank you.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo