How to Insert a Blank Row When the Value Changes in Excel

The clean way to insert a blank row when the value changes

To insert a blank row when the value changes in Excel, sort your data by the grouping column, then run a macro that adds a blank row above each spot where that column changes. Excel has no button for it. The closest built-in feature, Subtotal, inserts total rows instead of clean blank separators, which is not what you want if you just need visual gaps between groups.

Here's how the common approaches compare.

Method	Adds total rows?	Clean blank separators?	One pass?
Data > Subtotal	Yes	No	Yes
Helper column + Find & Replace	No	Yes	No, fiddly
Free macro	No	Yes	Yes

TL;DR: Key takeaways

• Excel has no built-in command to insert a blank row when a value changes.
• Sort your data first on the column you're grouping by. The macro compares each row to the one above, so unsorted data scatters blanks everywhere.
• The free macro inserts a blank row above every row where your chosen column changes, in one pass.
• It asks which column to watch, counting from the first column of your selection (1 = first selected column).
• It won't add a blank above the very first group, only between groups, and it reports how many rows it inserted.

Sort your data first (this is the catch)

This is the step people skip, and it's the one that matters. The macro walks down your data and drops a blank row every time the watched column differs from the row directly above it. If your data isn't sorted, "different from the row above" happens constantly, and you get blank rows scattered through the whole list instead of clean groups.

So before anything else, sort by the column you want to group on. Select a cell in that column, go to the Data tab, and use Sort A to Z. Microsoft's guide to sorting data in a range or table covers the options if you need a multi-level sort.

Once every region, category, or date sits together in one block, the macro can find the boundary between groups and separate them.

How to insert a blank row when the value changes

With the data sorted, run the macro. Download Insert Blank Row On Change. It's a free .xlsm file with one macro. No signup, and it runs offline, so your data stays on your machine.

First time running one of our macros? See How to run our tools for the 30-second setup.

1. Sort your data on the column you want to group by (see above).
2. Select your data range, starting at the first data row. Leave the header row out, or you'll get a blank row right under it.
3. Press Alt + F8, pick InsertBlankRowOnChange, and click Run.
4. When asked, type the column number to watch, counting from the first column of your selection. 1 means the first selected column.
5. The macro inserts a blank row above each row where that column's value changes, then reports the count.

The logic is a short backward loop. Here's a simplified version you can adapt if you'd rather paste code, with the watch column set to column A and data starting in row 2:

Sub InsertBlankRowOnChange()
    Dim r As Long, lastRow As Long
    Const watchCol As Long = 1   ' column to group by (A = 1)
    lastRow = Cells(Rows.Count, watchCol).End(xlUp).Row
    Application.ScreenUpdating = False
    For r = lastRow To 3 Step -1
        If Cells(r, watchCol).Value <> Cells(r - 1, watchCol).Value Then
            Rows(r).Insert Shift:=xlDown
        End If
    Next r
    Application.ScreenUpdating = True
End Sub

It loops from the bottom up so the inserted rows don't shift the rows it hasn't checked yet. The downloadable tool does the same thing but asks which column to watch and validates your selection, so you don't have to touch the code.

Why not just use Subtotal?

Excel's Data > Subtotal is the feature that comes closest, and it's worth knowing when it's the right call. It groups by a column and inserts a row at each change, exactly where you want a break. The catch is what it puts in that row: a SUM, COUNT, or other total, plus a grand total at the bottom.

If you actually want subtotals, use it. If you just want a blank gap to make a printout readable, Subtotal gives you rows full of numbers you then have to clear out. The macro skips straight to the clean result: an empty row, nothing else, between each group.

Example: a sales list grouped by region

A sales operations lead at a distributor in Ahmedabad prints a weekly order sheet with about 400 rows, grouped by region for the field reps. Reading it is hard when North, South, and West runs blur into one another with no visual break.

Doing it by hand meant sorting by region, then clicking into the sheet and inserting a row every time the region changed, roughly a dozen times, watching carefully not to miss a boundary. A few minutes of fiddly clicking every week.

Now the lead sorts by region, selects the data, and runs the macro with the region column as the watch column. Every region gets a blank row above it, the printout breaks cleanly into blocks, and the message confirms "Inserted 11 blank row(s) where the column-1 value changed." The weekly fiddle turns into about three seconds.

FAQ

How do I insert a blank row every time a value changes in Excel?

Sort your data on the column you want to group by, then run a macro that inserts a blank row above each row where that column's value differs from the row above. The free InsertBlankRowOnChange macro does this in one pass and asks which column to watch.

Do I have to sort my data first?

Yes, and it's the step that makes or breaks the result. The macro inserts a blank wherever the watched column changes from one row to the next. If the data isn't sorted, matching values are scattered, so you get blank rows all through the list instead of a clean break between each group.

Can I insert blank rows when a value changes without a macro?

You can, but each option has a downside. Data > Subtotal inserts total rows, not blank ones. A helper column plus Find and Replace works but takes several steps. In Excel 365 you can build a dynamic-array formula with FILTER and VSTACK, though it's complex. The macro is the fastest clean route.

Will it add a blank row above the very first group?

No. The macro only inserts a blank between groups, above each new group after the first one. Your first block of data stays flush at the top with no stray blank row above it. It also leaves your header row alone as long as you start the selection at the first data row.

How do I remove the blank rows again later?

Select your data, press F5, click Special, choose Blanks, then delete those rows. Or use the remove blank rows macro to clear them all in one step. That makes the separators a temporary formatting layer you can add for printing and strip out afterward.

What to do next

Sort your data on the grouping column first. That one step is what turns a scattered mess into clean groups.

Download the free Insert Blank Row On Change macro and run it on your own sheet. It's the quickest way to insert a blank row when the value changes in Excel, so a long list prints as tidy, readable blocks instead of one unbroken wall of rows. If you want totals per group instead of blank gaps, the guide to Excel productivity macros covers the summary tools.