See all How-To Articles

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 initial data

 

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.

 

delete blank rows initial data

 

To delete empty rows completely, follow these steps:

  1. At the end of the dataset, add one helper column (F), and enter the formula in cell F2:
=COUNTA(B2:E2)

 

delete blank rows 1

 

This formula counts all non-blank cells in the row.

  1. Now, copy the formula down the column, through Row 12.

 

delete blank rows 2

 

All blank rows have zeros in Column F.

  1. 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.

 

delete blank rows 3

 

  1. Click the filter icon in Column F, uncheck all values and leave only 0, and press OK.

 

delete blank rows 4

 

  1. Select the entire visible dataset (excluding the header row), and in the Ribbon, go to Home > Find & Select > Go To Special…

 

delete blank rows 5a

 

  1. In the Go To Special window, select Visible cells only and click OK.

 

delete blank rows 6

 

  1. Now all visible (empty) rows are selected. Right-click the selected area, and choose Delete Row.

 

delete blank rows 7a

 

  1. Now that all empty rows are deleted, clear the filter. Click the filter icon in Column F, check (Select All), and click OK.

 

delete blank rows 8

 

As a result, all blank rows are deleted from the dataset.

 

delete blank rows 9

 

  1. Finally, turn off filters and delete the helper Column F.

 

delete blank rows final data

You can also delete rows with blank cells in them.   You would need to be careful of this however, as you could end up deleting rows that are not entirely blank.  The method above will enabled 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.

  1. At the end of the dataset, add one helper column (F), and enter the formula in cell F2:
=COUNTA(B2:E2)

 

google sheets delete blank rows 1

 

This formula counts all non-blank cells in the row.

  1. Now, copy the formula down the column, through Row 12.

 

google sheets delete blank rows 2

 

All blank rows have zeros in Column F.

  1. 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.

 

google sheets delete blank rows 3

 

  1. Click the filter icon in Column F, uncheck all values and leave only 0, and press OK.

 

google sheets delete blank rows 4

 

  1. 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.

 

google sheets delete blank rows 5

 

  1. Now that all empty rows are deleted, clear the filter. Click on the filter icon in column F, check Select All, and click OK.

 

google sheets delete blank rows 6

 

As a result, all blank rows are deleted from the dataset.

 

google sheets delete blank rows 7

 

  1. Finally, turn off filters and delete the helper column F.

 

google sheets delete blank rows 8