How to Continue Weekly Spreadsheet Series in Excel 2016
Bottom line: Learn how to use the Fill Handle in Excel to copy down dates and fill a series of numbers.
Skill level: Beginner
What's the Fill Handle?
The Fill Handle is a great tool that allows us to quickly copy values or formulas down an entire column. We can hover our mouse over the bottom right corner of the selected cell/range until the cursor turns into a + symbol.
At this point we have two options:
- Drag it up/down or right/left to copy the cell contents to the new cells. Left-click and hold to drag the fill handle.
- Double left-click the mouse to fill the formula or value down an entire column. This fill down only works when there are cells in the column directly to the left of the fill handle.
Note: Double-clicking the fill handle will only extend down to the last non-blank cell in the current region. So if your column to the left contains a lot of blanks, the Fill Handle might not be the best option for copying down.
The fill handle has different behavior depending on the contents of the cell.
- If the cell contains a value or a formula, then the fill handle will Copy down the value.
- If the cell contains a date, then the fill handle will fill the series and increment the date by one for each cell.
How to Copy Dates Instead of Fill Series
In last week's blog post on Ctrl+Enter, Lisa left a great tip about using Ctrl+Enter to copy down dates because she did not want the fill handle to increment them.
Let's say we have entered today's date in cell B2 (keyboard shortcut Ctrl+;), and we want to copy the date down to all the cells in column B of the current region (used range).
If we double-click the fill handle, the fill series option will be used and increment each date by one day.
But, we wanted to COPY the date down, not increment it! Here are few ways to go about this using the fill handle to copy down.
Ctrl+D to Copy Down Dates
After double-clicking the fill handle, press Ctrl+D to copy the date down.
Ctrl+D is the keyboard shortcut to copy down the value/formula in the selected range. The value in the active cell (usually the first cell in the selected range) is copied down with Ctrl+D.
Bonus tip: Ctrl+R is the shortcut to copy across. I remember this by D for down, R for row.
Bonus tip #2: Ctrl+D can also be used to duplicate shapes, charts, and slicers. I shared this tip on my video on how to create the Progress Doughnut Chart.
Auto Fill Options Menu to Copy Cells
Another option is to use the mouse. After double-clicking the fill handle, the Auto Fill Options button appears below the last selected cell.
Clicking the button will open the Auto Fill Options menu. In the example with the date value, the Fill Series radio button will be selected. Click the Copy Cells radio button instead. The date value will then be copied down.
Bonus tip: The Auto Fill Options Menu can be opened with the keyboard shortcut: Alt+Menu Key. Then press the underlined letter of the option in the Auto Fill Options menu. So, the keyboard shortcut to change the Auto Fill option to Copy Cells is Alt+Menu Key, C.
Ctrl+D is faster than this, but it's good to know the keyboard shortcut exists. If your Menu Key is feeling neglected, then check out my article on keyboard shortcuts for the Menu key. Also checkout my article on the best keyboards for Excel keyboard shortcuts.
How to Create a Sequential List of Numbers
Another common use of the fill handle is to create a sequential list of numbers. 1,2,3,4,…
If we enter a 1 in a cell and then double-click the fill handle, the Copy Cells option is used in the Auto Fill Options, instead of Fill Series. Again, this is the opposite of what we want to happen in this case.
We can use the Auto Fill Options menu to change the fill from Copy Cells to Fill Series. The keyboard shortcut is: Alt+Menu Key, S.
We can also use the Fill Series menu that is on the Home tab of the ribbon. Keyboard shortcut is Alt, h, f, i, s. The old school Excel 2003 shortcut is a little shorter: Alt, e, i, s. That still works in modern versions of Excel.
Ctrl Key + Auto Fill
We can also use the Ctrl key to help us with filling a series (sequence) of numbers.
Here are the steps to fill the series of numbers:
- Enter a 1 in the first cell and press Ctrl+Enter to keep the cell selected.
- Press & hold the Ctrl key.
- Use the fill handle to drag the number down one cell. A 2 will be placed in the next cell.
- Double-click the fill handle to perform the fill series on the column.
When we enter a 1 in the first cell and a 2 in the second cell, then double-click the fill handle, Excel recognizes this as a series of numbers, and completes the fill as a series.
Holding the Ctrl key while dragging the fill handle will do the opposite of whatever the fill handle does. Holding the Ctrl key will copy dates and fill series on numbers. One of the many quirks of Excel… 🙂
We can also use the fill handle to copy Table formulas across in the Totals Row of a Table to keep the references relative.
How Do You Fill a Sequence of Numbers in Excel?
There are many other techniques for filling a series of numbers in a column. How do you perform this task? Please leave a comment below with your preferred method, or any questions. Thank you!
wisewouldarresplet.blogspot.com
Source: https://www.excelcampus.com/keyboard-shortcuts/fill-handle-copy-dates-numbers/