How to Remove Blank Rows in Excel — 5 Safe Methods

What Counts as a Blank Row?

Before removing anything, define what you mean by blank:

• Completely empty: Every cell in the row is empty — no spaces, no formulas returning blanks, no invisible characters.
• Key column is blank: The row has a blank in a required column (like an ID or name field) but may have data in other columns. These are usually data entry errors, not intentional dividers.
• Visual spacers: Blank rows added intentionally to visually separate sections. These should be removed only if the layout is being reworked.

The method you use matters. Choosing the wrong one can delete rows that have real data in columns you did not check.

Why Blank Rows Cause Problems

• PivotTables stop at blank rows: When you create a PivotTable, Excel guesses the data range by looking for the first blank row. If a blank row interrupts the middle of your data, Excel misses all rows below it.
• AutoFilter shows no data below blanks: AutoFilter also stops at the first blank row. Data below is not filtered or sorted.
• VLOOKUP and SUMIF miss rows: If your lookup or sum range is defined with a fixed boundary, blank rows inside the range create gaps that some functions interpret as the end of the list.
• Sorting gives unpredictable results: Excel sorts up to the first blank row by default. Data below is left unsorted in place.
• Row counts look wrong: COUNTA counts non-blank cells, not rows. A thousand-row dataset with 200 blank rows still shows COUNTA reflecting all filled cells, but occupies 1000 rows of range height.

Method 1: Filter on a Key Column (Safest)

The safest method. Use it when only rows with a blank in a specific required column should be deleted — it never accidentally removes rows that have data in other columns.

1. Click any cell in your data.
2. Press Ctrl + Shift + L to apply AutoFilter.
3. Click the dropdown arrow on the key column (e.g., "Customer ID" — a column that must never be blank for real rows).
4. Uncheck Select All, then scroll down and check Blanks. Click OK.
5. You now see only rows where that column is blank. Review them visually to confirm they are empty rows, not real data with a missing value in one column.
6. Select all visible rows (click the first row number, then Shift + click the last row number).
7. Right-click and choose Delete Row.
8. Press Ctrl + Shift + L again to clear the filter. The blank rows are gone.

This is the recommended method because you control exactly which column defines "blank." If a row had data in other columns, you can review and decide whether to keep it before deleting.

Method 2: Sort to Cluster Blank Rows

For small datasets where the order does not matter or you are about to sort anyway:

1. Select the entire data range.
2. Sort by the key column (Data > Sort A to Z).
3. Blank cells sort to the bottom.
4. Select the blank rows at the bottom and delete them.

Limitation: this permanently reorders your data. If the original row order matters, use the Filter method instead. You can add a sequential row-number helper column first if you need to restore the original order after deletion.

Method 3: Go To Special — Blanks (Use With Caution)

Fast, but dangerous if any row has partial data.

1. Select the entire data range (including all columns).
2. Press F5 or Ctrl + G to open Go To.
3. Click Special > choose Blanks > OK.
4. Excel selects every blank cell in the selection.
5. Right-click and choose Delete > Entire Row.

Critical warning: Go To Special selects blank cells, not blank rows. If a row has data in columns A and C but column B is blank, the entire row gets deleted. Only use this method when you are absolutely certain that a blank in any column means the whole row is blank — for example, on a freshly exported dataset with no partial rows.

Method 4: Power Query (Best for Repeatable Workflows)

If you receive and clean this data regularly, Power Query removes blank rows repeatably with one click:

1. Click any cell in the data.
2. Go to Data > From Table/Range.
3. In Power Query Editor, go to Home > Remove Rows > Remove Blank Rows.
4. Click Close & Load.

Power Query defines a blank row as one where all columns are null. Rows with partial data are not removed. The query can be refreshed each time you receive new data, removing blanks automatically without you having to repeat the steps.

Method 5: Free Remove Blank Rows Tool

For fast, one-off cleaning without touching your original file:

1. Upload your workbook to the Remove Blank Rows tool.
2. Choose whether to remove rows that are completely blank or rows blank in a specific column.
3. Preview the result and download the cleaned file.

The tool processes the file in your browser — nothing is sent to a server. It handles multiple sheets and large files without requiring any Excel configuration.

Before You Delete: Safety Checklist

• Save a backup first. Deleting rows cannot be undone after saving. Copy the file before cleaning.
• Check formulas referencing this data. Formulas using A2:A100-style absolute ranges will not adjust after deletion. Consider converting the data to an Excel Table so references adjust automatically when rows are deleted.
• Check for intentional blank rows. Some spreadsheets use blank rows as visual section dividers. Removing them may collapse sections that were meant to be visually separate.
• Check for rows that look blank but have content. Cells that appear empty may contain spaces, non-breaking spaces (CHAR 160), or formulas returning "". Use =LEN(A2) on suspicious cells — a result greater than 0 means the cell is not truly empty.

Spotting Fake Blank Rows

A row may look blank but actually contain invisible content:

• Space characters: Pressing the spacebar leaves a space that looks blank. Test with =LEN(A2)>0.
• Non-breaking spaces (CHAR 160): Common in data pasted from web pages. TRIM does not remove these. Use =SUBSTITUTE(A2,CHAR(160),"") to clean them. See the full guide on removing spaces in Excel.
• Formulas returning blank: A formula like =IF(A2="","",B2) makes the cell appear blank. The row is not blank — it contains a formula. AutoFilter > Blanks will not show these cells.

Verifying the Result

After removing blank rows:

• Check the row count: the row number of the last data row should match your expected record count.
• Re-create the PivotTable or refresh it and confirm the row count in Values matches the expected count.
• Press Ctrl + End. Excel should jump to the last cell of real data — if it jumps much further, empty rows may still exist below your data range.
• Run COUNTA on the key column: =COUNTA(A:A)-1 (subtracting 1 for the header) should equal your expected record count.

Frequently Asked Questions

Can I use Find & Replace to remove blank rows?

Find & Replace can find blank cells but cannot select and delete entire rows. Use the Filter method or Go To Special instead. Find & Replace is better suited for replacing blank cell content with a placeholder value, not for removing rows.

How do I remove blank rows in a protected sheet?

You need to unprotect the sheet first: go to Review > Unprotect Sheet and enter the password if required. Then remove the blank rows, and re-protect the sheet if needed. If you do not have the password, you cannot modify a protected sheet. See the guide on locking cells in Excel for how sheet protection works.

I deleted blank rows but Ctrl+End still jumps to a far row. Why?

Excel remembers the last used range until you save. After deleting the rows, save the file (Ctrl + S) and Ctrl+End resets to the actual last used cell. If it still jumps too far after saving, check for formatting or data in columns or rows outside your main data range — these extend the used range even if cells appear blank.