How to delete empty rows in Excel

The !SEMTools procedure described at the end is fully functional in the free version.

How do you quickly remove the extra empty rows from a table without touching the filled ones? Let’s see how to select all blank rows down to the end of the sheet and delete them in bulk.

Before you start, one important warning. The popular advice “Go To Special → Blanks → Delete → Entire row” deletes every row that has at least one empty cell, not only completely empty rows. If your table has gaps in some columns, you’ll lose data. The methods below delete only rows that are empty entirely.

Formula to find and delete empty rows

A formula is a reliable way to identify empty rows. The method: a helper column marks the empty rows, you filter by it and delete them.

Step by step

• Insert a new column to the left of your data, so that it becomes column A. Type a header in A1.
• In A2, enter a formula that checks whether the whole row is empty: =IF(COUNTA(B2:XFD2)=0,”Empty”,”Not empty”). The COUNTA function counts the non-empty cells in the row.
• Fill the formula down to all rows that may contain data. It’s more reliable to select the range from the bottom up; Excel hotkeys for navigation and selection help here.
• Select the header of the helper column and go to Data → Filter.
• In the filter drop-down of the helper column, leave only “Empty”.
• Select all filtered rows by clicking the row numbers on the left, right-click and choose Delete Row.
• Clear the filter by clicking Filter again, and delete the helper column.

In Excel 365, if you just need a copy of the table without empty rows, one formula does it:

=FILTER(B2:E100,BYROW(B2:E100,LAMBDA(r,COUNTA(r)>0)))

Delete empty rows at the end of the sheet

Sometimes the empty rows are only at the end of the dataset. They contain no data, but annoyingly show up in filters as “(Blanks)” or make the scroll bar tiny. They are easier to remove:

• go to the first empty row after your data: Ctrl+↓ takes you to the last filled cell of the column;
• select the first empty row by clicking its number;
• press Ctrl+Shift+↓ to select all rows from it to the end of the sheet;
• right-click any selected row number and choose Delete;
• if you don’t want the rows to show at all, hide them from the same context menu instead.

Save the workbook afterwards: Excel resets the used range only after saving.

Highlight empty rows with Conditional Formatting

Finding the empty rows is the basis for deleting them quickly. Conditional Formatting can highlight them:

• Select the whole range. Click the top-left cell of the table (usually A1) and press Ctrl+Shift+End: this selects everything up to the last used cell of the sheet.
• Go to Home → Conditional Formatting → New Rule → Use a formula to determine which cells to format.
• Enter the formula =COUNTA($1:1)=0 for a range starting in row 1, choose a fill color and apply it.
• Apply a filter with Ctrl+Shift+L and filter the rows by color.
• Delete the filtered rows, then remove the conditional formatting rules from the sheet.

If there are very many scattered empty rows, deleting them can take a while. When possible, sort the rows by color first and delete them as one continuous block: then it’s instant.

Delete rows with empty cells in a column

Sometimes you need the opposite: delete rows where a particular column is empty, for example rows without an email. Filtering does the job:

• select the table and apply a filter (Data → Filter or Ctrl+Shift+L);
• in the drop-down of the column that matters, leave only (Blanks);
• select the visible rows, right-click and choose Delete Row, then clear the filter.

Delete empty rows automatically in 2 clicks with !SEMTools

The methods above work, but they all take time: helper columns, filters, formatting rules and cleanup. I used them too, until I got tired and added a procedure to my !SEMTools add-in. The main advantage is speed, and it’s available even in the free version.

Delete all empty rows on the current sheet

The procedure is in the DELETE → Ranges menu and removes the extra empty rows from the sheet automatically and instantly:

Delete empty rows on all sheets

A workbook may have several sheets with data, and empty rows on some of them, but you don’t know which. You’d have to check every sheet and run the procedure on each. To save that time, another procedure goes through all sheets of the active workbook and deletes the empty rows for you:

Once again: the procedure is completely free and available in all versions of the add-in.

Summary

I hope you found what you were looking for on this page and were able to delete the empty rows. Avoid Go To Special → Blanks if your table has gaps. Use a helper column with COUNTA, FILTER in Excel 365, or the free !SEMTools procedure that does it in 2 clicks on one or all sheets.

You may also be interested in how to delete empty columns in Excel and how to delete cells by condition.