Nazmul Hossain Shovon
In this tutorial, I am going to show you 7 simple methods to autofill dates in excel without dragging. Although you can also do this by dragging, this would be very tedious if you have a large dataset. Throughout this tutorial, you will learn some important tools and functions that will be really helpful in other excel related tasks.
Download Practice Workbook
You can download the practice workbook from here.
7 Simple Methods to Autofill Dates in Excel Without Dragging
We have used a concise dataset for this tutorial to explain the steps easily. Note that, for all the methods in this tutorial, you should format the data cells in column C that contains the purchase dates, in a date format. Also, you have to manually type in the first purchase date.
1. Using Flash Fill Tool in Excel
The Flash Fill tool in excel is very unique in that it can recognize data patterns and fill the adjacent cells accordingly. We can use this tool to quickly autofill dates in excel without dragging.
Steps:
- First, manually enter the first date.
- Now, go to the Data tab and then to Data Tools.
- Under Data Tools, select Flash Fill.
- Immediately, this will fill the rest of the cells in the column.
Read More: How to Use Flash Fill in Excel to Split Data (4 Quick Ways)
2. Autofill Using Series Option
The Series option in excel can also autofill dates in excel without dragging with just a few inputs from the user. Let us see how it works.
Steps:
- To begin with, go to the Editing section under the Home tab and then to Fill.
- Now, click on Series.
- Next, in the Series window, fill all the data as I have shown below and click OK.
- As a result, the Series option will fill the remaining dates in the column.
Read More: [Fix] Excel Fill Series Not Working (8 Causes with Solutions)
3. Using SEQUENCE Function
The SEQUENCE function in excel can generate a list of sequential numbers as an array. We can use this function to quickly autofill dates without dragging.
Steps:
- To begin this method, double-click on cell C5 and enter the following formula:
=SEQUENCE(6,1,"1/1/2022",1)
- Now, press the Enter key and you should see the rest of the cells with autofill dates.
Read More: How to AutoFill Sequential Letters in Excel (5 Quick Ways)
4. Utilizing DATE Function
The DATE function generates a valid date by taking an individual year, month, and day as input. Here we will generate dates for the different months and autofill them without dragging.
Steps:
- First, navigate to cell C5 and type in the below formula:
=DATE(2022,SEQUENCE(6),1)
- Finally, press Enter and this will autofill the remaining dates.
Read More: How to Autofill Days of Week Based on Date in Excel (5 Easy Ways)
Similar Readings
- How to AutoFill Formula When Inserting Rows in Excel (4 Methods)
- Filling a Certain Number of Rows in Excel Automatically (6 Methods)
- How to AutoFill Numbers in Excel with Filter (2 Methods)
- [Fixed!] AutoFill Formula Is Not Working in Excel Table (3 Solutions)
- How to Auto Number or Renumber after Filter in Excel (7 Easy Ways)
5. Applying WORKDAY Function in Excel
The WORKDAY function can generate the list of workdays inside a specified range. This function skips the holidays as the name suggests. We will use this function to autofill dates of purchase in excel without dragging.
Steps:
- First, go to cell C5 and insert the following formula:
=WORKDAY(F5-1,SEQUENCE(F4))
- Now, press the Enter key and this will immediately autofill all the remaining cells with purchase dates.
Read More: How to AutoFill from List in Excel (8 Quick Ways)
6. Autofill Dates Using EDATE Function
The EDATE function in excel can autofill the same dates for different months. As this is a function, so we do not have to perform any dragging. Let us see how to use this function.
Steps:
- First of all, double-click on cell C5 and type in the following formula:
=EDATE(E5, SEQUENCE(6, 1, 0))- Then, press Enter.
- Consequently, this formula will generate and autofill all the remaining dates.
Read More: How to Autofill Numbers in Excel without Dragging (5 Quick Methods)
7. Utilizing Fill Handle in Excel
The Fill Handle in excel helps to extend data to different cells and also to copy formulas. We will use this tool to autofill dates in excel without dragging. Follow the steps below.
Steps:
- To start with, go the cell C5 where you inserted the first purchase date.
- Now, at the lower-right corner of this cell, you should see a + sign which is the Fill Handle.
- Here, simply double-click on this + sign and notice the cells below.
- As a result, you should see that the Fill Handle has automatically filled the remaining dates.
Conclusion
I hope that you were able to apply the above methods to autofill dates in excel without dragging. Although we have used a small dataset, you can use any large dataset, and actually, the benefits of these methods are more visible with larger datasets with lots of rows and columns. 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
- Drag Number Increase Not Working in Excel (A Solution with Easy Steps)
- How to Turn Off AutoFill in Excel (3 Quick Ways)
- AutoFill Formula to Last Row with Excel VBA (5 Examples)
- How to AutoFill Months in Excel (5 Effective Ways)
- Applications of Excel Fill Series (12 Easy Examples)
- How to Add Sequence Number by Group in Excel (2 Formulas)
- Excel Auto Generate Number Sequence (5 Simple Ways)
