Function Problems

How to Insert Blank Rows When Dates Change in Excel

Maira Mehtab Sep 21, 2026 869 views

Question details

The user needs to insert blank rows automatically between groups of records whenever the date changes in a sorted worksheet.

Product: Excel
Device & OS: not provided
Scenario: Organizing a sorted dataset by visually separating different date groups with blank rows for better readability.
Observed behavior: The data is currently contiguous, and the goal is to separate grouped records sharing the same date with a blank row whenever the date transitions to a new one.

Before you start

Ensure your dataset is strictly sorted in ascending or descending order by the date column before applying any method to insert blank rows.

Solution 1Recommended

Use the Excel Subtotal Feature

This is the most straightforward method to insert blank rows between grouped data without using complex formulas or macros.

The Subtotal feature can automatically group data and insert summary rows. By generating these summary rows and then clearing their contents, you can easily create clean blank rows between your data groups.

Select Data and Apply Subtotal

Highlight your entire dataset. Go to the 'Data' tab on the ribbon, click 'Outline', and select 'Subtotal'.

Configure Subtotal Settings

In the Subtotal dialog box, choose the date column from the 'At each change in' dropdown. Ensure no columns are checked under 'Add subtotal to' (or leave a dummy column checked) and click 'OK'.

Filter and Delete Count Rows

Select the entire dataset again, apply a Filter (Ctrl+Shift+L), and filter the date column to only show rows containing the word 'Count'. Select these visible rows, right-click, and delete them.

Clear Outline

Clear the filter to reveal your data. Go back to 'Data' > 'Outline' > 'Ungroup' > 'Clear Outline'. You will now have perfectly formatted blank rows separating your date groups.

Subtotal Limitation: The Subtotal feature cannot be used if your data is formatted as an official Excel Table. You must convert it to a standard range first (Right-click > Table > Convert to Range).

Solution 2

Use a Dynamic Array Formula (LET Function)

Ideal for advanced users who want to output a new array with blank rows automatically generated without altering the original dataset.

Solution 3

Use REDUCE and VSTACK Functions

A modern formula approach available in Microsoft 365 to conditionally expand arrays when a value change is detected.

Insert Blank Rows Between Data Groups Easily in WPS Spreadsheet

You can effortlessly organize grouped data and insert blank rows in WPS Spreadsheet using the intuitive Subtotal and Filter features, completely free of charge.

1. Open your Excel file in WPS: Launch WPS Spreadsheet and open the file containing your sorted date data.
2. Apply Subtotals: Navigate to the Data tab, click Subtotal, and group by the date column to automatically generate spacing rows.
3. Remove Subtotal Text: Use the built-in Filter to find the subtotal rows, delete the contents, and remove the outline to leave perfect blank separators.

Fully compatible with Microsoft Excel (.xlsx, .xls) file formats.Lightweight, fast, and completely free to use without subscription limits.Intuitive ribbon interface identical to standard spreadsheet tools.Built-in Subtotal and Data Outline features for quick data grouping.

microsoft office alternative - wps office

Frequently Asked Questions

Can I use a VBA macro to insert blank rows when a value changes?

Yes, you can write a simple VBA loop that reads from the bottom row up to the top, checking if the current cell's date matches the cell above it. If they are different, use Rows(i).Insert to add a blank row automatically.

Why is my Subtotal option grayed out?

The Subtotal feature cannot be used if your data is formatted as an official Excel Table (Insert > Table). You must convert the table back to a normal range (Right-click > Table > Convert to Range) before the Subtotal option becomes clickable.

Do the dynamic array formulas work in older versions of Excel?

No, functions like VSTACK, REDUCE, TOCOL, and LET are available only in Excel for Microsoft 365 and Excel 2021. If you are using an older version, the Subtotal method is the most reliable approach.

Excel & Data Problems