How to Delete Blank Rows in Excel Without Losing Data

Exports are the usual culprit. You download contacts from a form tool or orders from a shop dashboard, and the spreadsheet arrives full of gaps. Deleting the empty rows one at a time is fine for five of them, but nobody wants to do it for five hundred.

There's no single Delete Blank Rows button in Excel. Instead, you find the empty rows in one go and then delete them together. Here are the two ways Microsoft's help pages support, plus the safety check that stops you deleting real data.

Before you delete blank rows in Excel, save a copy

This takes ten seconds and I'd never skip it. Save a copy of the file, or duplicate the sheet, before you start. If something goes wrong you can press Ctrl+Z straight away, but a backup copy is a better safety net when you're working with a large customer or stock list.

Next, decide what blank means for your data. A row where every cell is empty is easy. A row where only the email column is empty is not blank at all, and you probably want to keep it.

Deleting blank rows in Excel with a filter

This is the method I trust most, because you see exactly which rows you're about to remove before anything disappears. Choose a column that should always have something in it, such as Name or Order ID.

1. Click any cell inside your data.
2. On the Data tab, select Filter. Small arrows appear in your header row.
3. Select the arrow on your chosen column.
4. Clear the (Select All) box, then tick (Blanks) at the bottom of the list, and select OK.
5. Excel now shows only the rows where that column is empty. Select those rows by their row numbers on the left.
6. Right-click the selection and choose Delete, or press Ctrl and the minus key.
7. Go back to the Data tab and select Filter again to switch the filter off and bring back your remaining rows.

Microsoft notes that the (Blanks) option only appears if the column contains at least one blank cell. If you can't see it, that column has no gaps, so try a different one.

Deleting empty rows from an Excel table

If your data is formatted as an Excel table, the filter arrows are already in the header row, so you can skip step 2. Microsoft's table steps are the same: filter the column for (Blanks), select the visible blank rows and press Ctrl and the minus key.

You can also use the Home tab: in the Cells group, select the arrow next to Delete and choose Delete Table Rows. That removes only rows inside the table, which is handy if you've got notes or totals elsewhere on the sheet.

Selecting blank cells in Excel with Go To Special

Go To Special is the other well-known route. It's quick, but it needs more care.

1. Select the block of data you want to clean.
2. On the Home tab, open Find & Select in the Editing group and choose Go To, or press Ctrl+G.
3. Click Special, choose Blanks and confirm.
4. Excel highlights every empty cell in your selection.
5. With those cells selected, use Delete Sheet Rows on the Home tab to remove the rows they sit in.

Here's the catch. Go To Special selects every empty cell, not every empty row. If a customer row has one missing phone number, that row gets selected too, and deleting rows would wipe that customer out. I only use this method when I know the gaps are whole empty rows, such as spacer rows in an old report.

Filter or Go To Special for blank rows in Excel?

Method	Good for	Watch out for
Filter for (Blanks)	Customer, order and contact lists	Pick a column that should never be empty
Table filter	Data already formatted as a table	Switch the filter off afterwards
Go To Special, Blanks	Spacer rows in tidy reports	Partly empty rows get caught too

Removing duplicate rows after you clear the blanks

Once the gaps are gone, duplicates are often the next problem, especially if you've pasted two exports together. In a table, click inside it, go to the Table Design tab and select Remove Duplicates in the Tools group. Choose which columns to check, such as Email, and select OK.

Microsoft says removed duplicates are deleted from the worksheet, but you can press Ctrl+Z or Undo straight away to bring them back if you've ticked the wrong column.

Why clean rows matter for business spreadsheets

Blank rows aren't just untidy. They can break things further down the line. Sorting can leave data stranded, and imports into email or CRM tools may skip or reject rows. Summary tools such as PivotTables also work best from a solid block of data with no gaps.

If you're on a wider digital clear-out, the same ruthless approach works elsewhere. My guide to emptying a cluttered Gmail inbox covers the inbox side, and removing an old YouTube playlist is worth a look if your channel has collected some dead weight.

If Excel won't delete your blank rows

1. Cells look empty but won't filter as blank? They may contain spaces or a formula returning nothing. Click a cell and check the formula bar.
2. Deleted rows came back? You may have switched the filter off before deleting. Repeat the steps and delete while the filter is on.
3. Delete greyed out? The sheet may be protected. Check the Review tab for Unprotect Sheet.
4. Deleted too much? Press Ctrl+Z immediately, or go back to the copy you saved.

Frequently asked questions

Is there a one-click way to delete blank rows in Excel?

No. Excel has no single Delete Blank Rows button. You filter for (Blanks) or use Go To Special to find them, then delete the selected rows together.

How do I delete blank rows in Excel without losing data?

Use the filter method on a column that should always be filled, such as Name. Only rows with that column empty are shown, so you can check them before deleting.

Why can't I see the (Blanks) option in the filter?

Microsoft says it only appears if the column contains at least one blank cell. Try a different column, or check whether the cells contain spaces.

What's the shortcut to delete selected rows?

Microsoft's table help gives Ctrl and the minus key for deleting the selected rows. You can also right-click the row numbers and choose Delete.

Can I undo deleting rows in Excel?

Yes, press Ctrl+Z straight away. A saved copy of the file is a safer backup for large lists.