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.
- In the Ribbon, go to Home > Find & Select > Go To Special.
- In the Go To Special dialog box, select Blanks and click OK.
- Now, all the blank cells in that range are selected.
- 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.
- 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.
- First, select the data. Then in the Ribbon, go to Data > Filter.
- Click on the arrow in the column header (the Product column) and check the Blanks box. Click OK.
- 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.
- Click OK to confirm deletion of the entire row.
- 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:
- First, select the range. Then, in the Menu, go to Data > Create a filter.
- Click on the filter icon and in the drop-down menu, check Blanks. Click OK.
- 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.
- In the Menu, go to Data > Turn off filter.
The rows with blanks are deleted.