Excel 2020: Fill Jan, Feb, ..., Dec, Total

January 22, 2020 - by Bill Jelen

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 tip in The Fill Handle Does Know 1, 2, 3… 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.

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.

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.

Title Photo: Curtis MacNewton at Unsplash.com

This article is an excerpt from MrExcel 2020 - Seeing Excel Clearly.