Managing dates efficiently in Excel can significantly boost your productivity. In this blog post, I’ll show you how to create a list of dates in Excel effortlessly using the powerful Autofill feature.

Whether you need a sequence of weekdays, months, or custom intervals, these tips will save you a lot of time! Let’s dive into the details.

Excel Practice Files

1. Autofill Days Of The Week In Excel

The Autofill feature makes creating a list of weekdays in Excel easy. This method helps you quickly generate schedules, logs, and other sequences.

Steps:

1. Enter the first day of the week (e.g., Monday) in cell A4.

2. Select cell A4 and position your mouse pointer over the fill handle (a small square at the bottom-right corner) until it turns into a black cross.

An Excel spreadsheet with the title "AutoFill Dates in Excel" in cell A1. The heading "Day of the Week" is in cell A3, and "Monday" is typed in cell A4. A small black plus sign appears at the bottom-right corner of cell A4, indicating the AutoFill handle is ready to be dragged to fill in the rest of the week.

3. Click and drag the fill handle [3] to create a list of consecutive days [4].

An Excel spreadsheet demonstrating how to autofill days of the week. The heading "AutoFill Dates in Excel" is in cell A1, and "Day of the Week" is in cell A3. "Monday" is typed in cell A4. The user has dragged the AutoFill handle down to cell A10, where "Sunday" appears in a tooltip. A green line outlines the autofilled range, and a small green circle labeled “3” is positioned near the bottom, possibly indicating a tutorial step.
Excel worksheet titled “AutoFill Dates in Excel.” Column A lists the full names of the days of the week from Monday to Sunday in cells A4 through A10, under the bolded header “Day of the Week.” This demonstrates a completed AutoFill sequence for the entire week.

Tip: Excel will fill the series using the same case as the day entered in cell A4. For example, if you enter the day in capitals, e.g. MONDAY, the series will be displayed as TUESDAY, WEDNESDAY, and so on.

2. Autofill Only Week Days In Excel

Sometimes, you may need a series that excludes weekends. Here’s how you can autofill only weekdays in Excel.

Steps:

1. Enter the first weekday (e.g., Monday) in the starting cell.

2. Use the fill handle to drag and create a list of days.

3. Click the Auto Fill Options button near the bottom right of the filled cells.

4. Select “Fill Weekdays” from the menu to exclude weekends from the series

Excel worksheet titled “AutoFill Dates in Excel.” Column A lists the days of the week from Monday to Sunday. The AutoFill Options button is highlighted, and the dropdown menu is open with “Fill Weekdays” selected. This setting tells Excel to exclude weekends when autofilling a date or day series.

3. Create Abbreviated Day Names in Excel

Occasionally, using abbreviated names for days is more convenient, particularly when working with narrow columns. Here’s how to craft a list of short-day names.

Steps:

1. Enter the three-character abbreviation of the first day (e.g., Mon) in the desired cell.

Excel worksheet showing a cell (A4) with the text "Mon" typed in. A black plus sign appears in the bottom-right corner of the cell, indicating the AutoFill handle is ready to be dragged.

2. Select the cell and drag the fill handle to create the list.

Excel worksheet showing the result after dragging the AutoFill handle down from "Mon". The days of the week—Mon through Sun—are filled automatically in cells A4 to A10.

Tip: The Auto Fill Options can still be used to create a sequence of weekdays only if needed.

3. Auto fill Every Second Day in Excel

Steps:

1. Enter the initial dates representing your desired interval (e.g., Wed and Fri).

2. Select both cells containing the dates.

Excel worksheet with "Wed" in cell A4 and "Fri" in A5. The AutoFill handle is active, ready to extend the pattern.

3. Drag the fill handle to extend the series, creating a list of every second day.

Excel shows the custom AutoFill pattern continuing from "Wed" and "Fri" with alternating days filled down the column: Wed, Fri, Sun, Tue, Thu, Sat, Mon.

4. Autofill Months of the Year

Autofill in Excel quickly creates a list of months. Just enter the first month and drag the fill handle to generate the rest.

Steps:

1. Enter “January” in the starting cell.

2. Select the cell and drag the fill handle down to autofill the list of months.

An Excel spreadsheet showing the months of the year being entered in column B. The heading "Months" is in cell B3, and cells B4 to B10 contain the months from January to July. The green AutoFill box with a small square at the bottom-right of cell B10 is visible, indicating that the user can drag to automatically fill in the remaining months.

Tip: Enter “Jan” for abbreviated months and follow the same steps above.

5. Auto fill Quarter Dates in Excel

Filling in quarterly months is perfect for financial reports and seasonal planning.

Steps:

1. Enter “Jan” and “Apr” in consecutive cells.

2. Select both cells and drag the fill handle down to extend the series.

3. Excel will create a sequence with quarterly intervals (e.g., Jan, Apr, Jul, Oct).

Excel worksheet showing a column labelled “Quarters” with the months Jan, Apr, Jul, and Oct listed from cell C4 to C7. These represent the starting months of each quarter. The AutoFill handle is active, allowing the sequence to be continued.

6. Autofill Week Starting Date

Autofill isn’t limited to text; it works seamlessly with actual dates as well.

1. Enter the start date (e.g., “20/05/2025”) in the first cell.

2. Enter the next date in the sequence (e.g., “27/05/2025”).

3. Select both dates, then drag the fill handle down to create a series of dates that are seven days apart.

Excel worksheet showing a “Date Series” in column D, with dates increasing by one week from 20/05/2025 to 1/07/2025. The AutoFill handle is active, ready to extend the weekly sequence further.

7. Auto fill Same Day Each Month

1. Enter a date (e.g., “20/05/2025”) in the first cell.

2. Select the cell and drag the fill handle down.

3. Adjust Auto Fill Options to “Fill Months”.

Excel worksheet showing a column labelled “Date Series” with daily dates filled in from 20/05/2025 to 27/05/2025. The AutoFill Options button is selected, displaying a dropdown menu with options such as Fill Series, Fill Formatting Only, Fill Days, Fill Months, and more. The option “Fill Months” is highlighted.

3. Excel will create a series on the same day each month.

Excel worksheet displaying a “Date Series” in column D where the 20th day of each month from May 2025 to December 2025 is listed, showing that Excel has autofilled the same day across consecutive months.

8. Autofill Same Day Every Year

1. Start with a specific date (e.g., “20/05/2025”).

2. Select the cell and drag the fill handle down. Adjust Auto Fill Options to “Fill Years”.

Excel worksheet displaying a sequence of daily dates starting from 20/05/2025. The AutoFill Options menu is open, and the “Fill Years” option is highlighted, showing how to change the fill pattern from days to years.

3. Excel will create a series on the same date each year

Excel worksheet showing a “Date Series” in column D, where the same date—20/05—is repeated across consecutive years from 2025 to 2033, demonstrating how Excel can autofill the same day each year.

Autofill Custom Dates in Excel

Creating a series of dates with specific intervals can be very useful for various planning and scheduling tasks.

Imagine wanting to create a list of phases for your project. By doing so, you can effortlessly record these dates into your spreadsheet whenever you plan or track the progress of your project. It’s a fantastic way to save time and ensure you have all the milestones noted accurately!

To create a custom list in Excel, follow these steps:

1. To start, enter the items you want to include in your custom list by typing them in a column one column in a worksheet.

Excel worksheet showing a finalised custom date list titled “Project Dates” in column A, listing phases from “Phase 1 - May 2025” to “Phase 5 - Jan 2027.”

Tip: enter a name for your list in the first cell in the list. This will be used to recall the list when we use Autofill.

2. Select the list, including the list name at the top of the list.

3. From the File tab, choose Options to open the Excel Options dialogue box.

Excel home screen with the “Options” button highlighted in the bottom-left corner, indicating how to access Excel settings for customising lists.

4. Once in the Excel Options dialog box, navigate to the Advanced category [1]. Scroll down to find the General section [2], and click the Edit Custom Lists button [3].

Excel Options dialog box with the “Advanced” section selected on the left. In the main panel, the “Edit Custom Lists…” button is visible under the section for creating lists used in sorts and fill sequences.

5. You have several choices in the Custom Lists dialogue box. You could type the list items directly in the List entries box [1], separating them with commas, or you can easily import the list from the ranges of cells you’ve set up earlier [2]. To import, click the Import button [3].

Custom Lists dialog box in Excel, showing how to create a new custom list. The List Entries field is empty, and a range of cells (e.g., $A:$A) is entered in the import box. The “Import” button is highlighted, ready to bring the data into the list.

6. After entering or importing the list items, your custom list will appear in the List entries box and the Custom lists box.

7. Click OK and OK again.

8. To use your custom list, type the first item from your list into a cell, then drag the fill handle

Excel worksheet with the header “Project Dates” in cell C1. The cell selection cursor shows the AutoFill handle being used, and a tooltip appears with “Phase 5 - Jan 2027,” indicating Excel is generating a custom sequence.

9. Excel will automatically fill the subsequent cells with the remaining items in your list. With custom lists, you will save a tremendous amount of time!

Excel worksheet displaying a custom list under “Project Dates,” with items from “Phase 1 - May 2025” to “Phase 5 - Jan 2027.” Cells C2 to C6 are filled with this sequence.

Tip: To ensure Autofill consistently uses your preferred case, start by entering the first list entry in the required case, e.g., lowercase or uppercase. This small step helps keep everything just the way you like it!

Watch The Excel Video Tutorial

Excel Practice File Download

  • Sign up to unlock the practice file

Conclusion: Best Practice Autofill Dates in Excel

After exploring various techniques for Auto-filling dates in Excel, it’s clear that this tool can make managing dates much easier and more efficient.

Whether filling in days of the week, skipping weekends, or creating custom date series, these best practices will help you get the most out of Excel’s autofill feature. Remember, practice makes perfect, so don’t hesitate to experiment with these methods to find what works best for you. Try these tips and share your feedback in the comments below!

Loved This Tip? Learn Even More in my Self-Paced Beginner Excel Course

Collection of devices with different parts of the Excel at Work Excel self-paced online courses being displayed with captions.

Excel Stage 1 is designed specifically for individuals who are totally new to Excel, have been self-taught or have received bits and pieces of training from multiple sources.

$347+gst


Sharyn Baines
Sharyn Baines
Certified Microsoft Office Specialist

Was this blog helpful? I’m here to empower your journey with Excel, aiming to make your daily tasks more efficient and boost your potential.

Share your thoughts in the Comments below – your insights not only enrich others, they also help me tailor future content to your needs.

And if you’re looking to take a step further, join our exclusive ‘Insider Group‘. As a member, you’ll receive Weekly Super-Tips, and early access to in-depth tutorials. Sign up Today!

Happy Excel-ling!!

Leave a Reply

Your email address will not be published. Required fields are marked *