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.