How to Edit AutoComplete in Excel (4 Easy Methods)

Get FREE Advanced Excel Exercises with Solutions!

Excel has some auto features that 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.


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.


How to Edit AutoComplete in Excel: 4 Easy Methods

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: How to Autocomplete from List in Excel


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


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.


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 off AutoComplete in Excel.

Steps:

How to On or Off AutoComplete in Excel

Read More: How to Turn on Autocomplete in Excel


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.

Download Practice Workbook

You can download the free Excel workbook from here and practice independently.


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.


<< Go Back to Autocomplete Excel | How to Create Excel Formulas | Excel Formulas | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Md. Sourov Hossain Mithun
Md. Sourov Hossain Mithun

Md. Sourov Hossain Mithun, an Excel and VBA Content Developer at Softeko's ExcelDemy project, joined in October 2021. Holding a Naval Architecture & Marine Engineering degree from BUET, Mithun showcases expertise during his two-year tenure. With over 200 published articles on Excel topics, he earned a promotion to Team Leader, excelling in leading diverse teams. Mithun's passion extends to Advanced Excel, Excel VBA, Data Analysis, and Python programming, contributing significantly to the innovative and dynamic environment of ExcelDemy... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo