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

 

 

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

 

 

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

 

 

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

 

 

As a result, the rows with blanks are deleted.

 

 

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.

 

 

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

 

 

See also

• Display Go To Dialog Box
• Jump to Cell
• Using Find and Replace in Excel VBA
• Go To Cell, Row, or Column Shortcuts

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.

 

 

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

 

 

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

 

 

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

 

 

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

 

 

As a result, the blank rows are deleted.

 

 

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.

 

 

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

 

 

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

 

 

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

 

 

The rows with blanks are deleted.