MrExcel Publishing
Your One Stop for Excel Tips & Solutions

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


January 22, 2020 - by Bill Jelen

Excel Fill Jan, Feb, ..., Dec, Total. Photo Credit: Curtis MacNewton at Unsplash.com

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.

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.

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.

Title Photo: Curtis MacNewton at Unsplash.com


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


Bill Jelen is the author / co-author of
MrExcel 2020 - Seeing Excel Clearly

This is a 4th edition of MrExcel LX. Updates for 2020 include: Ask a question about your data, XLOOKUP, Power Query's Data Profiling tools, How Geography Data Types decide which Madison, A SEQUENCE example for descending 52 weeks, Exchange Rates support in Stock Data Types, How to collapse the Search box, How to leave effective feedback for Microsoft, How to post your worksheet to the MrExcel Board using XL2BB.