Bonus Tip: Fill Jan, Feb, ..., Dec, Total

A person in one of my seminars wanted to have Jan fill into 13 values: Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, Dec, Total.

While you can edit any custom list that you create, you cannot edit the first four lists in the Custom Lists dialog.

However, if you use the preceding tip to add a new custom list with the 13 values, that list wins. If two custom lists have the value Jan, the lowest one in the dialog box is the one that is used.

If you fiscal year ends March 31, you could set up a list with Apr, May, Jun, ..., Jan, Feb, Mar, Total.

3 Custom Lists are shown. One with East, Central, West. One with a list of the 50 states. One with 12 month abbreviations, followed by the word Total.

Bonus Tip: Fill 1 to 100,000 in a Flash

What if you have so many items that you can't drag the Fill Handle? Follow these steps:

1. Type the number 1 in a cell.

2. Select that cell.

3. On the Home tab, toward the right, in the Editing group, open the Fill dropdown and choose Series.

4. Select Columns.

5. Enter a Stop Value of 100000.

6. Click OK.

The Series dialog says Series in Columns, Type Linear, Step Value 1, Stop Value 100000. Click OK.

What if you have to fill 100,000 cells of bagel flavors?

1. Type the first bagel flavor in A1.

2. Select A1.

3. Type A100000 in the Name box and press Shift+Enter to select from the current cell to A100000.

4. Home, Fill, Series… and click AutoFill in the Type box. Click OK to fill from the custom list.

Thanks to the person in row 6 at the Meonske Conference in Kent, Ohio, for suggesting this feature.