Microsoft Excel is an excellent spreadsheet tool for data analysis and management. It offers a wide range of functions for organizing, calculating, and visualizing huge datasets. Users can do everything right, from boring data entry to advanced financial analysis.
It boasts many automation tools. One of the most used tools, named the AutoFill, lets users finish hours of work within a few seconds.
You can use it to fill dates in sequential order in a column automatically. It will save a lot of time and help in maintaining accuracy.
In this article, we will learn different methods of how to autofill dates in Excel. It is a quick guide with lots of useful information, so make sure to read it till the end.
Click on the following links to jump to that particular section of the article,
Download the Example Excel Sheet
Before we dive deep into the actual steps, make sure to download the following Excel. It includes the worksheets with the datasets used to demonstrate the autofill feature in Excel.
It will surely help you improve your skills to use the autofill feature through practice. You can skip downloading the above file if you have your dataset ready.
How to Autofill Days in Excel
Automatically getting all the required dates in Excel is straightforward. We need to use the Fill Handle feature.
A fill handle appears at the corner of the cell, as shown in the following image,
Let’s understand how to use this Fill Handle to get the successive days in Excel.
STEP #1 – Enter your first date
We need to start by entering our first date in the spreadsheet.
- Open a new Excel spreadsheet
- Select the first cell
- Type the date as “1/1/2023”
(You can replace the date with the one as per your requirement) - Press the “Enter” key
That’s it!
If you prefer to enter the date in some other format, then you must make sure it is acceptable by Excel or not.
STEP #2 – Use Fill Handle to Autofill Days
This is a crucial step in which we will autofill the dates in sequential order. This step is also easy, like the first one.
- Hover to the lower-rightmost corner of the cell
- You will see a “+” icon will appear
- Click on the Fill Handle
- Drag it to the end of the table
Excel will instantly fill the dates sequentially until you stop dragging the Fill Handle.
Note that if you have a table, as shown below, that contains the Date column, you can hover to the Fill Handle until you see the “+” icon and double-click on it to autofill the dates for that entire table.
How to check the valid dates in Excel
Dates can be represented in various formats. As humans, it is easy for us to interpret them. However, when it comes to Excel, this is not the case.
Excel supports a limited number of date formats. Consider the following example.
In the above image, the first three dates are in the correct format and are acceptable in Excel. And the remaining two dates are invalid.
There are three ways to check if the date is valid or not.
METHOD #1 – Check the alignment
Valid dates are always aligned to the right side of the cell, as shown in the image below.
If Excel does not recognize the date formats, then they are shifted to the left side of the cell and treated as a regular text string.
METHOD #2 – Use the DATEVALUE Function
The DATEVALUE is one of the simple and useful functions in Excel. The general syntax of this formula is as follows,
=DATEVALUE(date_string)Here, the “date_string” is the date in text format or the cell reference containing the date string that you want to convert.
Here is an example of the DATEVALUE function,
As the first three dates are valid, the DATEVALUE function successfully returned serial numbers for them.
On the other hand, the dates in cells C5 and C6 are invalid, so the function has returned the errors as shown below.
METHOD #3 – Use the ISNUMBER Function
You can use the ISNUMBER function to check if the values in cells represent valid dates.
The ISNUMBER function helps you determine whether a cell contains a numeric value, which can include dates since Excel stores date as serial numbers.
The general syntax for the ISNUMBER function is as follows,
=ISNUMBER(value)Where the “value” argument represents the value you want to test. This can be a cell reference, formula, or direct input.
Here is an example of the ISNUMBER function,
As the first two dates are valid, the ISNUMBER function successfully returns a “TRUE” representing a valid date.
On the other hand, the dates in the third cell return a FALSE, representing the date as invalid.
How to Autofill Weeks in Excel
The steps to autofill weeks in Excel are similar to the ones discussed in the previous section.
Here, you will need to enter two dates for the respective weeks instead of entering a single date compared to the previous section.
STEP #1 – Enter the first two dates
We need to start by entering the first two dates in the spreadsheet.
- Open a new Excel spreadsheet
- Select the first cell
- Type the date as “1/1/2023”
- Press “Enter” to go to the next cell right below it
- Type the date as “1/8/2023”
- Press the “Enter” key
Note that the date format (mm/dd/yyyy) used here is valid and acceptable by Excel. If you wish to enter a different date format, make sure to check if it is valid.
STEP #2 – Use Fill Handle to Autofill Weeks
In this second step, we will get the week start dates in a few seconds for the entire year.
Let’s begin,
- Select the first two dates
- Hover to the lower-rightmost corner of the second cell
- You will notice a “+” icon appear over the Fill Handle
- Click on that “+” icon
- Drag it using your mouse till the end of the table
How to Autofill Months in Excel
The steps to autofill months are similar to the ones discussed for the days and weeks.
Here, we need two dates that represent the first and second-month start dates.
Let’s begin,
STEP #1 – Enter the first two dates
We will start by entering the first two dates. Note that these two dates must be the first date of each month to avoid confusion in the future.
- Open a new Excel sheet
- Select the first cell
- Put date as “1/1/2023”
- Press “Enter,” and you will be moved to the next cell right below the first cell
- Put the date as “2/1/2023”
- Press the “Enter” key
You can enter the dates in the format of your choice but do not forget to enter a valid date that is acceptable in Excel.
STEP #2 – Use Fill Handle to Autofill Months
In this step, we will use the Fill Handle visible at the lower-rightmost corner of the cell to autofill months in Excel.
- Select the first two dates
- Hover over the Fill Handle visible at the lower-rightmost corner of the second cell
- A “+” icon will appear
- Click on that “+” icon
- Drag it till the end of the table
All the months will be filled in a sequential order in Excel, as shown above.
How to Autofill Years in Excel
To autofill years in Excel, you need to follow the same steps discussed in the above sections.
STEP #1 – Enter the first two dates
We need to start by entering the first two dates for the respective years. Make sure to enter the start date for each year to avoid confusion in the future.
- Open a new Excel sheet
- Select the first cell
- Enter the first date as “1/1/2023”
- Press “Enter” to move to the next cell right below the first date
- Enter the second date as “1/1/2024”
- Press “Enter” on your keyboard
Once you fill in the dates, let’s move to using the fill handle to auto-fill the years.
STEP #2 – Use Fill Handle to Autofill Years
Again, let us use the Fill Handle to autofill years in Excel.
- Select the first two dates
- Hover to the second cell, and you will see that a “+” icon appears over the Fill Handle
- Click on that “+” icon
- Drag it till the end of the table
All the dates for respective years will be filled in a sequential order, as shown above.
Using Series Fill To Autofill Dates In Excel
There is one more alternative to autofill dates in Excel named the Series Fill. Note that this feature works fine on the desktop version. If you are using Excel on a web browser, chances are it may not work properly.
Here are the steps to use the Series dialog box on the desktop app to auto-populate dates:
- Open a new Excel spreadsheet
- Select a cell and type a date. I have typed the date as “1/1/2023”
- Left-click and select the desired number of cells where you want additional dates
- Go to the “Home” tab
- Select the “Fill” button in the “Editing” section. Refer to the following image,
- Select the “Series” option from the dropdown
- A Series dialog box will appear on the screen as shown below,
- In the dialog box, tick the radio button before the “Date” option as shown below,
- From the right column titled “Date unit,” select “Day” to show a sequence of dates for a single month
- Click on the “OK” button
You can see how easy it is to use the “Series” feature in Excel. You can change the “Step Value” to increase the interval between dates, allowing you to customize the sequence to your needs.
For example, setting the Step Value to “2” will fill every second day, while setting it to “7” will fill every week.
Finally, you can explore our blog section, which is full of useful tips and tricks on how to use Microsoft Excel and Google Sheets like a pro.