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.
How to AutoFill from List in Excel: 8 Quick Ways
This is today’s dataset where the Employee Name is given with their ID No. But other fields such as Joining Month, Joining Date and Salary have only one cell value. And, we have to fill the down to last row in 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 months 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 green 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: [Fixed!] Auto Fill Options Not Showing in Excel
2. Using the Fill Command from Excel Toolbar
The Fill command works similarly 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.
⏩ To do this, select the cell range with the given cell value.
⏩ Then click the Series option from the 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 Use Autofill Formula 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 be surprised when you know that auto-filling the dates, and name of the month are types of in-built custom lists. So, it is clear that the Custom List saves us time. We can generate the Custom List to fill data 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 in the blank space under the List entries.
⏩ After completing the listing, click on the 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) open the Custom Lists dialog box, and click on Import.
So, your list of 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.
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.
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.
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.
Read More: How to AutoFill Sequential Letters 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.
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 in James Smith, the first entry from the list. This way you can autofill without dragging.
Read More: How to Turn Off AutoFill in Excel
Download Practice Workbook
Conclusion
This is how we may autofill from a list in Excel quickly. Choose the suitable one according to your requirements. I hope this article might be beneficial for you. Anyway, if you have any queries or suggestions, please let them below.
