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:
- 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.
- 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.
- As you drag, a ToolTip shows values to be filled.
- 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 2015 or 1Q-15 or 1Q.15.
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.
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 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.
- Right-click and drag the fill handle to access these options..
- Select both dates.
- 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:
- Enter the number 1 in a cell and select that cell.
- Right-click the fill handle. Drag down one cell. Drag back up. Release the mouse button.
- Choose Series"¦ from the bottom of the flyout menu. (Be careful, you want "Series"¦" from the bottom, not "Fill Series" from the top.
- In the Fill Series dialog, choose Columns. Enter a Stop Value of 1,000,000. Click OK.
- Fill 1 million cells easily.