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

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.

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.

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.

Download Practice Workbook

You can download the practice workbook from here.

Create Custom AutoFill List.xlsm

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.

<< Go Back to Excel Autofill | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!