Remove Blank Rows in Excel

If you have a large list containing empty rows, dealing with each row manually may not be the best idea.

For that reason, I will show you how you can delete multiple rows at once with a few easy steps.

Deleting Blank Rows

If we have a situation where there is only one column, or many columns with blank cells in the same row, removing them is very simple.

Select cells from A1 to B8 and go to HOME >> Find & Select >> Go To Special…. Now, select Blanks and click OK.

As you can see, only blank cells are selected. Now, we will be able to remove rows 4 and 7.

We use one of the two methods.

First method:

Go to HOME >> Delete >> Delete Sheet Rows.

Second method:

Use Ctrl + – to open the Delete window and then choose Entire Row.

After deleting these rows, our example will look like this.

Deleting Blank Rows with the COUNTA function

Take a look at a slightly more complicated example.

In this example, we need to delete only the fifth row because only in this one all cells are blank.

We can’t use the same method we used earlier because only the sixth row will stay- no cell is empty (B6:D6).

Instead, we use the COUNTA function, which counts only cells that are not empty.

Example:

In this example add another column at the end of our table and then insert the following formula into cell E3.

Use Autofill to fill the rest of the cells.

Select cells from E3 to E8 and go to HOME >> Find & Select >> Find (Ctrl + F). In Find what enter 0 and click Find All. In our example, there is only one row with all cells blank and that is row 5.

Select all the values (Ctrl + A) and click Close. Now, the same way as before use Ctrl + – and then delete the entire row.

This is the final example.

Tomasz Decker is an Excel specialist, skilled in data analysis and financial modeling.