How to Autocomplete from List in Excel (4 Easy Ways)

The article will show you how to autocomplete from list in Excel. Every time we do a monotonous task, we may wish we could have done things differently. Excel provides us with various features to complete from a list automatically. In this article, we will describe various techniques to autocomplete cells or columns from list in Excel. We are using Excel 2019, feel free to use yours.

How to Autocomplete from List in Excel: 4 Easy Ways

First things first, let’s get to know about the dataset that is the base of our examples.

Here we have a list of the first and last names of several random people. Using this dataset, we will show you how to autocomplete from a list.

Note that this is a basic table to keep things simple. In a practical scenario, you may encounter a much larger and complex dataset.

1. Using AutoFill to Autocomplete from List in Excel

To complete a column or row with the same or similar data we can use the AutoFill feature. Let’s see an example, we want to insert serial numbers to the names.

Steps:

• First, select the B5 cell.

• To make Excel understand, we need to insert a couple of numbers (so that it can understand the sequence) and select them both.
• Selecting both the cells if you place the mouse cursor at the bottom right corner, you will find a + symbol. This is called Fill Handle.

• By any chance, if you don’t see this symbol in the image below.

• Then click File and click Options from the window in front of you.

• Excel Options dialog box will appear in front of you. Click Advanced there.

• Now, from the section of Editing Options check Enable fill handle and cell drag-and-drop. And click OK.
• Now you should find the Fill Handle option. Then, double-click the icon or drag it down to the remaining rows for this column.

• You will find this column is complete. No need to do enter manually.
• Another way of doing this is to select a cell after inserting a number there and click Series from the Fill option in the Editing section of the Home tab.

• Series dialog box will come in front of you.

• Set the Step Value and Stop Value (here we have set 1 and 10 for these respectively).

• Then, click OK. As a consequence, you will find the column is filled.

This is how we can autocomplete the list using AutoFill.

Read More: How to Turn on Autocomplete in Excel

2. Inserting AutoFill from Suggestion to Autocomplete from List in Excel

Excel provides us with suggestions once we input something that resembles something that is the list. Let’s have a look at the following procedure.

Steps:

• First, insert the first one or two letters of a name in the corresponding column.

• Here we have Gabrial in the First Name column. Once we have inserted “G” it showed us Gabrial as a suggestion. If it’s okay with you press ENTER or TAB.

• If you don’t see the suggestions like the image below

• Then, open the Excel Options dialog box. And explore the Editing Options section within Advanced.

• Then, check Enable AutoComplete for cell values. And press OK. Now you will see the suggestions.
• Remember, you will see the suggestions column-wise only. Let’s say if we insert “G” in the Last Name column. It will not show suggestions.

• As a result, we will find suggestions for a value that is inserted in this column.

• Like we have a name Joe in this Last Name column, upon writing “J” it provides Joe as a suggestion. Then, hit ENTER or TAB to insert the suggested value.

This is how we can AutoFill a list using Insertion from suggestion.

Read More: How to Edit AutoComplete in Excel

3. Applying Flash Fill to Combine

In this section, we will see how to use autocomplete using the Flash Fill feature.

Flash Fill is another handy tool that helps combine other cell values into a new cell. Let’s explore with examples.

Steps:

• Let’s say we want to generate Full Name from First Name and Last Name.

• First of all, we need to write a name manually. Then go to the Home tab. Under Editing option, select the Fill option.

• Here we have written “Alex Moriss” manually. Now select the Flash Fill option.

• You will find the names are generated as we wanted.

Consequently, Flash Fill auto-completes the columns using the names from our list.

4. Implementing Flash Fill to Split

Similar to the combination, we can perform the split operation using Flash Fill. For example, we will separate the first and last names from our listed names.

Steps:

• You need to write the first one manually. Let’s use the Flash Fill differently.

• Selecting the cell click the Flash Fill option from the Data Tools section from the Data tab. Consequently, you will find the column is autocompleted.

• After that, there is a keyboard shortcut for the Flash Fill feature. We will use that for completing the Last Name column.

• Then, write the first name manually and select that cell press CTRL + E.
• As a result, it will autocomplete the column from the listed names.

This is how we can autocomplete the list using FlashFill to split.

Practice Workbook

You are welcome to download the practice workbook from the link below.

Conclusion

That’s all for today. We have listed several methods to autocomplete from a list in Excel. Hope you will find this helpful. Feel free to comment if anything seems difficult to understand. Let us know any other methods that we have missed here.

Further Readings

• How to Turn Off AutoComplete in Excel
• [Fixed!] AutoComplete Not Working in Excel

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