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.
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 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. 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 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.
- 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 Turn on Autocomplete in Excel (3 Easy Ways)
Similar Readings
- How to Use Autofill Formula in Excel (6 Ways)
- Autofill Dates in Excel (3 Suitable Methods)
- Automatic Numbering in Excel (9 Approaches)
- How to Auto Populate Cells in Excel Based on Another Cell
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.
Very helpful but need auto prompt drop down when whenever a cell is selected.
Dear BENJAMIN THORPE
Thank you very much for your interest in our article. You can create an auto-prompt dropdown in Excel. In the dataset, we have the names of multiple people.
Simply follow the steps below:
● Go to the Developer tab >> Insert >> Combo Box (ActiveX Control).
● Then place the newly created ComboBox.
● Double-click on the ComboBox.
● A VBA window will pop up. Paste the following code into the window.
● Now, go back to the worksheet and click on the Design Mode from the Developer tab to turn it off.
● Double-click on the ComboBox and you will see an InputBox asking for the list.
● Select the list from your dataset and press OK.
● You will have the auto-prompt drop-down list.
You can also download the Excel file from here.
Excel Auto Prompt Drop-Down.xlsm
If you have any more queries, please let us know in the comments.
Regards
Md. Abu Sina Ibne Albaruni
Team ExcelDemy