Delete Blank Rows


To delete blank rows in Excel, use Go To Special, Blanks. You can also use the COUNTA function and a filter to remove empty rows.

1. On the Home tab, in the Editing group, click Find & Select.

Click Find & Select

2. Click Go To Special.

Click Go To Special

3. Select Blanks and click OK.

Select Blanks

Excel selects the blank cells.

Delete Blank Rows in Excel

4. On the Home tab, in the Cells group, click Delete.

Click Delete

5. Click Delete Sheet Rows.

Delete Sheet Rows


Delete Blank Rows Result

Conclusion: this method also deletes rows with one or more blank cells. If you don't want this, keep reading.

The following awesome trick only removes rows that are completely empty and leaves rows with one or more empty cells.

6. Add a Blanks column and insert the COUNTA function.

Blanks Column

7. Click on the lower right corner of cell H2 and drag it down to cell H10.

Remove Empty Rows in Excel

If the COUNTA function returns a 0, the row is "completely empty". To filter these empty rows, execute the following steps.

8. Select cell H1.

9. On the Data tab, in the Sort & Filter group, click Filter.

Click Filter

Arrows in the column headers appear.

Filter Arrows

10. Click the arrow next to Blanks.

11. Click on Select All to clear all the check boxes, and click the check box next to 0.

Filter Empty Rows

12. Click OK.

Result. Excel only displays the rows that are completely empty.

Empty Rows

13. Delete these rows.

14. Click Filter again to remove the filter.

15. Delete the Blanks column.


Remove Empty Rows Result