Combine the fill handle and Go To feature to create a repeating series

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:

  1. 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.
  2. Select the range of dates, including the blank cell following the last date. In this case, that would be A1:A4.

  3. 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).

  4. With the range still selected, press [F5] to display the Go To dialog.
  5. Click Special.
  6. Select Blanks.

  7. 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.

  8. In cell A2 (already selected), enter =A1.
  9. 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:

  1. Select the series, A1:A12.
  2. 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!
  3. 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.

  4. Choose Copy Here As Values Only.

There are other ways to replace formulas with literal values, but this is one of my favorites.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s