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.
How to Autofill Dates in Excel Without Dragging: 7 Simple Methods
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 Autofill Formula in Excel
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: How to Repeat Formula Pattern in Excel
Similar Readings:
- AutoFill Cell Based on Another Cell in Excel
- How to AutoFill from List in Excel
- How to Create a Custom AutoFill List in Excel
- How to Fill Down Blanks in Excel
- How to AutoFill Sequential Letters in Excel
- How to Apply AutoFill Shortcut in Excel
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 Auto Number Cells in Excel
Similar Readings:
- How to AutoFill Numbers in Excel
- How to Autofill Numbers in Excel Without Dragging
- How to AutoFill Ascending Numbers in Excel
- How to AutoFill Numbers in Excel with Filter
- How to Auto Number or Renumber after Filter in Excel
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.
Similar Readings
- How to Auto Generate Number Sequence in Excel
- How to Add Sequence Number by Group in Excel
- How to Repeat Number Pattern in Excel
- How to Enter Sequential Dates Across Multiple Sheets in Excel
- How to Autofill Days of Week Based on Date in Excel
- How to AutoFill Months in Excel
- How to Create Automatic Rolling Months in Excel
- Excel Formulas to Fill Down Sequence Numbers Skip Hidden Rows
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 Increment Month by 1 in Excel
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.
Similar Readings:
- How to Autofill a Column in Excel
- How to Fill Column in Excel with Same Value
- How to Fill Down to Last Row with Data in Excel
- How to AutoFill Formula When Inserting Rows in Excel
- Filling a Certain Number of Rows in Excel Automatically
- How to Auto Populate from Another Worksheet in Excel
- How to Perform Predictive AutoFill in Excel
- Applications of Excel Fill Series
- [Fix] Excel Fill Series Not Working
- Fix: Excel Autofill Not Working
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.
Read More: [Fixed!] AutoFill Formula Is Not Working in Excel Table
Download Practice Workbook
You can download the practice workbook from here.
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, If you have any queries, please let me know in the comments.
