Function Problems

How to Fill Consecutive Dates Down a Column in Excel

WPS Editor Sep 25, 2026 868 views

Question details

The user needs to populate a spreadsheet column with a continuous sequence of dates but is experiencing an issue where Excel duplicates the first date instead of creating a series.

Product: Microsoft Excel
Device & OS: not provided
Scenario: Creating a sequential list of dates in a spreadsheet column using the fill handle.
Observed behavior: When dragging the fill handle downwards, the application copies the original date repeatedly into the following cells rather than continuing the chronological sequence.

Before you start

Ensure your cells are formatted as a 'Date' type (such as Short Date). If the cells are formatted as Text, Excel will not recognize the value as a date and cannot calculate a chronological sequence.

Solution 1Recommended

Establish a Sequence Using Two Starting Dates

Providing Excel with two consecutive dates establishes a clear pattern, making it the most reliable method for generating a correct date sequence.

When you only provide one date, Excel may default to copying the cell contents. By entering two dates, you explicitly define the interval (e.g., daily, weekly) so the AutoFill tool knows exactly how to increment the series.

Enter the first date

Type your initial starting date into the first cell of your column.

Enter the second date

In the cell directly below the first one, type the next date in your desired sequence (for example, the very next day).

Highlight both cells

Click and drag your cursor over both cells to select them simultaneously.

Drag the fill handle

Hover over the small green square at the bottom-right corner of your selection until the cursor turns into a plus sign (+), then click and drag it down the column to fill the remaining dates.

Establish a Sequence Using Two Starting Dates

Custom Date Intervals: This method works for any interval. If you want a sequence of dates that are exactly seven days apart, simply enter two dates that are one week apart in the first two cells.

Solution 2

Use the AutoFill Options Menu

If you prefer to start with just one date and it accidentally duplicates, you can easily switch the output from copied cells to a sequential series.

Efficient Spreadsheet Management

Easily Fill Date Sequences in WPS Spreadsheet

WPS Spreadsheet offers powerful, intuitive AutoFill capabilities. You can seamlessly fill consecutive dates, skip weekends, or create custom intervals—all with perfect file compatibility with Microsoft Excel.

1. Enter your starting date: Open your worksheet in WPS Spreadsheet and type your first date into a single cell.
2. Drag the fill handle: Select the cell, click the small square at the bottom right corner of the cell border, and drag it down the column.
3. Adjust AutoFill options: If the dates duplicate, click the small AutoFill options icon that appears at the end of the selection and choose 'Fill Series'.

100% compatibility with Microsoft Excel formats (.xlsx, .xls).Smart AutoFill features that intuitively recognize date and number patterns.Free, lightweight, and fast-loading spreadsheet software.Familiar user interface requiring no steep learning curve.

microsoft office alternative - wps office

Frequently Asked Questions

Why is Excel copying the date instead of filling a series?

This usually happens when Excel doesn't recognize a pattern, often because only one cell was selected before dragging, or the default setting is temporarily stuck on 'Copy Cells'. Providing two starting cells or using the AutoFill Options menu to select 'Fill Series' will fix the issue.

How can I fill consecutive dates while excluding weekends?

Enter your first date, drag the fill handle down the column, and then click the AutoFill Options icon that appears at the bottom right. Select 'Fill Weekdays' from the menu, and Excel will automatically remove all Saturdays and Sundays from the sequence.

How do I fill consecutive months or years instead of days?

Type your starting date and drag the fill handle down. Click the AutoFill Options button that appears and choose either 'Fill Months' or 'Fill Years' to keep the day identical while advancing the month or year.

What should I do if the fill handle is completely missing?

If you cannot see the small green square in the corner of your selected cell, the feature may be disabled. Go to File > Options > Advanced, look under the 'Editing options' section, and check the box next to 'Enable fill handle and cell drag-and-drop'.

Excel & Data Problems