Copying a Date Down a Long List in Excel

Two techniques make it quick and easy

There are a number of mouse and keyboard shortcuts for copying. But there is one type of copy that can be frustrating. Copying dates can be challenging because, in general, Excel wants to increment them, not copy them. There is a simple technique to instruct Excel to copy a date.

Our Goal

We want to enter a date in column D and copy it down the rest of the data set in the screen shot below.

The data in the image goes down to row 61. This technique will work for thousands of rows. It assumes there are no blank rows in the data. It takes a lot longer to explain than it does to do it.

Control Key Entry

The Ctrl key allows you enter the same value in more than one cell. In our case we are going to select the range D2:D3 before we enter the date.

Once the cells are selected, we type the date but instead of pressing Enter we are going to press Ctrl + Enter. This will place the date in both cells – see below.

Fill Handle Technique

After pressing Ctrl + Enter the range D2:D3 should still be selected. Point to the Fill Handle (bottom right corner of the range) and double click it – job done.

Because the date is repeated in the two selected cells Excel will copy rather than increment when you double click the Fill Handle.

Blank Rows

If you have blank rows within the data range you want to copy to then that’s a little harder – I will cover that in a future blog post.

Please note: I reserve the right to delete comments that are offensive or off-topic.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

2 thoughts on “Copying a Date Down a Long List in Excel

  1. Hi Neale,
    What if you want to enter a date (say, in Column D) and copy it to a certain cell in the same column?
    let’s say that you want to fill cells D1:D200 with the date: 01/01/2020.

    consider the following solution:
    1) select cell D1
    2) Press F5 (or Ctrl+G)
    3) Type D200 and press OK
    4) Press Ctrl+Shift + ↑
    5) Press F2 and type 01/01/2020
    6) Press CTRL+ENTER

    voila, cells D1:D200 now contain 01/01/2020

    This method has at least 3 advantages:
    1) You don’t need to worry about blank rows in the adjacent column
    2) You don’t even need an adjacent column. the whole process can take place even
    if the worksheet is completely empty.
    3) The mouse is not needed here…

    Thanks

    P.S.
    I look forward to hearing your opinion

    Best Regards,
    Meni Porat

    • Hi Meni
      You can omit F5
      Select cell D1
      Type D200 in the Name Box and then hold the Shift key down and press Enter
      Type the date and press Ctrl + Enter – done

      You can also just type D1:D200 in the Name Box to select that range

      Thanks for your comments

      Regards
      Neale