In this tutorial, I am going to show you 6 simple methods on how to copy the same date in Excel. You can use these methods even in large datasets to make copies of a single date value in other adjacent cells. Throughout this tutorial, you will also learn some important Excel tools and techniques which will be very useful in any Excel related task.
How to Copy Same Date in Excel: 6 Simple Methods
We have taken a concise dataset to explain the steps clearly. The dataset has approximately 7 rows and 2 columns. Initially, we are keeping all the cells with dates in Date format. For all the datasets, we have 2 unique columns which are Sales Person and Joining Date. Although we may vary the number of columns later on if that is needed.
1. Using Fill Handle
The Fill Handle in Excel gives us the option to extend numbers, dates, or texts to other adjacent cells. Let us see how to use this tool to copy the same date to other cells.
Steps:
- First, go to the lower-right corner of cell C5 and drag the + symbol down using the mouse.
- Now, click on the Auto Fill Options icon and select Copy Cells.
- As a result, this will copy the date value of cell C5 to all the cells below.
2. Utilizing Absolute Reference
In this method, we will be using the Absolute Reference formula to copy the same date in Excel. Follow the steps below to achieve this.
Steps:
- To begin with, navigate to cell C5 and insert the following formula:
=$C$5
- Finally, press Enter and copy this formula down to show the same date in the remaining cells.
Read More: How to Use IF Formula with Dates
3. Applying Paste Special
The Paste Special feature also gives us a quick and easy way to copy the same date value to adjacent cells in Excel. Below are the steps to do this.
Steps:
- To start with, copy cell C5 using CTRL+C.
- Next, select the cells from C6 to C10 and right-click.
- Here, select Paste Special and from there click on Paste Link.
- Immediately, this will create copies of the date in cell C5 in the other cells.
Read More: How to Add Time in Excel Automatically
4. Copy Same Date from Clipboard
A Clipboard is a place where Excel keeps track of all the values that we copy. Although it requires some manual work, we can still copy the same date value using this feature in Excel. Let us see how to do that.
Steps:
- Similarly, as before, copy cell C5 and click on the Clipboard icon in the top-left corner.
- Now, click on cell C6 and go to the Clipboard pane on the left.
- Here, click on the drop-down and select Paste.
- Finally, this will generate copies of the beginning date.
Read More: Making a List of Countries by Time Zone in Excel
5. Copying by DATE Function
The DATE function in Excel can take dynamic values as input and produce the respective date value. We can use this function to quickly copy the same date value to other cells. Let us see how to use this function.
Steps:
- First of all, go to cell C5 and type in the following formula:
=DATE(2022,1,20)
- Finally, press the Enter key and copy this formula down using Fill Handle.
Read More: How to Calculate Due Date with Formula in Excel
6. Applying VBA Code
If you are familiar with VBA in Excel, then you can copy the same date to other cells with just a few clicks. Follow the steps below to do this.
Steps:
- For this method, go to the Developer tab and select Visual Basic.
- Now, select Insert in the VBA window and click on Module.
- Next, type in the formula below in the new window:
Public Sub CopySameDate()
Range("C6:C10").Value = Range("C5")
End Sub
- Then, open the macro from the Developer tab by clicking on Macros.
- Now, in the Macro window, select the CopySameDate macro and click Run.
- As a result, the VBA code will create copies of the date in cell C5.
Read More:Â How to Create World Time Zone Clock in Excel
How to Insert Dates In Excel Automatically
To insert dates automatically in Excel, we can again use the simple Fill Handle tool without typing any formula. Let us see the detailed steps.
Steps:
- First, click on cell C5 and double-click on the + icon in the lower-right corner.
- Consequently, this will automatically fill the cells below with increasing dates.
Read More: How to Use Excel Date Shortcut
How to Auto Fill Date In Excel Using Shortcut
In Excel, there are many shortcuts that can be applied from the keyboard to quickly achieve some result. Let us see how to auto-fill the date in Excel using a simple shortcut.
Steps:
- For this, select all the cells from C5 to C10 and press Ctrl+D.
- As a result, this shortcut will fill the dates in those cells automatically.
Download Practice Workbook
You can download the practice workbook from here.
Conclusion
I hope that you were able to apply the methods that I showed in this tutorial on how to copy the same date in Excel. As you can see, there are quite a few ways to achieve this. So wisely choose the method that suits your situation best. If you get stuck in any of the steps, I recommend going through them a few times to clear up any confusion. If you have any queries, please let me know in the comments.
Related Articles
<< Go Back to Autofill Dates | Excel Autofill | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!