Delete Empty Rows in a Spreadsheet (Sheets & Excel)

Empty rows look harmless. They aren't. A =SUMIFS('Transactions'!D:D, 'Transactions'!A:A, ">="&Assumptions!$B$3) will return the right number because SUMIFS ignores empties in full-column ranges. But a pivot table sourced at 'Transactions'!A1:F5000 truncates silently at the first blank row it hits. Your board pack shows $4.2M in Q3 revenue. The real number is $6.8M. The other $2.6M lives below row 847, where someone pasted in October data and left three blank rows as a separator.

Method Comparison: Which Approach Fits Your Model?

Filter and Delete Empty Rows in Google Sheets

The fastest manual method in Google Sheets as of June 2026.

1. Click any cell in your data range.
2. Data > Create a filter.
3. Click the dropdown on a column that should always contain a value - date, entity ID, deal name. Never filter on an amount column where zero is a legitimate entry.
4. Uncheck "Select all," then check (Blanks).
5. Select all visible rows (Shift+click from first data row to last).
6. Right-click > Delete rows.
7. Data > Remove filter.

One thing to verify first: cells containing =IFERROR(VLOOKUP(...), "") are not truly empty - they hold a formula that returns an empty string. Those rows won't appear in the blank filter. Convert the column to values first (Paste Special > Values only), or use a helper column with =LEN(TRIM(A2))=0 to flag them before filtering.

Go To Special: Delete Empty Rows in Excel

Excel's Go To Special is faster than it looks and gets through a 15,000-row transaction file in under 2 minutes.

1. Select only the column that should always be populated - not the entire sheet. Selecting all columns will catch partially blank rows and delete real data.
2. Home > Find & Select > Go To Special > Blanks > OK.
3. Right-click any highlighted cell > Delete > Entire Row.

The risk here is row shifting. According to Microsoft's Excel documentation, deleting rows with Go To Special is non-reversible beyond Ctrl+Z (which has a limited undo history on large files). Run this on a copy of the tab first if you're not sure your cross-tab references are all using full-column notation.

Above 15,000 rows, Go To Special slows noticeably. Power Query's "Remove Empty Rows" step in Excel, or Apps Script in Google Sheets, is faster and more repeatable.

FILTER(): Delete Empty Rows Without Touching Source Data

When you need a clean dataset for analysis but want the raw import preserved, FILTER() gives you a non-destructive copy:

=FILTER('Raw Data'!A2:F5000, LEN(TRIM('Raw Data'!A2:A5000))>0)

This pulls every row where column A isn't blank or whitespace-only. Drop it on a staging tab, run your SUMIFS against the staged range, and the source stays untouched.

The limitation: this is a formula, not a permanent delete. If the source grows and new blank rows appear mid-dataset, the FILTER output shifts and any hardcoded references below it break. It's clean for a board pack or a bank syndicate DCF where you're sharing a read-only view, but it's not a substitute for cleaning the source in a model where other analysts write into specific cells.

FILTER() was introduced in Google Sheets in 2020 and in Excel 365 in 2022. It's not available in Excel perpetual licenses (2019 or earlier).

Apps Script: Delete Empty Rows at Scale

For recurring imports - a weekly CRM export, a monthly GL pull, a nightly data feed - manual cleanup is friction. This script deletes empty rows in 8-12 seconds on a 15,000-row file:

function deleteEmptyRows() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet()
    .getSheetByName('Transactions'); // update to your tab name
  
  const lastRow = sheet.getLastRow();
  const keyCol = 1; // Column A - required on every valid row
  
  // Read the whole column in one API call (avoids per-row calls, which are slow)
  const values = sheet.getRange(2, keyCol, lastRow - 1, 1).getValues();
  
  // Collect row numbers to delete, iterating bottom-up to avoid index shift
  const rowsToDelete = [];
  for (let i = values.length - 1; i >= 0; i--) {
    if (values[i][0] === '' || values[i][0] === null) {
      rowsToDelete.push(i + 2); // +2: zero-indexed array + 1 header row
    }
  }
  
  rowsToDelete.forEach(row => sheet.deleteRow(row));
  
  SpreadsheetApp.getUi().alert(`Deleted ${rowsToDelete.length} empty rows.`);
}

Two things worth knowing. First, getLastRow() sometimes overshoots on sheets where data was cleared but not deleted - Sheets retains the row in the sheet's extent even when empty. If that's causing slow runs, trim the excess after the loop with sheet.deleteRows(sheet.getLastRow() + 1, sheet.getMaxRows() - sheet.getLastRow()). Second, always iterate bottom-up when deleting rows. Deleting row 5 shifts row 6 down to row 5, so a top-down loop silently skips every other empty row.

To automate this on a schedule, add a time-driven trigger under Extensions > Apps Script > Triggers > Add Trigger > Time-driven.

Cross-Tab Reference Risk When You Delete Empty Rows

This is where most problems appear in multi-tab models. If your Cash Flow tab references 'P&L'!B14 explicitly and you delete rows above row 14 in the P&L, that reference does not update. It silently reads whatever landed in row 14 after the shift - which might be your depreciation line instead of EBITDA.

The fix is full-column references with SUMIFS criteria, not cell-specific hardcodes:

=SUMIFS('P&L'!C:C, 'P&L'!B:B, Assumptions!$B$5)

rather than:

='P&L'!C14  ← stays at row 14 regardless of what you delete above it

As of June 2026, Google Sheets does update structured table references automatically when rows are deleted. Cell-specific references in formulas do not update. Audit your cross-tab references before any bulk delete on a model with downstream tabs. A quick Ctrl+H search for 'P&L'! across the workbook will surface every hardcoded cell reference worth reviewing.

Automating Cleanup With ModelMonkey

If you're running the same set of steps on every import - delete the empties, re-sort by date, reformat the amount column - ModelMonkey handles that in a single prompt. You describe the cleanup rules once, and it runs the steps against your active sheet with an approval step before any destructive operation, so you can see exactly what's about to be deleted before it happens. Works in both Google Sheets and Excel.