How to remove blank rows in Excel

Method 1: Go To Special > Blanks on one key column

This customer list came out of an export with empty rows in between. Rows 3, 6 and 9 are empty, and row 7 looks empty but has a space in A7. Kim Tran has no region and Ben Cole has no orders yet, but they are real customers and must stay.

The trick is which cells you select. Pick one column that is filled in for every real record and empty in every junk row. Here that is Email. The blanks in B2:B11 are rows 3, 6, 7 and 9, exactly the four rows to remove.

1. Select B2:B11, the Email column, without the header.
2. Press F5 (or Ctrl+G), click Special, choose Blanks and click OK. Only the empty cells in B2:B11 stay selected.
3. Go to Home > Delete > Delete Sheet Rows. Excel removes every row that has a selected cell.
4. Check the row count. If something went wrong, press Ctrl+Z straight away.

The trap: selecting the whole table first

Most online answers say to select the whole data range and use Go To Special > Blanks. On this list that selects C5 (Kim's missing region) and D11 (Ben's missing orders) along with the empty rows. Delete Sheet Rows then deletes Kim and Ben too, and nothing warns you.

Before deleting, count how many rows are really blank across every column. The formula joins each row's four cells into one piece of text, trims away spaces and counts the rows where nothing is left. If Go To Special highlights more rows than this, stop and choose a different key column.

Rows that look blank but contain a space

A7 holds a single space, so it looks empty but Excel treats it as text. Go To Special > Blanks on column A skips it, COUNTBLANK skips it, and Ctrl+arrow keys stop at it. Exports and copy-paste from web pages often leave spaces, empty strings and non-printing characters like this.

This is why the key column matters. On column A, COUNTBLANK reports only 3 blank cells, and Blanks would leave row 7 behind. Either pick a column where the junk rows really are empty, or clean the column first with Ctrl+H: Find what a single space, Replace with nothing, Replace All. That removes every space in the selected cells, so only do it on a column where values never contain spaces, or use TRIM in a helper column instead.

Method 2: FILTER a copy without gaps (Excel 365 / 2021)

If you cannot change the original, for example because another sheet or a colleague's formula points at it, make a gap-free copy with FILTER. Put the formula on another sheet or to the right of the data. It keeps every row where Email is not empty and updates when the source changes.

One surprise: FILTER returns 0 for empty cells, so Kim's region and Ben's orders show as 0. To keep them blank, filter a version of the range where empties are turned into "": =FILTER(IF(A2:D11="","",A2:D11),B2:B11<>""). FILTER is not available in Excel 2019 or earlier; use Method 1 or 3 there.

Method 3: a filter, then delete the visible rows

The AutoFilter route lets you see what you are about to delete before you delete it, which makes it the safest method on a long list. When rows are filtered, Excel deletes only the visible rows, not the hidden ones in between.

For blank rows across several columns, add a helper column with =LEN(TRIM(A2&B2&C2&D2)), fill it down and filter that column for 0. Delete the helper column when you are done. If the data lives in Power Query, Home > Remove Rows > Remove Blank Rows does the same on every refresh.

1. Click any cell in the header row and go to Data > Filter.
2. Open the Email drop-down, untick Select All, tick (Blanks) and click OK.
3. Select the visible row numbers under the header, right-click and choose Delete Row.
4. Go to Data > Clear to show the remaining rows, then Data > Filter to turn the filter off.

Why not just sort the blanks to the bottom?

Sorting does push empty rows to the end, and it is quick. But it throws away the original order, which matters for logs, invoices and anything in date or entry order. Worse, if you click one cell inside data that has blank rows, Excel treats each blank row as the edge of the table and sorts only one block.

If you do sort, first add a helper column numbered 1, 2, 3 down the rows, select the whole range including it, sort, delete the blank rows at the bottom, then sort by the helper column to put the order back.