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

Get FREE Advanced Excel Exercises with Solutions!

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.

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

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

how to create a custom autofill list in excel result

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.

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.

how to create a custom autofill list in excel result

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

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


<< Go Back to Excel Autofill | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Mehedi Hasan Shimul
Mehedi Hasan Shimul

Md. Mehedi Hasan, with a BSc in Electrical & Electronic Engineering from Bangladesh University of Engineering and Technology, holds a crucial position as an Excel & VBA Content Developer at ExcelDemy. Driven by a deep passion for research and innovation, he actively immerses himself in Excel. In his role, Mehedi not only skillfully addresses complex challenges but also exhibits enthusiasm and expertise in gracefully navigating tough situations, emphasizing his steadfast commitment to consistently deliver exceptional and quality content.... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo