How to Delete Blank Rows in Excel & Google Sheets

Written by

Editorial Team

Reviewed by

Laura Tsitlidze

Last updated on September 26, 2023

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 by 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:
```
=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.

This deletes all blank rows from the dataset.

Finally, turn off filters and delete the helper column.

You can also delete rows with blank cells in them with Go To (see How to Delete Rows With Blank Cells). But be careful, as you could end up deleting rows that are not entirely blank. The method above enables you to delete any rows that are completely blank, whereas if you delete rows with blank cells in them, you may end up deleting rows that do actually contain data.

You can use a similar method to delete blank columns in a worksheet.

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:
```
=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 in 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.

This deletes all blank rows from the dataset.

Finally, turn off filters and delete the helper column.