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

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:

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
   
   

4. 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.
   

5. Click the Add button and review your new custom list in the left “Custom lists” pane.
   
6. Now, when you type “Accounting” in one cell, and drag the AutoFill tool, the entire list will appear, in order, within the selected cells:
   

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:

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.

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:

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.

Related Posts

Polish Your Documents: Guide to Removing Hyperlinks in Word

Microsoft Outlook Tip: Set Calendar Dates Using Natural Language, Abbreviations

Efficient Math Operations in Excel without Formulas: The 'Paste Special' Trick