If there are some blank cells, rows, or columns that make your excel data seems not so easy to read or edit, you can try to delete or move them manually and accurately. But if the excel file is large, and there are several excel spreadsheets in it, probably manual deletion would waste you lots of time. So let's learn to delete or remove blank or empty cells/rows/columns easily, even though it would probably damage your excel data.

How to delete blank cells in Excel?

Step 1: Select the data range you want to delete blank cells.

Step 2: In Home tab, press Ctrl + F to open Find and Replace dialog.

open "find and replace" dialog

Under Find tab in Find and Replace dialog, click the Options to expand all the options you can set when you want to find something in selected excel file.

For example, if you want to find out all the blank cells in selected data range, choose to Look in "Values" and let Find what be blank. Click Find All and all the blank cells are found.

find out all blank cells

Step 3: In results you find, press Ctrl + A to select all of them and click "Delete > Delete Sheet Rows" in Home tab and Cells group.

delete blank cells

Instantly, all of the found cells would be removed or deleted from data range.

How to delete blank rows/columns in Excel?

Two methods will be listed here for you to delete blank rows or columns that you want to remove from excel data.

Method 1: Delete blank rows/columns with Excel command

Step 1: Select the data range that you want to delete or remove blank or empty rows or columns in Excel.

Step 2: Open Go To Special dialog.

1. Press F5 and Go To dialog pops up. Click Special in dialog to open Go To Special dialog.

open "Go To" dialog

2. Click Home tab and Find & Select > Go To Special option in Editing group. Then Go To Special dialog appears.

open "Go To Special" dialog

Step 3: Select or check Blanksoption in Go To Special dialog. And click OK.

select blanks in excel data range

Then you would find in the data range you specify, all of blank cells are selected.

Step 4: Click Delete > Delete Sheet Rows/Delete Sheet Columns in Home tab and Cells group.

Then all the blank rows or columns will be deleted or removed in Excel.

Method 2: Eliminate blank rows by Excel filter functionality

Step 1: Select the range from which you need to remove the blank rows.

Step 2: Click Home > Sort & Filter > Filter in Editing group.

choose to filter selected excel data

Step 3: Select a column and click the filter dropdown, uncheck the values excepting Blanks and click OK.

select the columns to filter data

Step 4: With all the blank rows you select, click "Home > Delete > Delete Sheet Rows" to delete or remove all empty rows.

Related Articles:

  • Remove and Bypass Excel Sheet Protection Password on Workbook
  • How to Remove Restrict Editing in Excel 2007-2016 without Password
  • How to Remove or Change Comment Author Name in Excel 2016/2013/2010
  • 2 Options to Prevent Excel Sheets from Deletion

  1 person found this article useful

  1 person found this article useful