How to Delete Rows With Blank Cells in Excel & Google Sheets

This tutorial demonstrates how to delete rows with blank cells in Excel and Google Sheets.

 

delete rows blank cells 7

 

Delete Rows With Blanks

Say you have a data range that contains rows with blanks, and you want to delete those rows. There are a few ways to do this, including using Excel features like Find & Select or Filter.

Find & Select

To delete rows with blanks using the Find & Select feature in Excel, use Go To Special.

  1. In the Ribbon, go to Home > Find & Select > Go To Special.

 

delete rows blank cells 2

 

  1. In the Go To Special dialog box, select Blanks and click OK.

 

delete rows blank cells 3

 

  1. Now, all the blank cells in that range are selected.

 

delete rows blank cells 4

 

  1. To delete these blank cells, right-click anywhere in the selected range. Then, in the drop-down menu, click Delete and choose Table Rows.

 

delete rows blank cells 5

 

As a result, the rows with blanks are deleted.

 

delete rows blank cells 6

 

Possible Issue With Find & Select

This method is only good to use on small tables and where there are no blank cells in a row with data, because if a row with data contains just one blank cell, the entire row is deleted. If you have a larger table, it’s not easy to see the potential data loss.

Say you have this table of products, and you want to delete Rows 4, 6, and 8 because they are empty.

 

delete rows blank cells 8

 

If you applied Steps 1–4 above, Rows 3 and 5 would also be deleted, because these rows contain both data and blanks.

 

delete rows blank cells 15 new

 

See also

Filter for Blanks

A safer way to delete rows with blanks is to use filters.

  1. First, select the data. Then in the Ribbon, go to Data > Filter.

 

delete rows blank cells 10

 

  1. Click on the arrow in the column header (the Product column) and check the Blanks box. Click OK.

 

delete rows blank cells 11 new

 

  1. Select all the filtered rows by pressing CTRL + HOME on the keyboard. Then press the (↓) down arrow key to go to the first data row and press CTRL + SHIFT + END. Right-click anywhere within the selected range and from the drop-down menu, choose Delete row.

 

delete rows blank cells 16 new

 

  1. Click OK to confirm deletion of the entire row.

 

delete rows blank cells 17 new

 

  1. To clear the filter, in the Ribbon go to Data > (2) Clear.
    To turn off the filter altogether, click on (3) Filter.

 

delete rows blank cells 13 new

 

As a result, the blank rows are deleted.

 

delete rows blank cells 19

 

Note: You can also use VBA code to remove blank rows.

Delete Rows With Blanks in Google Sheets

To delete rows with blanks in Google Sheets, follow these steps:

  1. First, select the range. Then, in the Menu, go to Data > Create a filter.

 

delete rows blank cells 20

 

  1. Click on the filter icon and in the drop-down menu, check Blanks. Click OK.

 

delete rows blank cells 21

 

  1. Now, select the first cell in the range (A2). Then press the SHIFT key and click on the last cell of the range (C9). This selects the entire range. Right-click somewhere within that range, and from the drop-down menu, choose Delete selected rows.

 

delete rows blank cells 22

 

  1. In the Menu, go to Data > Turn off filter.

 

delete rows blank cells 23

 

The rows with blanks are deleted.

 

delete rows blank cells 24