6 Ways to Create a Custom AutoFill List in Microsoft Excel

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

Custom list items
Custom list items

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.

Options navigation item
Options navigation item

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

Edit custom lists
Edit custom lists

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.

The import button
The import button

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.

Custom list down arrow
Custom list down arrow

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.

Importing custom list
Importing custom list

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.

Using the AutoFill handle
Using the AutoFill handle

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:

Custom Sort
Custom Sort

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.

Custom list drop down
Custom list drop-down

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.

Excel preferences on Mac
Excel preferences on Mac

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

Custom lists on Mac
Custom lists on Mac

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:

Using a TEXT function
Using a TEXT function

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")
Using the fill handle
Using the fill handle

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.

View Code in Worksheet
View Code in 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.

VBA Script and Run button
VBA Script and Run button

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.

Creating custom list vba script
Creating a custom list VBA script

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

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

Save as type
Save as type

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.

Running a VBA macro
Running a 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.

Numbers of items in custom list
Number of items in the custom list

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

Enter list item 1
Enter list item 1

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:

Custom sort of sales managers
Custom sort of sales managers

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

Custom sort custom list
Custom sort custom list

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

Custom list
Custom list

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

Sorted using custom list
Sorted using a custom list

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.

Delete custom list
Delete custom list

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

Confirmation dialog
Confirmation dialog

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.

About the Author

Bipasha Nath

Bipasha Nath

Bipasha is a technical content writer with 10+ years of experience in the technology industry. She previously worked in a SaaS software development company focused on SharePoint, OneDrive, Word, Excel, Word solutions.

Subscribe

Subscribe for awesome Microsoft Excel videos 😃

John MacDougall

I’m John, and my goal is to help you Excel!

You’ll find a ton of awesome tips, tricks, tutorials, and templates here to help you save time and effort in your work.

Related Posts

Comments

0 Comments

Submit a Comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Get the Latest Microsoft Excel Tips

Follow Us

Follow us to stay up to date with the latest in Microsoft Excel!

Subscribe for awesome Microsoft Excel videos 😃