MrExcel.com - Your One Stop for Excel Tips & Solutions

Enter a Series of Months, Days, or More by Using the Fill Handle

September 23, 2021 - by

Enter a Series of Months, Days, or More by Using the Fill Handle

Problem: I need to create a new worksheet. My first task is to enter the 12 month names across row 1. Is there a faster way than typing them all?

Strategy: You type the first value and drag that cell’s fill handle to the right or down. Follow these steps:

  • 1. Type January in cell B1. If you now press the Enter key, Excel will normally move the cell pointer to B2. You can press Enter and then press the Up Arrow key to move back to B1, or you can simply press Ctrl+Enter to accept the cell value and stay in the current cell.

  • 2. The square dot in the lower right corner of the cell is the fill handle. Click it and drag right or down. As you drag, a ToolTip will show you the value that will be entered in each cell.

Type January in B1. With B1 selected, drag the fill handle to the right two columns. The tooltip will show you have dragged as far as March.
Figure 87. As you drag, a ToolTip shows values to be filled.

  • 3. When you release the mouse button, Excel will fill the series with month names.

Additional Details: Excel can extend many other built-in series in addition to month names:

  • Jan will extend to Feb, Mar, and so on.
  • MON will extend to TUE, WED, and so on.
  • Q1 will extend to Q2, Q3, Q4, Q1. (Also Qtr 1 or Quarter 1)
  • Room 10 will extend to Room 11, Room 12, and so on.
  • 1st period will extend to 2nd period, 3rd period, and so on.
  • Today’s date (press Ctrl+;) will extend to tomorrow’s date.
  • For quarters and years, use 1Q 2018 or 1Q-18 or 1Q.18.

Gotcha: Excel can extend many built-in series, but can it count 1, 2, 3, and so on? If you enter 1 in cell B1 and drag the fill handle down, what do you think you will get? 1, 2, 3. What will you actually get? 1, 1, 1.

Many people tell me to enter 1 in B1, 2 in B2, select B1:B2 and drag the fill handle. While this works, there is a faster way: You can enter 1 in B1 and then hold down the Ctrl key while you drag the fill handle. Excel will fill with 1, 2, 3. Alternatively, select the 1 and the blank cell next to the 1. Drag down. Excel will fill 1, 2, 3.

The Ctrl key can be used to copy instead of fill. Select a date or text. To copy without incrementing, drag the fill handle while holding down Ctrl.

Additional Details: If you forget to hold down Ctrl, you can open the Auto Fill Options dropdown that appears at the end of the range. You can select Fill Series to change the 1, 1, 1, 1 to 1, 2, 3, 4. You can toggle Ctrl while dragging - keep your eye on the + symbol next to the mouse pointer.

Gotcha: The Fill Options icon can be difficult to dismiss. This is particularly annoying if it is covering up data. The Esc key will not make it go away. One fast way to dismiss the icon is to resize a column on the worksheet.

If you need to fill odd numbers, you can enter 1 in B1 and 3 in B2. Select B1:B2 and drag the fill handle.

There are other fill possibilities as well. One cool option is Fill Weekdays. You enter a starting date in a cell, place the cell pointer in that cell, right-click, and drag the fill handle down several cells. A ToolTip will indicate that you are filling the series with daily dates. When you release the mouse button, you will have several options. Choose Fill Weekdays to fill in only Monday through Friday dates.

To fill weekends, start with a Saturday or Sunday in A1. Use a formula of =WORKDAY.INTL(A1,1, "1111100") in A2. Format A2 as a date and then copy down.

To fill the 15th and last of each month, select both dates, right-click the fill handle and drag. When you release the mouse, choose Fill Months.

Another example of filling dates. Right-drag the fill handle from a date and choose Fill Weekdays. Excel only fills Monday through Friday dates.
Figure 88. Right-click and drag the fill handle to access these options..
Two dates appear in A1 and A2. 1/15/2024 is in A1. 1/31/2024 is in A2. Both cells are selected and you are about to right-click the fill handle and drag the fill handle down. This will fill the 15th and last of each month.
Figure 89. Select both dates.
After you let go of the right mouse button, choose Fill Months.
Figure 90. Right-drag, fill months.

Additional Details: The fill handle is a shortcut to default settings you can also get by selecting Home, Fill, Series. You can enter a value in a cell, select that cell, and choose Home, Fill, Series to display a dialog where you can specify any type of series.

Say that you want to fill the numbers from 1 to 1,000,000. Try this:

  • 1. Enter the number 1 in a cell and select that cell.

  • 2. Right-click the fill handle. Drag down one cell. Drag back up. Release the mouse button.

  • 3. Choose Series… from the bottom of the flyout menu. (Be careful, you want “Series…” from the bottom, not “Fill Series” from the top.

  • 4. In the Fill Series dialog, choose Columns. Enter a Stop Value of 1,000,000. Click OK.

Type the number 1 in J1. In the Series dialog, change from Rows to Columns. Leave the type as Linear. Leave the Step Value as 1. Change the Stop value to 1 million. When you click OK, Excel will fill a million sequential numbers.
Figure 91. Fill 1 million cells easily.

This article is an excerpt from Power Excel With MrExcel

Title photo by Behnam Norouzi on Unsplash