Generally, most keyboard tricks and shortcuts work consistently in Excel, but there's one aspect involving the Ctrl key and the mouse that behaves the opposite depending upon whether a cell contains a value or a date.
In this article I'll describe the paradox and show an alternative with the right-mouse button that works consistently no matter what type of data you're working with. I'll also share a couple of keyboard shortcuts that can straighten things out should the technique blur.
To begin, let's enter the word Day1 into cell A1 of a blank worksheet. Select cell A1 and then drag the notch in the lower right-hand corner, known as the Fill Handle, down column A to cell A20. Notice that Excel creates a series that runs from Day1 through Day 20.
Next, enter the number 1 in cell B1 and then drag that down through cell B20. In this case cells B1 through B20 all contain the number 1.
The same action occurs if you double-click the Fill Handle if you have data in at least one adjacent column. Next, in cell C1 enter the number 1.
This time, hold down the Ctrl key as you drag down through cell C20. When you do so Excel creates a series from 1 through 20. Thus, dragging a value results in filling all cells with that same value, while holding down the Ctrl key creates a series of numbers.
Figure 1: Holding down the Ctrl key creates a series of numbers.
Now for the paradox. In cell D1 enter the date 1/1/2020 and then drag down through cell D20. Excel creates a series from 1/1/2020 through 1/20/2020, the same as if you were to double-click the Fill Handle.
Now, enter 1/1/2020 in cell E1 and drag down holding the Ctrl key. Notice that in this case holding down the Ctrl key causes Excel to fill each cell from E1 to E20 with 1/1/2020.
Fortunately, there's another way to fill cells and stay in control: drag with your right-mouse button instead of your left. As shown in Figure 2, when you release your mouse Excel will display the choices related to the type of data that you're dragging:
Copy Cells: This appears no matter what type of data that you're dragging. As you might expect, it takes data from the first cell and copies it down through your selection. Keyboard shortcut equivalents should muscle memory kick in, and if you unintentionally left-drag include Ctrl-D for copying down and Ctrl-R for copying to the right.
Fill Series: This appears when you're dragging values or dates. Thus, it emulates what happens when you hold down the Ctrl key while dragging a value, or when you drag a date without holding down the Ctrl key.
Fill Days: This appears when you're dragging dates and emulates what happens when you drag a date without holding the Ctrl key.
Fill Weekdays: This appears when you're dragging dates and enables you to omit Saturdays and Sundays from your series of dates.
Fill Months: This will create a series of dates one month apart. When the initial date is the 29th, 30th, or 31st of a month, Excel will use the last day of February but will otherwise use the day of the month from the initial cell.
Fill Years: This will create a series of dates one year apart.
Figure 2: Drag with the right-mouse button to select how you want the data to copy from a menu.
If you unintentionally left-drag and end up with results other than you expected, click the AutoFill Options icon that appears adjacent to the bottom of your selection. Depending upon context, you can make many of the same selections as they appear when you right-drag a selection.
This article is by no means an exhaustive discussion of the ways to create series and/or copy data in Excel, but instead is written to illustrate both as a paradox, as well as a handful of alternatives.
David H. Ringstrom, CPA, is an author and nationally recognized instructor who teaches scores of webinars each year. His Excel courses are based on over 25 years of consulting and teaching experience. His mantra is “Either you work Excel, or it works you.” David offers spreadsheet and database consulting services nationwide.