Microassist

Software Tips

Home » Software Tips » Create a Custom List to Use with Excel’s AutoFill Tool

Create a Custom List to Use with Excel’s AutoFill Tool

By   |    |    |  3

Day of the Week? Month of the Year? A Custom Series? Use Excel’s AutoFill Tool to Populate Cells Quickly

AutoFill Magic

You can use Excel’s AutoFill Tool for any number of known series. For days of the week, for instance, simply start with a day, then click and drag the dark square (the fill handle) at the bottom right of the bordered cells. In our example, we’ll put “Sunday” in cell B3 and drag the Excel fill handle to down to B9. After releasing your selection, all the days of the week are listed in the column, Sunday to Saturday, from B2 to B9:

Three screen grabs show steps for using AutoFill to add days of the week as described in the paragraph above.

This technique can be used for any common series: Months of the year, fiscal quarters (Q1, Q2, etc.), and more.

Customizing a List for Excel’s AutoFill Tool

But what if you have a custom list? Just as you can use AutoFill in Excel 2013 to fill out a series that increments from one unit, you can also get set this feature to fill out custom lists of your own design. For example, you may need to enter a series of departments into your spreadsheet on a regular basis. Instead of manually typing the departments each time you need them, create a custom list and Excel’s AutoFill tool will help you with entering the data. A custom list can only contain text or text with numerical values.

There are two ways to create a custom list in Excel: One way is to open the Custom Lists dialog box, then manually type the sequence of entries in the List Entries box located on the right side of that dialog box. The second way is to enter the custom series items in successive cells of a worksheet, then open the Custom Lists dialog box to import the cell values. We’ll cover both ways.

Creating a New, Manually Typed AutoFill Custom List

For this example, we will add a list of departments: Accounting, Sales, Marketing, HR, Training, and Development.

Follow the steps below to create a new custom list in Excel:

  1. Click on File and select Options
  2. Click on Advanced then scroll down to General section
  3. Click the Edit Custom Lists
    Screen grab of menus highlighting the option path to editing custom lists
  1. Click inside the List Entries list box and add each entry in the order you want them to display, press enter after each list item.
    Excel Custom List window with "New List" selected in the left "Custom Lists" pane, and "Accounting, Sales, Marketing, HR, Training, and Development" listed in the right "List Entries" pane. Default lists in the left "Custom Lists" pane include abbreviated days of the week (Sun, Mon, Tue) and months (Jan, Feb, Mar), as well as the fully spelled out versions of each.
  1. Click the Add button and review your new custom list in the left “Custom lists” pane.
    Custom Lists window with the new list highlighted at the bottom of the default custom lists.
  2. Now, when you type “Accounting” in one cell, and drag the AutoFill tool, the entire list will appear, in order, within the selected cells:
    Animation showing how Excel's AutoFill tool works. In this case, the animation shows typing "Accounting" in one cell, then dragging the fill handle downward to populate additional cells with the remaining list values: Sales, Marketing, HR, Training, Development.

Creating a New AutoFill Custom List Using the Import Option

If your data is already in the workbook, it will be more efficient to grab your data from the cells you’ve already populated. For instance, let’s say you’ve already created a report for your departments, but you know you’ll need to do this regularly. Select the list items, then open the Custom Lists dialog window as directed in Steps 1 through 3, above. The cell range will appear in the “Import list from cells:” field:

The Custom Lists dialog window showing $B$2:$B$7 in the Import field.

Click the “Import” button to add the information in that range as a new list. The new list will appear as a new item in the “Custom lists” pane and the values will appear in the “List Entries” pane on the right.

Custom List dialog window showing new custom list after using import feature.

Using Default and New Custom Lists to Auto Populate Cells in Excel

You’ll notice that Microsoft has already defined several custom lists, such as the days of the week and months of the year, abbreviated and spelled out. These values can be used to populate cells across a row or in a column.

After you add your custom list, using either method, you can use it in the same way as the ones defined by Microsoft. Just type one of the values from the list, drag the mouse cursor and Excel will complete the rest. Using the autofill makes for easy data entry when working with a list of data that you do not want to copy and paste repetitively.

Examples of lists:

A top example shows the days of the week, starting in cell B2 with Sunday, and ending at H2 with Saturday. The second example has Abilines in cell A2, followed by Austin, Corpus Christi, Dallas, Forth Worth, Houston, and Irving, with Irving ending in G2. Cities are listed alphabetically.

Microassist’s built-in lists cannot be deleted from Excel. However, any list that you create can be deleted. With the ability to create your own custom lists, your entries are unlimited.

Catch Our Next Microsoft Excel Class!

This tip scratches the surface of all the great features within Microsoft Excel. To take a deeper dive and ramp up your Excel productivity, check out upcoming classes on our Course Schedule page.

If you’d like a custom course delivered on your campus, we do that, too! Just contact our Training Team for details.

Tips Come to You!

To get a new tip every month, subscribe to our Training News newsletter. You’ll receive skill-building resources for yourself, your team, and your organization.


Subscribe to Monthly Training Updates

Receive monthly productivity and training insights, software tips, and notices of upcoming classes!

Name

MENU