How to AutoFill from List in Excel (8 Quick Ways)

Often we need to use AutoFill for filling the cell or cell range downwards, upwards, and so on instead of entering the data repeatedly. How would you feel if you can fill the cell range from a list automatically? In this article, I’ll discuss 8 quick ways to autofill the cell range from a given list in Excel with proper explanation.

Download Practice Workbook

8 Quick Ways to AutoFill from List in Excel

This is our today’s dataset where Employee Name is given with their ID No. But other fields such as Joining Month, Joining Date & Salary have only one cell value. And, we have to fill the below cell range.

Probably, you might know that Excel can autofill the dates, name of month & day within seconds. So, you don’t need any source data list in those cases if you have only one cell value. Here, the first two methods are quite simple to fill the below cell range in the case of month or dates using the Fill Handle Tool and the Fill command. Later, other fruitful ways will be discussed

1. Using the Fill Handle Tool

The Fill Handle Tool is one of the most popular AutoFilter features in Excel. Actually, the Fill Handle Tool appears as a green-colored small square at the right-bottom of the cell (also appears as a Plus sign).

However, if you drag down the cursor by selecting the square, you’ll get the following output.

Read More: How to Use Custom Autofilter in Excel for More Than 2 Criteria

2. Using the Fill Command from Excel Toolbar

The Fill command works similar to the Fill Handle Tool except it has some extra features. Whatever, if you wish to autofill the joining date except counting the weekend, that means, you want to count only weekdays.

⏩ For doing this, select the cell range with the given cell value.

⏩ Then click the Series option from Home tab > Fill command.

⏩ Next, the following dialog box will appear and check the circle before the Weekday unit.

⏩ After finishing the above task, you’ll get the output where the two dates (orange colored) exclude the weekend within days.

Read More: How to Autofill Days of Week Based on Date in Excel

3. Generating Custom AutoFill List

Custom List provides the opportunity to save a list of data in Excel, later you may use the list rather than re-typing. Undoubtedly, you’ll get surprised when you know that auto-filling the dates, name of the month are types of in-built custom lists. So, it is clear that the Custom List saves our time. We can generate the Custom List in two individual ways.

3.1. Custom AutoFill List from New Entries

If you need to produce a Custom List from new entries, follow the steps below.

⏩ Go to Excel Options by clicking File > Options.

⏩ Move to the Advanced option > click on the Edit Custom Lists.

⏩ Then you’ll see the Custom Lists dialog box, and type the new entries at the blank space under the List entries.

⏩ After completing the listing, click on Add option, and finally press OK.

⏩ Now, your newly created Custom List is ready to autofill. Just type the first entry and use the Fill Handle Tool.

If you do that, the following output will be found.

3.2. Custom AutoFill List from Existing Data

Again, you have the feature to import the existing dataset instead of re-typing that.

In order to do that, select the cell range (C5:C12) and open the Custom Lists dialog box, and click on Import.

So, your list of the Employee Name is ready for auto-filling, just type the first entry and use the Fill Handle Tool. So, the output will be as follows.

Read More : How to Create a Custom AutoFill List in Excel (2 Quick Methods)

4. Applying the Flash Fill Tool

Imagine you have to autofill a new list combining the given two lists. For example, you need to fill the field of Full Name combining the First Name and Last Name.

In such a situation, we have a faster tool namely Flash Fill.

Before doing that, input one cell manually (James Smith), then click on the Flash Fill from the Data Tools of the Data tab.

After clicking on the Flash Fill option (the keyboard shortcut is CTRL + E), you’ll get the auto-filled list.

Read More: How to Use Flash Fill in Excel to Split Data (4 Quick Ways)

Similar Readings

• How to Enter Sequential Dates Across Multiple Sheets in Excel
• How to Auto Number or Renumber after Filter in Excel (7 Easy Ways)
• 12 Automatic Ways of Numbering in Excel
• How to Turn Off AutoFill in Excel (3 Quick Ways)
• Flash Fill Not Recognizing Pattern in Excel (4 Causes with Fixes)

5. Using CONCATENATE Function

But if you don’t want to input any value manually, you may use the CONCATENATE function to combine the two text strings quickly.

The formula will be-

Here, C5 is the first name and D5 is the last name.

When you press Enter and use the Fill Handle Tool, you’ll see the following output.

6. Using SEQUENCE Function

Again, you may use the SEQUENCE function to fill the row or column (a list) with sequential numbers.

Say, you are filling the salary field based on the given first cell value i.e. $40,000. But you need to increase the salary by $500 for every row.

So the adjusted formula will be-

Here, 8 is the number of rows (D5:D12), 1 is the column number, 40000 is the fixed value, and $500 is the increment value.

Read More: How to Add Sequence Number by Group in Excel

7. Utilizing VBA to AutoFill from List in Excel

For a larger dataset, we may apply VBA to autofill from the given list without any hassle.

We can autofill a field from the given list by extracting the data from the list. For example, we can extract the last name from the Employee Name (full name) and autofill the field of Last Name.

Step 1:

Firstly, open a module by clicking Developer > Visual Basic.

Secondly, go to Insert > Module.

Step 2:

Then copy the following code into the newly created module.

Sub AutoFill_VBA1()
  Dim List1 As Range
  Dim List2 As Range
  Set List1 = Range("D5:D5")
  Set List2 = Range("D5:D12")
  List1.AutoFill Destination:=List2, Type:=xlFlashFill
End Sub

In the above code, I declared two lists (List1 & List2) as Range type. Then I set their corresponding range. Lastly, I used VBA AutoFill and xlFlashFill type to extract and then autofill the last name.

Step 3:

Next, run the code (the keyboard shortcut is F5 or Fn + F5) and the output will be as follows.

Read More: How to Use VBA AutoFill in Excel

8. Creating an AutoFill Enabled Drop-Down List 

Furthermore, we may create an AutoFill enabled drop-down list using the Combo Box.

⏩ Go to Developer tab > Insert option > Choose the Combo Box from the ActiveX Controls.

⏩ Next, draw the Combo Box over the D5 cell and right-click on the box, then pick the Properties option.

⏩ After that, fix the ListFillRange as C5:C12 as we want to use those lists.

Now, if you type J, it will automatically fill James Smith, the first entry from the list.

Conclusion

This is how we may autofill from a list in Excel quickly. Choose the suitable one according to your requirement. I hope this article might be beneficial for you. Anyway, if you have any queries or suggestions, please let them below.

Related Articles

• How to Repeat Rows for a Specified Number of Times in Excel
• Increment Month by 1 in Excel (8 Handy Ways)
• How to Perform Predictive AutoFill in Excel (6 Easy Ways)
• Fix: Excel Autofill Not Working (7 Issues)
• How to Create Automatic Rolling Months in Excel (3 Quick Ways)
• AutoFill Cell Based on Another Cell in Excel (5 Easy Methods)