How to Delete Blank Rows in Excel & Google Sheets
This tutorial demonstrates how to delete blank rows in Excel and Google Sheets.
Delete Blank Rows
If you have a dataset containing blank rows, you can easily delete them, using the COUNTA Function and deleting filtered cells. Say you have the following dataset.
To delete empty rows completely, follow these steps:
- At the end of the dataset, add one helper column (F), and enter the formula in cell F2:
1 |
=COUNTA(B2:E2) |
This formula counts all non-blank cells in the row.
- Now, copy the formula down the column, through Row 12.
All blank rows have zeros in Column F.
- Now, filter and display only blank rows. Then delete them. First, turn on the filter. Select any cell in the heading (Row 1), and in the Ribbon, go to Home > Sort & Filter > Filter.
- Click the filter icon in Column F, uncheck all values and leave only 0, and press OK.
- Select the entire visible dataset (excluding the header row), and in the Ribbon, go to Home > Find & Select > Go To Special…
- In the Go To Special window, select Visible cells only and click OK.
- Now all visible (empty) rows are selected. Right-click the selected area, and choose Delete Row.
- Now that all empty rows are deleted, clear the filter. Click the filter icon in Column F, check (Select All), and click OK.
As a result, all blank rows are deleted from the dataset.
- Finally, turn off filters and delete the helper Column F.
Note: You can also use VBA code to delete blank rows.
Delete Blank Rows in Google Sheets
Following almost the same steps, you can also delete blank rows in Google Sheets.
- At the end of the dataset, add one helper column (F), and enter the formula in cell F2:
1 |
=COUNTA(B2:E2) |
This formula counts all non-blank cells in the row.
- Now, copy the formula down the column, through Row 12.
All blank rows have zeros in Column F.
- Now, filter and display only blank rows and delete them. First, turn on the filter. Select any cell in the heading (Row 1), and in the Toolbar, click the Filter icon.
- Click the filter icon in Column F, uncheck all values and leave only 0, and press OK.
- Google Sheets always delete only visible cells/rows, so you just have to select all filtered rows, right-click the selected area, and choose Delete selected rows.
- Now that all empty rows are deleted, clear the filter. Click on the filter icon in column F, check Select All, and click OK.
As a result, all blank rows are deleted from the dataset.
- Finally, turn off filters and delete the helper column F.