How to Create a New Custom List for AutoFill Series in Excel

Excel is an electronic spreadsheet application available in Microsoft Office Package Software that allows users to do simple as well as complex mathematical, trigonometric, financial, and other various kinds of calculations in an easy, fast, and flexible way.

Excel has so many built-in features and functions that you can use in your Workbook. AutoFill is one of the most popular features of Excel that helps you enter a series of data automatically.

You can use AutoFill Series if you are entering a series of natural numbers, days of the week, name of months, and any other predictable series.

For example; if you are entering the name of months from January to December, adding all the names of months manually can take a lot of time. But if you specify the all the name of months once, you can enter the name of all months automatically by just entering “January” in one cell and then applying the AutoFill series. Applying AutoFill will then produce February, March, April and so on.

By default AutoFill supports entering numbers. To autofill the numbers from 1 to 100, first enter 1 in a cell and press the “CTRL” key then drag the fill handle. It will produce 2, 3, 4, 5, 6 and so on.

So, if you want to create a custom AutoFill series in Excel then follow every step that I have mentioned in this article.

Step 1: Open Excel Options Dialog Box

By clicking on the Office Icon Button or File Menu will open a layout with various options like save, save as, print, open, new and so on.

Inside the File menu, there is another option called Excel Options.

If you have installed MS Office 2007 then you open the Excel Options dialog box clicking on the “Excel Options” button which is located near the “Exit Excel” button.

To quickly open the Excel Options dialog box you can use the shortcut key combination of “ALT + F + I”.

Step 2: Edit Custom Lists

Completing the previous step will open the Excel Options dialog box. In the dialog box, by default the Popular tab is active; where you can change the most popular options in Excel.

There are 3 block options on the right side of Excel options dialog box with activating Popular tab. On the top block option, there is an Edit Custom Lists button.

Clicking on the Edit Custom Lists button will open a small dialog box to let you create your own custom list for use in fill sequences.

In the custom lists dialog box, you can create your own custom fill series entering the value in List entries as shown in screenshot below.

Once you add the value in the list entries, click the “Add” button and press “OK” to complete the task.

Step 3: AutoFill the Series in Worksheet

Now let’s see whether our new custom list works or not if we drag the fill handle. In a cell, we first enter the Mercury (you can enter your own value that you have added in list entries).

When we drag the fill handle from a cell holding Mercury (up to the 8 cells), the AutoFill series feature of Excel will automatically produce Venus, Earth, Mars,…, and so on.

In your case, you have to drag the fill handle up to the ‘n’ number of cell locations depending on the length of the custom list.