How to remove blank rows in Excel without deleting real data

Blank rows break more than the look of a spreadsheet. Excel treats a fully empty row as the edge of your data, so Ctrl+A, sorting, filters, PivotTables and Format as Table stop at the first gap and quietly leave the rows below it out.

Removing them takes a minute, as long as you avoid the popular shortcut that also deletes rows holding data.

The trap: Go To Special > Blanks

The most shared tip is: select the data, press F5, click Special > Blanks, then Home > Delete > Delete Sheet Rows.

That selects every blank cell, not every blank row. If your table has a customer with no phone number, the empty phone cell is selected too, and deleting sheet rows removes that customer entirely.

It is safe in exactly one case: when you select a single column that is filled in on every real row, such as an ID column. Otherwise, use one of the methods below.

Method 1: Filter and delete (any version)

Good for a quick cleanup when one column is always filled for real rows.

1. Click a cell in your data and press Ctrl+Shift+L to add filters.
2. Open the filter on a column that is never empty for real rows (an ID or date column).
3. Untick Select All, tick (Blanks) and click OK.
4. Select the visible row numbers, right-click and choose Delete Row.
5. Clear the filter.

Method 2: A helper column that checks the whole row (safest)

When no single column is reliable, test the entire row. With data in columns A to F, starting at row 2, add this in column G:

=COUNTBLANK(A2:F2)=COLUMNS(A2:F2)

It returns TRUE only when every cell in the row is empty. Fill it down, filter column G to TRUE, delete those rows, then delete the helper column.

COUNTBLANK also counts cells whose formulas return an empty string "" as blank. COUNTA does the opposite and counts them as filled, so pick the one that matches what you mean by “blank”.

Method 3: Power Query (repeatable)

If you clean the same export every week, let Power Query do it.

1. Click inside the data and choose Data > From Table/Range.
2. In the Power Query editor, choose Home > Remove Rows > Remove Blank Rows.
3. Click Home > Close & Load.

Next time, paste the new data into the source table, right-click the output and choose Refresh.

Method 4: The FILTER function (Excel 365)

To produce a gap-free copy with a formula and leave the source untouched:

=FILTER(A2:F500, BYROW(A2:F500, LAMBDA(r, COUNTA(r) > 0)))

Every row with at least one value is returned, in its original order. Wrap it in IFERROR(…, "") if the range might be completely empty.

Method 5: Sort the empties to the bottom

Sorting any column moves fully empty rows to the end of the range, where they can be deleted in one block. Sorting reorders your data, so add an index column (1, 2, 3 …) first and sort back by it afterwards.

When “empty” rows will not delete

Rows that look blank but survive every method contain something you cannot see:

• Spaces. Use Find and Replace to replace a single space with nothing in the affected columns, or TRIM the data.
• Formulas returning "". Copy the range and Paste Special > Values so the empty strings become real blanks.
• Non-printing characters from exports, such as non-breaking spaces (character 160). =CLEAN(SUBSTITUTE(A2, CHAR(160), "")) strips them.

Empty columns cause the same boundary problems sideways. The helper-column method works for them too: check each column with COUNTA and delete the ones that return 0.

Remove blank rows and columns in one pass

For an export you just want tidy, our Excel Data Cleaner removes rows that are empty in every column, never partially filled ones, and can also drop empty columns, trim whitespace and remove duplicate rows. It shows how many rows and columns it removed before you download the clean .xlsx, and the workbook stays in your browser.

Excel Data CleanerStrip empty rows/columns, remove duplicates, normalize headers, convert dates, and clean up messy workbooks.Remove blank rows →

For CSV files, the CSV Cleaner does the same and reports how many blank rows it found.

Once the gaps are gone, the next common cleanup is repeated records: see how to remove duplicates in Excel.