Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

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.


Practice Workbook

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


4 Suitable Ways to Autocomplete from List in Excel

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

Dataset of Excel autocomplete from list

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.

Using AutoFill to Autocomplete from List in Excel

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

Using AutoFill to Autocomplete from List in Excel

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

Using AutoFill to Autocomplete from List in Excel

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

Using AutoFill to Autocomplete from List in Excel

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

Using AutoFill to Autocomplete from List in Excel

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

Using AutoFill to Autocomplete from List in Excel

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

Using AutoFill to Autocomplete from List in Excel

  • Series dialog box will come in front of you.

Using AutoFill to Autocomplete from List in Excel

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

Using AutoFill to Autocomplete from List in Excel

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

Using AutoFill to Autocomplete from List in Excel

This is how we can autocomplete the list using AutoFill.

Read More: How to Autofill Numbers in Excel


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

Excel provides us 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.

Inserting AutoFill from Suggestion to Autocomplete from List in Excel

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

Inserting AutoFill from Suggestion to Autocomplete from List in Excel

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

Inserting AutoFill from Suggestion to Autocomplete from List in Excel

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

Inserting AutoFill from Suggestion to Autocomplete from List in Excel

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

Inserting AutoFill from Suggestion to Autocomplete from List in Excel

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

Inserting AutoFill from Suggestion to Autocomplete from List in Excel

  • 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.Inserting AutoFill from Suggestion to Autocomplete from List in Excel

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

Read More: How to Turn on Autocomplete in Excel (3 Easy Ways)


Similar Readings


3. Applying Flash Fill to Combine

In this section, we will see how to 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.

Read More: How to Populate a List Based on Cell Value in Excel


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

Read More: How to AutoFill Cell Based on Another Cell in Excel


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

Shakil Ahmed

Shakil Ahmed

My name’s Shakil. My articles are targeted to support you in enriching knowledge regarding different features related to Microsoft Excel. I am fond of literature, hope some knowledge from them will help me providing you some engaging articles even though some weary technical terms.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo