How to Delete All Blank Rows in Excel at Once

The fastest way to remove blank rows from your spreadsheet

The quickest method is to use Excel's Go To Special feature to select all blank cells at once, then delete the rows they occupy. This works in both Windows and Mac versions and takes about 30 seconds for most spreadsheets. You do not need to delete rows one at a time or use formulas.

If your spreadsheet has thousands of rows or blank cells scattered throughout, this approach saves hours compared to manual deletion. It also works whether your blanks are isolated or clustered together.

Key Takeaways

• Select your entire data range, then use Go To Special to find all blank cells in one step.
• After blanks are selected, right-click and choose Delete to remove entire rows at once.
• If blanks are only in specific columns, select just those columns before using Go To Special to avoid deleting rows you want to keep.
• The Sort method works if your data has a column with no blanks — sorting moves all blank rows to the bottom where you can delete them together.
• Always save a copy of your file before deleting rows, in case you need to undo the change later.

Using Go To Special to select all blanks at once

Open your spreadsheet and select the range of cells you want to clean. Click the first cell with data, then hold Shift and click the last cell in your data range. This selects everything between those two points, including any blank cells inside.

On Windows, press Ctrl+H to open Find & Replace, then click Options and choose Go To Special. On Mac, press Ctrl+G instead. A dialog box appears with checkboxes for different cell types. Check the box labeled Blanks, then click OK. Excel now highlights every empty cell in your selection.

Right-click on any highlighted cell and choose Delete. A second dialog appears asking what you want to delete. Select Delete Entire Row, then click OK. Excel removes every row that contains a blank cell in your selection. This is much faster than deleting rows individually.

Sorting to move blank rows to one location

If your spreadsheet has a column that contains data in every row — like an ID number or date — you can sort by that column to push all blank rows to the bottom. This works well when blanks are scattered throughout your sheet.

Select your entire data range including headers. Click the Data tab, then choose Sort. In the dialog, select the column with no blanks as your sort key. Click OK. Excel rearranges your data so that rows with blanks in other columns move to the bottom.

Once all blank rows are grouped together at the end, select them all at once by clicking the row number of the first blank row, then holding Shift and clicking the last row number. Right-click and choose Delete Rows. This removes them all in one action.

Deleting blanks in specific columns only

Sometimes you only want to remove rows where certain columns are blank, not rows with blanks anywhere. For example, you might want to keep a row if it has a blank in the Notes column, but delete it if the Customer Name column is empty.

Select only the columns you care about. Click the column header of the first column, then hold Ctrl and click other column headers to select multiple non-adjacent columns. Now use Go To Special on just those columns. When you delete, only rows with blanks in your selected columns are removed.

This approach prevents you from accidentally deleting rows that have blanks in columns you do not care about. It takes an extra 10 seconds but protects your data.

Using filters to hide and delete blank rows

The Filter method works well if you want to see what you are deleting before it happens. Select your data range, then click the Data tab and choose AutoFilter. A dropdown arrow appears in each column header.

Click the dropdown in any column and uncheck the box next to Blanks. This hides all rows with blanks in that column. Now select the visible rows you want to delete, right-click, and choose Delete Rows. Only the rows you see are removed.

After deletion, click the dropdown again and check Blanks to show all remaining rows. This method is slower than Go To Special but gives you a chance to review what will be deleted. It also works if you want to delete blanks in only one or two columns without affecting the rest of your sheet.

Why blanks appear in spreadsheets and how to prevent them

Blank rows often appear when you copy data from another source, export from a database, or manually enter information with inconsistent spacing. They can also result from deleting cell contents while leaving the row structure intact.

To prevent blanks in the future, use consistent data entry practices. If you are importing data, check the source file before copying. When you delete information from a cell, consider whether you should delete the entire row instead. Some spreadsheet programs allow you to set rules that prevent blank cells in key columns.

Regular cleanup — running one of these deletion methods once a month — keeps your spreadsheet manageable and prevents performance slowdowns that occur when files contain thousands of hidden blank rows.

What to do if deletion goes wrong

If you delete rows by mistake, press Ctrl+Z when ready to undo. Excel remembers your last action and can restore deleted rows as long as you have not saved the file yet.

This is why saving a backup copy before bulk deletion is important. If you close the file without undoing, you cannot recover the deleted rows. Keep a copy of the original file in a separate folder before you start cleaning.

If you realize the mistake hours or days later, check whether your spreadsheet program has a version history or recovery feature. Some cloud-based versions of Excel keep older versions of files for 30 days.

Frequently Asked Questions

Will deleting blank rows affect my formulas?

Yes, if your formulas reference rows by number — like =SUM(A1:A100) — deleting rows will shift those numbers. Formulas that reference named ranges or entire columns are safer. Before deleting, check whether any other sheets reference rows in this spreadsheet by row number.

Can I delete blank rows in multiple sheets at once?

No, you must clean each sheet separately. Select the first sheet, delete blanks, then move to the next sheet and repeat. There is no built-in feature to delete blanks across multiple sheets in one action, though some third-party add-ins offer this.

What if every other row is blank?

Use Go To Special the same way. Select your data range, find all blanks, and delete entire rows. Excel removes every blank row regardless of whether they are scattered or in a pattern. The result is a spreadsheet with no gaps.

Does the sort method work if I have headers?

Yes, but make sure your headers are included in the selection when you sort. Excel recognizes the first row as headers and keeps it at the top. If headers are not selected, they may move during the sort.

Can I undo a deletion after I save the file?

No, once you save, the deleted rows are gone unless your spreadsheet program has a version history feature. Always save a backup before deleting large numbers of rows, or test the deletion on a copy first.