How to Autofill Dates in Excel Without Dragging (7 Simple Methods)

Get FREE Advanced Excel Exercises with Solutions!

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.

how to autofill dates in excel without dragging


1. Using Flash Fill Tool in Excel

The Flash Fill tool in Excel is 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.

flash fill to autofill dates in excel without dragging

Read More: Applications of Excel Fill Series


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.

series option to autofill dates in excel without dragging

  • Next, in the Series window, fill in all the data as I have shown below and click OK.
  • As a result, the Series option will auto fill the remaining dates in the column.

Read More: How to Autofill Days of Week Based on Date 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)

SEQUENCE function to autofill dates in excel without dragging

  • Now, press the Enter key and you should see the rest of the cells with autofill dates.

Read More: How to Repeat Number Pattern 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)

DATE function to autofill dates in excel without dragging

  • Finally, press Enter and this will autofill the remaining dates.


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))

WORKDAY function to autofill dates in excel without dragging

  • 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, 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.

EDATE function to autofill dates in excel without dragging


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.

fill handle to autofill dates in excel without dragging

  • As a result, you should see that the Fill Handle has automatically filled the remaining dates.


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.


Related Articles


<< Go Back to Autofill Dates | Excel Autofill | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Nazmul Hossain Shovon
Nazmul Hossain Shovon

Nazmul Hossain Shovon, a BUET graduate in Naval Architecture and Marine Engineering, embarked on his career with 8 months dedicated to the Exceldemy project's triumph. Transitioning into a Software Developer role, he specialized in web add-in development. At Exceldemy, he authored about 125 blog articles and solved many visitors’ problems, refining his writing skills and delving into Excel-related topics. With a primary passion for programming and software development, Shovon continually explores new horizons, fostering professional growth in his... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo