Creating custom lists for AutoFill in Excel 2007
In addition to varying the increment in a series created with AutoFill, you can also create your own custom series. For example, say your company has offices in the following locations and you get tired of typing out the sequence in each new spreadsheet that requires them:
- New York
- Chicago
- Atlanta
- Seattle
- San Francisco
- San Diego
After creating a custom list with these locations, you can enter the entire sequence of cities simply by entering New York in the first cell and then dragging the Fill handle to the blank cells where the rest of the companies should appear.
To create this kind of custom series, follow these steps:
1) Click Office Button → Excel Options → Popular or press Alt+FI and then click the Edit Custom Lists button in the Top Options for Working with Excel section to open the Custom Lists dialog box.
If you've already gone to the time and trouble of typing the custom list in a range of cells, go to Step 2. If you haven't yet typed the series in an open worksheet, go to Step 5 instead.
2) Click inside the Import List from Cells text box and click the Minimize Dialog Box button (the one with the picture of the tiny grid to the right of the Import List from Cells text box) so that you can see your list and drag through the range of cells to select them.
3) After selecting the cells in the worksheet, click the Maximize Dialog box button.
This button automatically replaces the Minimize Dialog box button to the right of the Import List from Cells text box.
4) Then click the Import button to copy this list into the List Entries list box.
Skip to Step 7.
5) Click inside the List Entries list box and then type each entry (in the desired order), being sure to press Enter after typing each one.
When all the entries in the custom list appear in the List Entries list box in the order you want them, proceed to Step 7.
6) Click the Add button to add the list of entries to the Custom lists box.
Finish creating all the custom lists you need, using the preceding steps. When you are done, move on to Step 7.
7) Click OK twice, the first time to close the Custom Lists dialog box and the second to close the Excel Options dialog box and return to the current worksheet in the active workbook.
After adding a custom list to Excel, from then on you need only enter the first entry in a cell and then use the fill handle to extend it to the cells below or to the right.
Related Articles