How to Delete Blank Rows in Excel Safely

To delete blank rows in Excel safely, filter a column that should always be filled, show only the blanks, and delete those visible rows. It takes under a minute and never touches rows that hold data. This guide is for anyone cleaning an export or a shared list, and it shows three methods, plus the popular shortcut that quietly deletes real data.

What counts as a blank row?#

Before you delete anything, decide what "blank" means for your list. There are two different cases:

• A fully empty row: every cell from the first column to the last is empty. These usually come from copying, importing or leaving gaps between groups.
• A row with a missing key value: the Name or ID is empty, but other cells have something in them. These might be broken records you want to fix, not delete.

All the examples use this list in A1:D8. Rows 4 and 7 are completely empty. Row 6 is missing only a region.

The goal is to remove rows 4 and 7 and keep Dmitri's row. Before you start, save the file or copy the sheet. Deleting rows can't be fixed later if you've already saved and closed.

Method 1: Delete blank rows with a filter (safest)#

Filtering on a key column is the safest method because you see exactly which rows will go before you delete them. A key column is one that every real record fills in, like Name or ID.

1. Click any cell in the list.
2. Turn on filters with Ctrl+Shift+L (Mac: ⌘⇧F), or choose Data > Filter.
3. Click the filter arrow on the Name column.
4. Untick (Select All), tick (Blanks) at the bottom, and click OK.
5. Select the visible rows by dragging over their row numbers on the left (rows 4 and 7 here).
6. Press Ctrl+- (Mac: ⌘-), or right-click and choose Delete Row.
7. Turn the filter off with Ctrl+Shift+L (Mac: ⌘⇧F) again.

Excel deletes only the visible rows, so the hidden rows with data are safe. Dmitri's row stays, because his name is filled in. If you're new to filters, the guide to the Excel filter shortcut covers the keys, and you can practise filtering a table in a live sheet.

Method 2: Use a helper column to find truly empty rows#

When no single column is always filled, add a helper column that counts the filled cells in each row. A helper column is an extra column you add only to do a calculation, then delete.

In E2, type:

=COUNTA(A2:D2)

Fill it down to E8 with Ctrl+D (Mac: ⌘D). COUNTA counts cells that are not empty. Rows 4 and 7 show 0. Dmitri's row shows 3, because only one of its four cells is empty.

Now filter column E for 0 and delete the visible rows, exactly as in Method 1. Then delete the helper column. This method is the most precise, because a row only shows 0 when every cell is empty. The article on COUNTIF for non-blank cells explains COUNTA and other ways to count filled cells.

Method 3: Go To Special > Blanks (use with care)#

Go To Special can select every empty cell in one step, but it is only safe on a single key column. Many online tips tell you to select the whole list, so be careful.

The risky way looks like this: select A1:D8, choose Home > Find & Select > Go To Special, pick Blanks, then Delete > Delete Sheet Rows. Excel selects every empty cell, including C6, Dmitri's missing region. So it deletes row 6 too, and Dmitri's sales disappear without warning.

The safe way:

1. Select only the key column, for example A2:A8.
2. Press F5 or Ctrl+G (Mac: Control+G) and click Special.
3. Choose Blanks and click OK. Only A4 and A7 are selected.
4. Choose Home > Delete > Delete Sheet Rows.

Selecting a column quickly matters here. Click the first cell and press Ctrl+Shift+Down arrow (Mac: ⌘⇧Down arrow) to extend to the end of the data. Watch out: the selection stops at the first empty cell, so you may need to press it again to reach the last row. You can practise selecting ranges with Shift to get fast at this.

Why can't Excel find my blank rows?#

If the filter shows no (Blanks) or Go To Special says "No cells were found", the cells are probably not truly empty. Common causes:

• A space typed into the cell, often from an import.
• A formula that returns empty text, like =IF(B4="","",B4). The cell looks empty but holds a formula.
• An apostrophe on its own, which Excel hides.

To check a cell, type =LEN(A4) next to it. A result of 0 means the cell is empty or holds empty text. Any other number means there are hidden characters. Use Find & Replace (Ctrl+H; Mac: Control+H) to replace a single space with nothing, or paste the formula cells as values first, then try again.

Should you delete or hide blank rows?#

Delete blank rows when they are true gaps in a list. They break sorting, filtering, pivot tables and shortcuts like Ctrl+Down arrow, which stop at the first empty row.

Hide rows instead when they are deliberate spacing in a report you might print or present, or when a formula elsewhere points at them. Hiding keeps the layout and all references. The guide to hiding and unhiding rows and columns shows how.

Deleting also affects formulas. A SUM over D2:D8 shrinks to D2:D6 when two rows go, which is what you want. But a formula that pointed directly at a deleted cell shows #REF!. The guide on how inserting and deleting rows affects formulas explains why.

If the empty cells are inside your records and you want to fill them rather than remove rows, see how to fill blank cells with the value above.

Remove blank rows with a formula#

In Excel 2021 or Microsoft 365, the FILTER function can return a clean copy of the list without touching the original. In an empty area, type:

=FILTER(A2:D8,A2:A8<>"")

This returns every row where the Name is not empty: Amira, Ben, Carla, Dmitri and Elif. The result spills, which means it fills the cells below and to the right by itself. If you later need it as plain data, copy it and paste as values. Older versions of Excel don't have FILTER, so use Method 1 there.

Practise it#

Start with the exercise on this page: counting the empty key cells tells you how many rows you should expect to delete, which is a quick safety check. Then practise filtering a table in a live spreadsheet, where you filter for blanks and work with only the visible rows. Finish with the Shift selection lesson so you can select a whole column of data in one move.