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

Get FREE Advanced Excel Exercises with Solutions!

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.

Dataset

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

Using the Fill Handle Tool

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

Using the Fill Handle Tool

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.

Using the Fill Command

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

Using the Fill Command

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

Using the Fill Command

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.

Opening the 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.

Excel AutoFill from List Generating Custom AutoFill List

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

Excel AutoFill from List Generating Custom AutoFill List

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

Excel AutoFill from List Generating Custom AutoFill List


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.

Excel AutoFill from List Generating Custom AutoFill List

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.

Excel AutoFill from List Generating Custom AutoFill List

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.

Applying the Flash Fill Tool

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

Applying the Flash Fill Tool

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


Similar Readings


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-

=CONCATENATE(C5,D5)

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.

Excel AutoFill from List-Using CONCATENATE Function


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-

=SEQUENCE(8,1,40000,500)

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.

Using SEQUENCE Function

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.

Utilizing VBA to AutoFill from List

Step 1:

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

How to Insert VBA Code

Secondly, go to Insert > Module.

How to Insert VBA Code

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

Utilizing VBA to AutoFill from List

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.

Utilizing VBA to AutoFill from List

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.

Creating an AutoFill Enabled Drop-Down List

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

Creating an AutoFill Enabled Drop-Down List

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

Creating an AutoFill Enabled Drop-Down List

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

Creating an AutoFill Enabled Drop-Down 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

Md. Abdul Kader

Md. Abdul Kader

Hi! I am Abdul Kader and presently working as ‘Excel & VBA Content Developer’ at Exceldemy. I publish my articles related to Microsoft Excel here. In 2019, I completed my graduation in Urban and Regional Planning from Chittagong University of Engineering and Technology. Having extreme eagerness to learn, I want to develop my skills and capabilities higher.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo