5. Excel How to delete empty rows ?

An easy way to remove empty rows is to :

This method works well when there are no empty cells in the rows we want to keep.

1^st situation

Let's take the following list for example :

Open the Find & Select menu in the Editing group on the Home tab of the Ribbon and choose Go To Special...
In the Go To Special dialog box, check the Blanks option.
Confirm with the OK button. All the empty cells on the sheet are now selected.
Open the Delete menu in the Cells group on the Home tab of the Ribbon and choose Delete Sheet Rows.

If I use the previous method with the following list case, then line 3 and 9 will also be deleted:

A solution :

If I want to make sure to delete only completely empty rows, then I use the Excel function COUNTA to test whether all cells in a table row are empty.

In cell F2 enter the following formula and copy it to column F by Auto Filling:

=COUNTA(A2:E2)

This formula gives 0 when all 5 cells in a row are empty. Then you just need to filter column F with the value 0 and delete the filtered rows.

An alternative way of testing whether all the cells in a table row are empty is to use the And function.

In cell F2 enter the following formula and copy it to column F by Auto Filling:

=AND(A2="",B2="",C2="",D2="",E2="")

Filter column F with the value TRUE and delete the filtered rows.