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.
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.
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.
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 like 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. 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).
And click OK. You will find the column is filled.
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.
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.
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.
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. Hit ENTER or TAB to insert the suggested value.
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.
First of all, we need to write a name manually.
Here we have written “Alex Moriss” manually. Now use the AutoFill feature.
We have found a bunch of “Alex Moriss”. No worries, click on the Auto Fill Options.
And then select Flash Fill. You will find the names are generated as we wanted.
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.
Again, 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. You will find the column is autocompleted.
There is a keyboard shortcut for the Flash Fill feature. We will use that for completing the Last Name column.
Write the first name manually and selecting that cell press CTRL + E.
It will autocomplete the column from the listed names.
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.