The AutoFill feature of Excel is an excellent tool that saves both time and labor. It has some built-in list like a series of numbers to autofill. But we can add a customized list in Excel which we can use to autofill later on. In this article, we will show 2 quick methods to create a custom AutoFill list in Excel.
How to Create a Custom AutoFill List in Excel: 2 Quick Methods
Custom autofill list can be created by using Excel Options very easily or we can use a short piece of VBA code to create the list. We are showing step-by-step procedures for both methods in the following sections.
1. Use Advanced Excel Options to Create Custom AutoFill List
In this method, we will discuss the use of Advanced Excel Options to create a custom autofill list. Follow the steps given below for this method.
- Firstly, create a dataset that has some items that we will use to autofill later on. In our dataset, we will use the names from the Store List column to autofill.
- Then, select the range of data for including them in the custom autofill list. We selected the range B5:B9.
- Afterward, go to the File tab in the Excel ribbon.
- Further, select Options from the appeared pane.
- After that, in the Excel Options window select Advanced > Edit Custom Lists.
- Consecutively, a Custom Lists window will appear.
- Select the range of data in the Import List from Cells We have selected it before. So, it will be filled in that case.
- Also, select Import > Add from the window. Press OK.
- Now, come back to the worksheet and write the first name of the list in any cell.
- Finally, use the Fill Handle to fill up the following cells.
- Bravo! We have successfully used the custom autofill list. See the screenshot given below.
Read More: How to Use Autofill Formula in Excel
2. Generate Custom AutoFill List Applying VBA Code in Excel
Another way to generate a custom autofill list is the use of a short piece of VBA code. We are showing the steps for this method below.
- Primarily, create a dataset. We have kept the Manager column blank to fill it with the custom autofill list.
- Then, right-click on the worksheet name tab and select View Code.
- Consecutively, a code window will appear. Write the following code there.
Sub Cutom_AutoFill()
Application.AddCustomList ListArray:=Array("John Carter", "Willium Smith", "Carl White", "Michael Dum", "Doughlus Liam")
End Sub
Note: In the code part: Application.AddCustomList ListArray:=Array(“John Carter”, “Willium Smith”, “Carl White”, “Michael Dum”, “Doughlus Liam”) insert the list of items you want to use for custom autofill list.
- Further, press the Run button from the VBA window.
- Afterward, come back to the worksheet and write the first item of the list in any cell. We wrote the first name John Carter in Cell C5.
- Finally, use the Fill Handle to fill up the cells below.
- As a result, we will see that using VBA code the cells are filled with the items we enlisted in the code.
Read More: How to Apply AutoFill Shortcut in Excel
How to AutoFill Series of Numbers in Excel
Excel has a built-in AutoFill feature for a series of numbers. In this example, we will show how to create different series using the Fill Handle of Excel. Let’s walk through the procedures.
- Firstly, create a dataset with 2/3 initial entries.
- See the screenshot below for an example. We will create Normal Sequence (1,2,3,4…), Odd sequence (1,3,5,7…), Even Sequence (2,4,6,8…) and Specific Sequence (1,4,7,10…).
- Then, select the initial 2/3 entries for the series and use the Fill Handle to create the whole series in the following cells.
- Again, we use the Fill handle to create the rest series in a similar fashion.
- Finally, we can see all the series created with the help of Fill Handle.
Read More: How to AutoFill Sequential Letters in Excel
Download Practice Workbook
You can download the practice workbook from here.
Conclusion
AutoFill feature of Excel is an excellent tool for use. Here, we have shown 2 quick methods to create a custom autofill list in Excel. If you have any queries or suggestions, please let us know in the comment section.
Related Articles
- AutoFill Cell Based on Another Cell in Excel
- How to AutoFill from List in Excel
- How to Turn Off AutoFill in Excel
- Fix: Excel Autofill Not Working
- [Fixed!] Auto Fill Options Not Showing in Excel
- [Fixed!] AutoFill Formula Is Not Working in Excel Table
- [Solved:] Excel Double Click AutoFill Not Working
<< Go Back to Excel Autofill | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!