Last month we talked about how to use Excel’s Autofill feature to automatically fill in a series of words (like days of the week) or numbers. In order for the feature to work Excel needs to be able to recognize the series. So this month I want to show you how you can create your own custom series that can be used with Autofill.
If you already have the list you want to use typed on a worksheet, highlight the list. Next, or if you are going to enter the list manually, go to File > Options. Select Advanced on the left side of the Options window and scroll down the the end of the list. Click the Edit Custom Lists button which you’ll find the General Section. If you highlighted a list the cell reference will already be entered in the Import List From field. Just click the Import button. If you didn’t have a list highlighted, NEW LIST should be selected in the Custom Lists section and you can click in the List entries section and start typing your list. Just press Enter to go to the next line so you can enter the another item. When you are done click the Add button to start building a new list or click OK to close the window.
Now you can fill your list by typing the first item from the list in any cell and dragging the autofill handle or by clicking the Fill button and choosing Series and selecting Autofill.
Since you use the list by typing the first entry, you may want to use a heading for the first list entry. For example, if you want to be able to autofill a list of sales reps, the first rep may have a long name that is inconvenient to type, or if the list changes frequently it may be a challenge to remember what the first list entry is. In a case like that, it may be better to make Sales Reps the first entry instead of a name. The only drawback to using a heading as the first entry is that if you want this list to repeat, the the heading will be repeated along with the names.
