Are you looking for the answers to the question “how to create a custom AutoFill list in Excel”? If yes, continue reading this article where I’ll describe some solutions that are easy to implement by anyone.

When you use Microsoft Excel for data entry and data storage, you often need to enter repetitive or sequential data. The data entry tasks also include entering long lists. Manual entry of such data requires a lot of time and effort. Also, there is a high chance of error during manual data entry.

All these issues can be avoided using the AutoFill list feature of Excel. AutoFill is particularly useful when you need to fill a column or row with a sequence of numbers, dates, months, days of the week, etc.

If you don’t know how to create a custom AutoFill list in Excel, this blog is for you. Here, you’ll learn some useful and efficient approaches to creating a custom AutoFill list.

What Is a Custom AutoFill List in Excel?

AutoFill is a feature of Microsoft Excel that allows you to automatically fill a series of data into cells based on the pattern of the existing data. It’s a time-saving attribute that streamlines the process of populating cells with sequential and repetitive data.

A custom AutoFill list is a personalized list of entries that can be created in Excel to fill in cells with data automatically. This feature is used for entering lists of names, dates, and many more. Using this can save time and effort, especially while working with a lot of data.

Excel saves the Custom Lists on the registry of the local computer. So, you can access all the Custom Lists from any Excel workbook on your PC. However, if you send any of those workbooks to someone else via email, the recipient won’t be able to access those Custom Lists on their PC.

At the time of writing, Microsoft hadn’t yet made the Custom Lists feature available on Excel for the web or Excel online app.

Reasons to Create a Custom Autofill List in Excel

• A custom AutoFill list lets you have consistency in data entry by letting you accurately autofill cells with predefined values.
• While you’re working with a huge amount of data in Excel, AutoFill list helps you avoid typographical errors.
• Instead of repeatedly typing the same values, you can populate cells with the preferred list of items using the AutoFill feature. Thus, it saves time by eliminating the need to manually enter the data.
• Data analysis of an Excel file needs data integrity and standard format. AutoFill can ensure that specific terms or values are used throughout a dataset.
• Custom AutoFill ist offers a user-friendly way to input data for long lists or categories. It also makes the data entry process more efficient.
• This feature enables you to create different custom lists based on the unique requirements of your work. Thus, you can tailor the AutoFill feature for specific projects or datasets.

Now that you’ve learned more about the Custom AutoFill List in Excel and why you would want to use it, find below some effortless methods to create one:

Create a Custom AutoFill List On Excel Options

First, create a list of items you want in the Custom List as text strings on the Excel worksheet. Insert the content or dataset in a column as shown above.

Click the File tab on the ribbon and choose Options from the left-side navigation panel.

On the Excel Options dialog, select the Advanced option on the left. On the right side menu, scroll down to the General section.

Now, you must see the Edit Custom Lists button. Click on it to bring up the Custom Lists dialog box.

On the Custom Lists dialog, select the NEW LIST item on the left. You can add the items of the Custom List below into the List entries field manually by typing each item separated by a comma.

Alternatively, click on the Up Arrow icon beside the Import button and highlight the previously created data column on the spreadsheet.

Now, click the Down Arrow button on the Custom Lists dialog.

On the Options dialog, click the Import button to add the list items to the Custom List you’ve just created. Click OK twice to complete the process.

Now, open a new workbook or worksheet. Type the first item of your Custom List. Then, drag down the AutoFill handle to automatically generate the rest of the Custom List items.

Create a Custom AutoFill List Using the Sort Tool

If you’re unable to find the Edit Custom List button on your Excel desktop app, you can access it using the Sort tool. Here’s how:

On the Home tab, go to the Editing commands block and click on the Sort & Filter drop-down menu.

Click on the Custom Sort menu on the Sort & Filter context menu.

Now, you should see the Sort dialog. There, click on the A to Z drop-down list and click on the Custom List option.

The Custom List dialog will pop up on your Excel worksheet. Now, you can follow the previously explained steps to create a new Custom List.

Create a Custom AutoFill List on Mac

The user interface elements of Excel for Mac are slightly different from the Windows Excel software edition. Thus, you won’t find File > Options > Excel Options in Excel for Mac.

To open the Custom List editing tool, open an Excel workbook on your Mac. Click on the Excel menu on the macOS Toolbar.

On the context menu that opens, click on the Preferences option. Now, on the Excel Preferences dialog menu, you should find the Custom Lists option below the Formulas and Lists.

Once you get to the Custom Lists dialog, the steps are the same as the Windows Excel app to create a Custom AutoFill List.

Create a Custom AutoFill List For Numbers Only

The Custom AutoFill tool of Excel can’t process numerical values. You must embed numbers within text strings in the Custom List.

Alternatively, you can create a column of numbers that you want in your Custom List. Then, convert that dataset to text strings. Now, Excel will accept this list of numbers as a dataset of text strings.

Here’s how you can convert your numerical dataset to text strings:

Go to your dataset and highlight a blank cell in the next column. Enter the following formula into the highlighted cell. Hit Enter to convert a numerical value to a text string.

=TEXT(E2,"0")

Now, drag the fill handle down the column to convert the rest of the numbers to text strings.

That’s it! You can now follow the steps explained earlier to create a Custom AutoFill List using these numbers which are actually texts in Excel.

Create a Custom AutoFill List Using View Code

You can also use the View Code tool of an Excel worksheet to quickly insert a short Custom List in the Excel workbook. The list will also be saved locally in the computer’s registry.

In this method, you need to write a simple Excel VBA script. I’ve included the VBA script below so you can reuse it on your own worksheet.

Open the target worksheet and right-click on the worksheet name tab at the bottom of the worksheet. On the context menu that shows, click on the View Code option.

You should see a blank module on the Excel VBA Editor. There, copy and paste the following script:

Sub Cutom_List()
Application.AddCustomList ListArray:=Array("X", "Y", "Z", "X1", "Y1", "Z1", "X2", "Y2", "Z2")
End Sub

To customize the script, simply replace X, Y, Z, etc. with items from your own list. To add more list items, simply expand the above array list by separating two items using a comma. Don’t forget to put the new items inside double quotes. Click the Run button to execute the VBA script.

You can now find the newly added content in the Custom List dialog box.

Prompt-Based VBA Script For Custom AutoFill List

If you don’t want to manually create your Custom List on Excel, you can take advantage of this Excel VBA macro. When you run it, Excel will show a message box to find out how many items are there in the list. Then, Excel will show a prompt box to enter the list of items one at a time.

To use the script, go to your Excel workbook and open any worksheet. Press the Alt + F11 keys on your keyboard to launch the Excel VBA Editor tool.

On the tool, click the Insert button and select the Module option. This will create a new module in the Excel VBA Editor.

Inside the new module, copy and paste the following script. This script doesn’t need any modifications and you can use it as is.

Sub CreateCustomList()
    Dim customList As Variant
    Dim listItem As String
    Dim inputCount As Integer

    ' Prompt the user for the number of items in the list
    inputCount = InputBox("Enter the number of items in the list:", "Custom List")

    ' Check if the user entered a valid number
    If Not IsNumeric(inputCount) Or inputCount <= 0 Then
        MsgBox "Please enter a valid positive number.", vbExclamation
        Exit Sub
    End If

    ' Resize the array to store the list items
    ReDim customList(1 To inputCount)

    ' Prompt the user to enter each list item
    For i = 1 To inputCount
        listItem = InputBox("Enter list item " & i & ":", "Custom List")
        customList(i) = listItem
    Next i

    ' Add the custom list to Excel
    With Application
        .AddCustomList ListArray:=customList
    End With

    MsgBox "Custom list added successfully!", vbInformation
End Sub

Click the Save button and choose the Go back option on the Microsoft Excel message box.

You should now see the Save As dialog box. There, click on the Save as type drop-down list and choose the Excel Macro-Enabled Workbook (XLSM) option. Finally, click the Save button to save the VBA macro.

To run the macro, press the Alt + F8 keys to launch the Macro dialog box. There, select the CreateCustomList macro and hit the Run button.

You can now enter the total number of the list items, like 10 on the prompt.

Then, add the list item and hit Enter to get the next prompt for the list item. Don’t forget to remember the first item on the list. Otherwise, you can find the whole list in File > Options > Excel Options > Advanced > General.

How to Use Your Custom List to Sort Data

This is one of the best and most productive uses of Custom AutoFill Lists.

Often, you download software data that includes values for the items in your Custom List. For example, you’ve extracted sales performance data of the sales managers in your business.

Now, the whole data consists of numbers on 50 sales managers in 10 different locations of your business. You need to sort data for a specific group of sales managers in the New York location. Luckily, you created Custom Lists for 5 sales managers in all the locations separately.

In this scenario, here’s how you can sort using a Custom List:

Highlight the dataset and click Sort & Filter > Custom Sort.

Expand the Order menu by clicking the A to Z drop-down list. Select Custom List.

On the Custom List dialog, select your list and click OK twice.

Excel will automatically sort the dataset based on your list of sales managers in the New York office.

How to Delete a Custom List in Excel

To delete one or more Excel Custom AutoFill Lists from your PC, follow these steps:

Bring up the Custom List dialog box on your Excel worksheet. I’ve already explained the steps to do this earlier in this article.

Select the list you want to delete on the left-side menu. Click Delete on the right side.

On the Microsoft Excel dialog, click OK. Excel will delete the list.

Conclusions

Custom AutoFill list enables you to do more than just automatic filling of ranges. It also allows you to sort and filter data based on the custom order you’ve defined. Hence, you can use this feature to maintain consistency and organization in your Excel worksheets.

So, whenever you want to add custom AutoFill in your Excel, there is no more wondering “how to create a custom AutoFill list in Excel”. You can follow the methods mentioned in this article and implement them properly with the stepwise instructions.

Do you have any suggestions about creating a custom AutoFill list? Is there are other reasons why you use AutoFill in your Excel database? Share it with your fellow readers in the comment section.