I want to create a series of consecutive dates, but I need two rows for each date. For instance, A1:A2 would both equal 2/2/2013, A3:A4 would then equal 2/3/2013, A5:A6 would equal 2/4/2013, and so on. I’ve tried using the fill handle any number of ways, but it doesn’t work. Is there an easy way to do this? — Jenny
Jenny, you’re right, the fill handle, alone, can’t handle this task, but you can use the technique I describe at http://www.techrepublic.com/blog/msoffice/quickly-fill-blank-cells-in-excel/3014. Here’s a step-by-step review that should help:
- Enter the first two dates (or the necessary dates to create the sequence pattern) skipping a row in-between each. To illustrate, you might enter 2/2/2013 in A1 and 2/3/2013 in A3.
Select the range of dates, including the blank cell following the last date. In this case, that would be A1:A4.
Use the fill handle to create a series of dates that increment by one with an empty cell between each. In this case, you’d use the fill handle to fill A5:A12. (End with an even-numbered row to avoid an extra selection step).
- With the range still selected, press [F5] to display the Go To dialog.
- Click Special.
Click OK to return to the sheet. Cell A2 is the current (or anchor) cell, but the remaining blank cells in the selected range are also selected.
- In cell A2 (already selected), enter =A1.
Press [Ctrl]+[Enter] to enter the relative formulas into each of the selected blank cells. If you don’t press [Ctrl], this technique will not work.
Once you have your series, you can quickly eliminate the formulas (if necessary), as follows:
- Select the series, A1:A12.
- Right-click the border and drag the range to the next column, as if you were going to move the range. Be sure to right-click!
Don’t actually drop the range into the next column, move it back instead. I know that sounds odd, but doing so will display the shortcut menu shown below.
- Choose Copy Here As Values Only.
There are other ways to replace formulas with literal values, but this is one of my favorites.