Table of Contents

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

Result:

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.

Result:

Remove Empty Rows Result

Leave A Comment

Excel meets AI – Boost your productivity like never before!

At Formulas HQ, we’ve harnessed the brilliance of AI to turbocharge your Spreadsheet mastery. Say goodbye to the days of grappling with complex formulas, VBA code, and scripts. We’re here to make your work smarter, not harder.

Related Articles

The Latest on Formulas HQ Blog