How to Delete Blank Rows in a Spreadsheet (2026)

That context matters more than the mechanics. A $42M ARR company nearly blew their monthly close when a NetSuite GL export added 87 blank separator rows into a 2,400-row dataset. The SUMIFS pulling actuals into the board pack P&L returned zero for six accounts - silently. Nobody caught it until the CFO asked why operating expenses were $1.2M light.

Blank rows in a linked model aren't a formatting problem. They're a data integrity problem.

Why Blank Rows Break Multi-Tab Models

A blank row inside a SUMIFS range causes no error. It just silently excludes whatever comes after it if your range doesn't extend far enough. Worse: structured references like Table1[Amount] snap to the actual table boundary, which may or may not include rows below the blank.

The specific failure pattern in a three-statement model: your cash flow tab pulls actuals with =SUMIFS('P&L'!D:D, 'P&L'!A:A, Assumptions!$B$3, 'P&L'!B:B, "Revenue"). Full-column references survive blank rows fine. The moment someone uses a fixed range like 'P&L'!D2:D487, a blank row at row 201 stops the aggregation and returns a partial result. No error. Just wrong numbers.

ARRAYFORMULA has the same exposure - specifically with IF logic that depends on the current row being non-empty. See ARRAYFORMULA in Google Sheets: FP&A Analyst Guide for the full behavior breakdown.

Deleting Blank Rows in Google Sheets: Filter Method

This is the right call for a one-time cleanup on a dataset under 50,000 rows. It takes about 30 seconds.

Select the column that should never be blank - your account code, entity name, or transaction date. Go to Data → Create a filter. Click the dropdown for that column, uncheck everything except "(Blanks)". You'll see only empty rows. Select them all (click the first row number, Shift+click the last), right-click, and choose Delete rows. Remove the filter.

Pick the anchor column carefully. If you filter on a column that legitimately has blanks (like an optional "Notes" or "Comments" field), you'll delete rows with real data. Use the identifier column - the one that's always populated if the row is real.

Deleting Blank Rows in Excel: Go To Special

Excel's Go To Special is faster than filtering for this task. Select only your anchor column first, then Ctrl+G (or F5) → Special → Blanks → OK. This selects every blank cell in your selection. Then Shift+Space to extend to full rows, right-click → Delete → Entire row.

The critical detail: Go To Special selects blank cells, not blank rows. If you run it on the full dataset instead of a single column, it'll try to delete any row that has a single empty cell - which includes rows with real data. Always pre-select the anchor column before pressing Ctrl+G.

On a 5,000-row GL file, Go To Special runs the whole operation in about 8-12 seconds. Filtering takes roughly 90 seconds of manual work to reach the same result.

Power Query for Recurring Imports in Excel

If the blank rows come from a recurring source - ERP export, accounting system dump, payroll file - filtering manually each month is the wrong fix. Power Query solves it permanently.

In Excel: Data → Get Data → From File (or From Table/Range if your data is already loaded). In the Power Query editor, Home → Remove Rows → Remove Blank Rows. Load back to the sheet.

According to Microsoft's Power Query documentation, "Remove Blank Rows removes all rows from the table where all cells in the row are empty." That distinction matters: a row with one non-blank cell survives. If your source has rows that are mostly empty but have a stray zero or space in one column, filter on the anchor column explicitly instead of using the built-in Remove Blank Rows.

Next month's import then auto-cleans on refresh. Five minutes of setup eliminates a recurring manual step from your close process.

FILTER Formula: Non-Destructive Option in Google Sheets

If you want to keep the source data intact and work from a clean version, FILTER is the right tool:

=FILTER('Raw Import'!A2:F2400, 'Raw Import'!A2:A2400 <> "")

This spills a clean copy - every row where column A is non-empty - into a separate output range. Your source data stays untouched, which matters when you're reconciling against the original file or multiple tabs pull from the same import.

The output is dynamic. When the source updates, the FILTER result updates too. Wrap it in SUMIFS on the output range and you have a clean pipeline from raw import to model input without ever deleting anything.

Apps Script for Automated Cleanup

For analysts who run the same cleanup on every import, a short Apps Script function handles it:

function deleteBlankRows() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet()
                  .getSheetByName('GL Import');
  const lastRow = sheet.getLastRow();
  
  // Read anchor column (A) all at once - faster than per-row reads
  const anchorCol = sheet.getRange(1, 1, lastRow, 1).getValues();
  
  // Iterate backwards to avoid row-index shift after each delete
  for (let i = lastRow; i >= 2; i--) {
    if (anchorCol[i - 1][0] === '') {
      sheet.deleteRow(i); // 1-indexed: row 1 = header, row 2 = first data row
    }
  }
}

The loop runs bottom-to-top because deleting row 50 shifts all subsequent rows up by 1. A top-down loop would skip the old row 51 (now row 50) on every iteration. This is the most common Apps Script blank-row bug.

According to the Google Apps Script Class Sheet reference, Sheet.deleteRow(rowPosition) "deletes the row at the given row position" and is 1-indexed throughout. Attach this to a button on the import tab and you've cut a manual step from the close process entirely.

Method Comparison (As of June 2026)

When Not to Delete Blank Rows

Blank rows used as section separators in a report template should stay. Same for blank rows that divide account categories in a COA layout or act as visual groupings in a board pack output tab. Deleting them breaks the report formatting.

The question is: is this a data tab or a presentation tab? Data tabs (raw imports, transaction logs, actuals feeds) should have zero blank rows. Presentation tabs (dashboards, board packs, output schedules) can have intentional blank rows for readability.

If your model pulls from a tab that doubles as a presentation layer, build a clean data tab upstream and feed all cross-tab references from there. ModelMonkey can identify which blank rows are structural separators versus genuinely empty data rows and delete only the latter - useful when a cleanup would otherwise require manual judgment on a 2,000-row import file.

Try ModelMonkey free for 14 days - it works in both Google Sheets and Excel.