This tutorial demonstrates how to delete rows with blank cells in Excel and Google Sheets.
Delete Rows With Blanks
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.
Filter for Blanks
A safer way to delete rows with blanks is to use the Filter feature.
- First, select the data. Then in the Ribbon, go to Data and click on 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. 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.
- Confirm deleting the entire row by clicking OK.
- To clear the filter, in the Ribbon go to Data and click on Clear.
To turn off the filter altogether, click on 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, just follow these steps:
- First, select the range. Then, in the Toolbar, go to Data, and from the drop-down menu, choose 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.