See all How-To Articles

How to AutoFill Custom / Alphabetic Character Lists in Excel

This tutorial demonstrates how to autofill the alphabet and other custom lists in Excel.

 

custom list departments excel 1

 

Excel is able to recognize patterns and autofill a list based on a single entry. This is often used for days, months, numbers, etc. You can also create a custom list with your own data, and Excel will use it to recognize patterns for autofill.

Default Custom Lists

View Custom Lists

  1. In the Ribbon, go to the File tab and click Options.

 

excel options custom lists 1

 

  1. In the Options pop-up screen, go to the Advanced tab, scroll down, and under the General group, choose Edit Custom Lists.

excel options custom lists 2a

 

This brings up a new window displaying and editing custom lists in Excel. By default, Excel allows you to autofill months and days, both with full and short names (January and Jan; Monday and Mon).

The left side of the Custom Lists window shows these four default lists. If you click on the last list, you’ll see all the months in the List entries box to the right. That’s the source for autofilling a list of months in Excel.

 

excel options custom lists months 1

 

Use a Default Custom List

To autofill with a default custom list (i.e., months), follow these steps:

  1. Enter January in cell B1 and position the cursor in the bottom-right corner of B1 to get the fill handle.

 

month custom list january 1a

 

  1. Now drag the fill handle down to Row 12 to get all 12 months – in sequential order – in Column B.

 

month custom list 1

 

Create a Custom List and Autofill

Type in a Custom List Directly

In the Custom Lists screen, you can also create a list with the letters of the alphabet.

  1. In the Custom lists box on the left, click on NEW LIST.
  2. In the List entries box on the right, enter all list elements (i.e., A–Z). Separate each entry by comma, space, or line break (ENTER).
  3. Finally, click Add in order to add the new list.

 

add custom list directly 1a

 

When you do this, a new list of letters is added to Custom lists, as you can see in the picture below.

 

add custom list directly 2a

 

Autofill the Alphabet

Now Excel is able to recognize an alphabetic pattern, so you can autofill the letters A–Z.

  1. Enter the letter A in cell B1 and position the cursor in the bottom-right corner of B1 to get the fill handle.

 

letters custom list 1

 

  1. Drag the fill handle down to Row 26 to get all 26 letters of the alphabet – in sequential order – in Column B.

 

letters custom list 2a

 

Import a Custom List

Another option to add a custom list is to reference cells in a worksheet. Say you have departments in your company in cells B2:B8, as pictured below, and want to make a custom list from that range.

 

custom list departments 1

 

  1. First, go to the Custom Lists window, as shown above (File > Options > Advanced Options, and choose Edit Custom Lists).
  2. Select (1) NEW LIST under Custom lists, then click on the (2) icon near the Import button to choose a range of cells.

 

custom list cells import 1

 

  1. Select the range containing the new list items (B2:B8), and press ENTER.

 

custom list cells import 2a

 

  1. This brings you back to the Custom Lists window. Click the Import button.

 

custom list cells import 3a

 

As shown below, the list of departments is now stored as a custom list.

 

custom list cells import 4a

 

Autofill a Custom Series

Now you can autofill the departments list based on one entry.

  1. Enter Marketing in cell B1 and position the cursor in the bottom-right corner of B1 to get the fill handle.

 

departments custom list marketing 1

 

  1. Drag the fill handle down to Row 8 to get all eight departments from your new list – in sequential order – in Column B.

 

departments custom list autofill 1