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.
- AutoFill Cell Based on Another Cell in Excel
- How to AutoFill from List in Excel
- How to Apply AutoFill Shortcut in Excel
- How to Fill Down Blanks in Excel
- How to Repeat Formula Pattern in Excel
- How to Use Autofill Formula in Excel
- How to AutoFill Sequential Letters in Excel
- How to Auto Number Cells 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.
- How to AutoFill Numbers in Excel
- How to Autofill Numbers in Excel Without Dragging
- How to AutoFill Ascending Numbers in Excel
- How to AutoFill Numbers in Excel with Filter
- How to Auto Number or Renumber after Filter in Excel
- How to Link PowerPoint Chart to Excel
- How to Add Sequence Number by Group in Excel
- How to Repeat Number Pattern in Excel
- 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 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.
- How to Autofill Dates in Excel Without Dragging
- How to Enter Sequential Dates Across Multiple Sheets in Excel
- How to Autofill Days of Week Based on Date in Excel
- How to AutoFill Months in Excel
- How to Autofill Dates in Excel
- How to Increment Month by 1 in Excel
- How to Create Automatic Rolling Months in Excel
- Filling a Certain Number of Rows in Excel Automatically
Download Practice Workbook
You can download the practice workbook from here.
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.
- How to Autofill a Column in Excel
- How to Fill Column in Excel with Same Value
- How to Fill Down to Last Row with Data in Excel
- How to AutoFill Formula When Inserting Rows in Excel
- How to Auto Populate from Another Worksheet in Excel
- How to Perform Predictive AutoFill in Excel
- Applications of Excel Fill Series
- [Fix] Excel Fill Series Not Working
- Fix: Excel Autofill Not Working
- [Fixed!] AutoFill Formula Is Not Working in Excel Table
- How to Turn Off AutoFill in Excel
- [Fixed!] Auto Fill Options Not Showing in Excel
- [Solved:] Excel Double Click AutoFill Not Working
- Excel VBA: Autofill Method of Range Class Failed
- How to Use VBA AutoFill in Excel
- AutoFill Formula to Last Row with Excel VBA
- AutoFill Not Incrementing in Excel?