Software

How to create an efficient AutoFill series in Excel


A custom AutoFill series is a list of values you enter frequently. This list might consist of employee names, department names, and so on. The problem is that you enter them over and over. You can save time by creating an AutoFill series in Excel 2003 as follows:

  1. Enter the values and then select the list.
  2. Choose Options from the Tools menu.
  3. Click the Custom Lists tab.
  4. Click Import.
  5. Click OK to return to the sheet.
  6. Select a blank cell, enter the first item in the list and then expand the fill handle to complete the list.

Creating a list in Office 2007 takes a few extra clicks:

  1. Enter the values and then select the list.
  2. Click the Microsoft Office button
  3. Click Excel Options (at the bottom right).
  4. Click Popular.
  5. In the Top Options For Working With Excel section, click Edit Custom Lists.
  6. Click Import.
  7. Click OK twice.
  8. Select a blank cell, enter the first item in the list, and then expand the fill handle to complete the list.

In either version, the process is simple enough until the first item in your list is long or unstable. For instance, your list might start with "Office of Correspondence with Friends of the Missing Men of the United States Army." Now, you really don't want to have to type that just to get the rest of your list! Or, suppose the employee that heads your list quits. Your only option is to re-create the list.

You can avoid these potential pitfalls by heading up the list with a generic and short title, such as "Employees," and using that heading as the first item. Let's work through a quick example using Excel 2003:

  1. In cell A1, enter Employees.
  2. In cells A2..A4 enter a few names.
  3. Select cells A1..A4.
  4. Choose Options from the Tools menu, click the Custom Lists tab, click Import, and then OK.
  5. In cell C1, type Employees.
  6. Grab the fill handle and extend the selection to cell C4.

november2007blog8fig1.jpg

This method has three benefits:

  • You won't have to re-create the list if the first item changes.
  • A short header reduces the number of keystrokes you must enter.
  • You won't have to remember an obscure value or exactly how you entered it (last name, first name format, and so on).

Don't use this time-saving method on a repeating series because Excel will include the heading text in the list.

About

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

2 comments
LocoLobo
LocoLobo

I've done pick lists and custom lists before but didn't know there was a fill series option.

mccrea
mccrea

I have a list that I use in a Listbox, how can I make it so that when I start typing, it auto completes the rest. Is this possible?