When you work with large amounts of data in Excel or Google Sheets, even the simple task of removing all blank cells in your worksheet could become a daunting chore if you have to do it manually. A quick and painless way to clean up your spreadsheet in Excel is to use the Go To Special feature. This tool will help you identify all the empty cells in your document and delete them all at once. While this makes it an easy option to use, beware that it may cause a misalignment in your document. To be safe, you should always save a backup copy of your document before you start deleting cells. It is also better to delete entire rows or multiple columns to avoid screwing up the order of your data.
If you’re using Google Sheets, you can use Filter to delete blank rows or blank cells in a column; this method also works in Excel.
How To Delete Blank Cells in Excel using Go To Special
1. Select cell range. Highlight all the cells you want to filter.
2. Select Go To Special from the Find & Select menu. You’ll find the Find & Select Menu on the Home tab in the Editing group. You can also hit F5 then click the Special button.
3. Select the Blanks option in the popup menu. All the blank cells in your document will be highlighted.
4. Delete selection. Right-click on any one of the highlighted cells and click Delete. Choose from the delete options in the popup menu, then click OK.
How To Delete Blank Rows in Excel using Filter
1. Select data set range. Highlight all the cells you want to filter.
2. Navigate to the Sort & Filter menu. On the Home tab, in the Editing group, click Sort & Filter and select Filter (funnel icon).
3. Filter all Blank cells. Click the arrow icon from any column. In the dropdown menu, uncheck Select All and check the (Blanks) option. This will sort together all the blank rows in the range you chose.
4. Delete selection. Right-click on any one of the highlighted cells and click Delete. Your table will look empty. Once again, click the arrow icon from the column you chose and select Clear Filter. All your data will reappear without the blank cells.
Note: The same process applies to delete blanks cells in a column.
Unfortunately, the Go To Special command is not available in Google Sheets.
How To Delete Blank Rows In Google Sheets
1. Select data set range. Highlight all the cells you want to filter.
2. Turn on Filter. Click the Create a filter option from the Data tab. Filter icons will appear on each header row column.
3. Filter all Blank cells. Click the filter icon from any column. In the dropdown menu, click Clear, then check the (Blanks) option. This will sort all the blank cells in the range you chose.
4. Highlight blank rows.
5. Right-click on any one of the highlighted cells and click Delete rows. The spreadsheet will look empty.
6. Select Turn off filter from the Data tab. This will display the rest of your data.
