Delete Blank Rows


This example teaches you how to delete blank rows or rows that contain blank cells.

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

The following awesome trick only removes rows that are completely empty and leaves rows with a few 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