How to Autocomplete Cells or Columns From List in Excel

Every time we do a monotonous task, we may wish we could have done things differently. Excel provides us 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.

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

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

Practice Workbook

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

Autocomplete from List

1. Autocomplete Using AutoFill

I. Complete Cells using AutoFill Feature

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.

Serial No Generate

To make Excel understand, we need to insert a couple of numbers (so that it can understand the sequence) and select them both.

Fill Handle Icon

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 like the image below.

Insert Couple of values

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

Options selection

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

Check Enable AutoFill

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. Double click the icon or drag it down to the remaining rows for this column.

AutoFill numbers - Excel Autocomplete From List

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.

Fill series - Excel Autocomplete From List

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

Series dialog box - Excel Autocomplete From List

And click OK. You will find the column is filled.

AutoFill numbers 2- Excel Autocomplete From List

Read more: How to Autofill Numbers in Excel

II. Complete Insertion from Suggestion

Excel provides us suggestions once we input something that resembles something that is the list.

Suggestions - Excel Autocomplete From List

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.

Complete suggestion value - Excel Autocomplete From List

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

No suggestions - Excel Autocomplete From List

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

Check Enable AutoComplete - Excel Autocomplete From List

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.

Column wise suggestions - Excel Autocomplete From List

We will find suggestions for a value that is inserted in this column.

Suggestions 2 - Excel Autocomplete From List

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

Insert suggestions value

Read more: How to Perform Predictive Autofill in Excel (5 Methods)

2. Autocomplete Using Flash Fill

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

I. Flash Fill to Combine

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

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

Generate full name data - Excel Autocomplete From List

First of all, we need to write a name manually.

Insert one cell manually

Here we have written “Alex Moriss” manually. Now use the AutoFill feature.

Error in autofill - Excel Autocomplete From List

We have found a bunch of “Alex Moriss”. No worries, click on the Auto Fill Options.  

Select Flash Fill

And then select Flash Fill. You will find the names are generated as we wanted.

Full Names - Excel Autocomplete From List

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

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

Split data

Again, you need to write the first one manually. Let’s use the Flash Fill differently.

Select Flash Fill from ribbon

Selecting the cell click the Flash Fill option from the Data Tools section from the Data tab. You will find the column is autocompleted.

Completes column with Flash Fill

There is a keyboard shortcut for the Flash Fill feature. We will use that for completing the Last Name column.

Last Name Split

Write the first name manually and selecting that cell press CTRL + E.

Shortcut of Flash Fill

It will autocomplete the column from the listed names.

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

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