Analistable

Combined sheet has blank rows: why it happens and how to fix it

By the Analistable team · Updated · 5 min read

Blank rows in a combined sheet come from the inputs, not the combine: a range or table that runs past the data, cells that are formatted but empty, or a VSTACK over fixed ranges that returns 0 for every empty cell. Wrap the stack in FILTER, remove blank rows in Power Query, or trim the source ranges.

Part of our guide: How to combine Excel sheets into one

Where the blank rows come from

Common causes and the matching fix
CauseWhat you seeFix
VSTACK over A2:D500 with 40 rows of data460 rows of 0s per sheetFILTER the stack
Sheet formatted down to row 10,000Power Query returns thousands of null rowsRemove Blank Rows step
Empty rows inside an Excel tableGaps between recordsDelete them in the table
Cells holding a space or "" from a formulaRows that look empty but aren'tFilter on trimmed length

VSTACK padding with zeros

VSTACK returns exactly the cells you give it. Point it at Jan:Mar!A2:D500 to leave room for growth and every empty cell comes back as 0, because a reference to an empty cell in a formula result shows 0. Stack ranges of different widths and the short ones are padded with #N/A. Filter on the first column to keep only real rows:

=LET(
  data, VSTACK(Jan:Mar!A2:D500),
  FILTER(data, CHOOSECOLS(data, 1) <> "")
)

In current Microsoft 365 builds, TRIMRANGE or the trim-reference syntax (Jan!A2:.D500) cuts trailing empty rows from a range before it is stacked; check your version has them. Converting each source to an Excel table and stacking the tables (=VSTACK(tblJan, tblFeb)) also avoids padding, as long as the tables themselves have no empty rows. There is more on these functions in dynamic arrays for combining data.

Rows returned by each formula, three sheets with 40, 38 and 41 rows of data
FormulaRows returnedBlank or zero rows
=VSTACK(Jan:Mar!A2:D500)14971378
FILTER on first column1190
=VSTACK(tblJan,tblFeb,tblMar)1190

Ghost rows from the used range

Excel tracks a used range per sheet. Formatting, borders or data validation applied to whole blocks, or data that was typed and later cleared, can extend it far past the real data. Press Ctrl+End on a source sheet: if it jumps well below the last record, the sheet carries a ghost range, and Power Query, macros and some combine tools read every row of it as nulls.

  1. Select the first empty row under the data, press Ctrl+Shift+Down to the bottom of the sheet.
  2. Right-click > Delete (not just Clear Contents) and do the same for empty columns to the right.
  3. Save the file. Ctrl+End should now land on the last real cell.

In Power Query, the simpler fix is a step after combining: Home > Remove Rows > Remove Blank Rows. It removes rows where every column is null or empty and keeps working as new files arrive.

Rows that only look empty

A cell holding a space, or a formula that returns "", isn't blank, so Remove Blank Rows and <>"" tests can keep it. Test on trimmed length to catch them:

=LET(
  data, VSTACK(tblJan, tblFeb, tblMar),
  FILTER(data, LEN(TRIM(CHOOSECOLS(data, 1))) > 0)
)

In Power Query, select the key column, Transform > Format > Trim, then filter out empty values. The same approach applies if you pull rows with formulas rather than stacking; see pulling data from multiple sheets into one.

Check before you report

  • Compare =ROWS(result) with the total of =COUNTA() on each source's key column.
  • Sort the combined sheet by the key column: blanks and zeros collect at one end where they are easy to see.
  • Check pivot tables for a (blank) item, which means empty rows made it through.

Analistable reads the sheets you load in the browser and you can ask it to count rows per sheet with an empty key, which is a quick way to confirm the inputs are clean. More combine methods are in the combine Excel sheets guide.

When blank rows are in the source on purpose

Some sheets use empty rows as visual separators between groups, or put a subtotal row after each block. Removing blanks is correct for those, but subtotals are a separate problem: they look like data and double every total once combined.

A source block with a separator and a subtotal
RowRegionRepSalesKeep?
2NorthAli4200Yes
3NorthBea3150Yes
4Subtotal7350No
5No (blank)
6SouthDee5010Yes

Filtering on a non-empty Region removes both the blank and the subtotal here, because the subtotal has no region. If subtotal rows do carry a label, filter them by text instead, for example CHOOSECOLS(data,2)<>"Subtotal" inside FILTER, or Text Filters > Does Not Contain in Power Query. Then total the combined data yourself. The real North figure is 7,350; leaving the subtotal in would show 14,700. Blank separators are harmless by comparison, since they add no value to a SUM, but they still inflate row counts and appear as (blank) items in pivot tables and slicers, which confuses anyone reading the report.

If you control the source layout, take separators and subtotals out and use grouping or a pivot table for presentation. The sheets then combine without any clean-up step, and the totals are calculated from the data rather than copied with it.

Frequently asked questions

Why does VSTACK show zeros instead of blanks?
Empty cells referenced in a formula return 0. Filter the stack on a key column, or stack tables or trimmed ranges instead of oversized fixed ranges.
Why does Power Query bring in thousands of empty rows?
The source sheet's used range extends past the data, often from formatting. Add a Remove Blank Rows step or delete the unused rows in the source.
Does Remove Blank Rows catch cells with spaces?
No, a space is a value. Trim the column first, then remove blanks or filter out empty values.
Why does Ctrl+End jump far below my data?
Excel's used range includes cells that were formatted or once held data. Delete the unused rows and columns, not just their contents, and save the file to reset it.
Is it safe to remove every blank row after combining?
Usually, provided a blank row never carried meaning. Check that the number of rows removed matches the padding you expected, and keep a copy of the sources so you can compare totals.

Related guides