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.
Download Practice Workbook
You can download the practice workbook from here.
6 Simple Methods to Copy Same Date in Excel
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.
Read More: Formula to Copy and Paste Values in Excel (5 Examples)
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 Copy a Cell in Excel Using Formula(7 Methods)
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: VBA Paste Special to Copy Values and Formats in Excel (9 Examples)
Similar Readings
- Automatically Update One Worksheet from Another Sheet in Excel
- How to Copy Only Visible Cells in Excel (4 Quick Ways)
- Copy a Worksheet in Excel (5 Smart Ways)
- How to Copy and Paste Multiple Cells in Excel (8 Quick Methods)
- Exchange (Copy, Import, Export) Data Between Excel and Access
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: How to Paste From Clipboard to Excel Using VBA
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: Copy and Paste is Not Working in Excel (9 Reasons & Solutions)
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 Copy and Paste in Excel Using VBA (7 Methods)
Similar Readings
- How to Copy and Paste Conditional Formatting in Excel
- Excel Formula to Copy Cell Value to Another Cell
- How to Copy PDF File Names into Excel (9 Efficient Methods)
- Excel Formula to Copy Cell Value from Another Sheet (4 Examples)
- Copy Rows from One Sheet to Another Based on Criteria 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 Copy and Paste in Excel without Formulas (7 Easy Tricks)
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.
Read More: [Fixed]: Right Click Copy and Paste Not Working in Excel (11 Solutions)
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. Lastly, to learn more excel techniques, follow our ExcelDemy website. If you have any queries, please let me know in the comments.
Related Articles
- How to Copy Paste Vertical to Horizontal in Excel (2 Easy Ways)
- Copy Chart Format in Excel (3 Easy Ways)
- How to Copy from PDF to Excel and Keep Columns
- Copy Conditional Formatting Color to Another Cell in Excel
- How to Copy Above Cell in Excel (3 Easy Ways)
- [Fixed!]: Microsoft Excel Cannot Paste the Data as Picture
- Copy and Paste Formulas Without Changing Cell References in Excel