Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

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.


Download Practice Workbook

You can download the practice workbook from here.


2 Quick Methods to Create a Custom AutoFill List in Excel

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 Store List column to autofill.

how to create a custom autofill list in excel

  • Then, select the range of data for including them in the custom autofill list. We selected the range B5:B9.

create a custom autofill list

  • Afterward, go to 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.

how to create a custom autofill list in excel using advance option

  • Consecutively, a Custom Lists window will appear.
  • Select the range of data in 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.

how to create a custom autofill list in excel result

Read More: Use Autofill Formula in Excel (6 Effective Ways)


2. Generate Custom AutoFill List Applying VBA Code in Excel

Another way to generate 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.

Generate Custom AutoFill List Applying VBA Code in Excel

  • 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 the cells are filled with the items we enlisted in the code.

how to create a custom autofill list in excel result

Read More: How to Fill Down Blanks in Excel (4 Quick Methods)


How to AutoFill Series of Numbers in Excel

Excel has built-in AutoFill feature for 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…).

How to AutoFill Series of Numbers in Excel

  • 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 Add Sequence Number by Group in Excel (2 Formuals)


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. Visit our ExcelDemy Website for similar articles regarding Excel.


Related Articles

Mehedi Hasan Shimul

Mehedi Hasan Shimul

Hi! I am Mehedi Hasan Shimul. As I am an Engineer solving different problems with the help of Excel amuses me. I write Excel related different problem solving articles here. Hope it will help you.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo