Delete All Empty Rows or Blank Cells from a Range in Excel

Add to Favorites

How to quickly delete all empty cells or rows from a range in Excel. 

This allows you to quickly clean your data to remove worthless blank sections that only serve to annoy you and break other features in Excel, like using the quick-fill handle to copy formulas down a column.

Steps to Remove Blank Cells or Rows from a Range in Excel

  1. Select the column or row that contains the blank cells.  You do not need to select the entire data range or worksheet.
  2. Go to the Home tab, then Find & Select and then Go To Special... (If you are in Excel 2003, hit Ctrl + G and then Alt + S [or just click the Special... button after you hit Ctrl + G])
  3. Select Blanks and then hit OK.
  4. Now, only the blanks from the range that you selected will be highlighted:
  5. Right-click one of the selected cells and then click Delete.
  6. In the window that opens, select either Shift cells up or Entire row.

    Shifting cells up will just remove the blanks from the range that you selected.  This works well if you have a data table on one side of the worksheet and other data on the other side and you don't want to mess-up the other data; in this case, you would select the entire data table that contained blanks before going to "Go To Special" instead of just selecting a single column.
    Entire row will remove the entire row from the worksheet, regardless of what is off to the right or left of the range that you selected.
    The other two options work the same way but for removing columns instead of rows.
  7. In this case, I will select Entire row and then hit OK. Here is the result:

    Now, we have a nice, neat, and organized set of data that does not contain any blanks or empty cells or rows.

Notes

If you have a lot of data all over your spreadsheet, including to the right or left of the data that has blank rows, and you don't want to interfere with the other data, simply select the entire data set from which you want to remove rows before going to select the blanks and then delete them.  In this case, select the "Shift cells up" feature from the Delete window.  If you accidentally mess-up adjacent data in the spreadsheet, just hit Ctrl + Z to undo the mistake.

This is a simple but powerful method for cleaning large sets of data quickly and it will save you a lot of time.

Make sure to download the accompanying spreadsheet so you can try this out in Excel.


Downloadable Files: Excel File

Similar Content on TeachExcel
Remove All Data Validation from a Cell in Excel
Macro: Remove all data validation from a cell in Excel with this free Excel macro. This is a...
Formula to Delete the First or Last Word from a Cell in Excel
Tutorial: Excel formula to delete the first or last word from a cell. You can copy and paste the for...
Require a Unique List of Numbers in a Range in Excel
Tutorial: I'll show you how to require a user to enter a unique number into a range of cells in Exce...
Get the First Word from a Cell in Excel
Tutorial: How to use a formula to get the first word from a cell in Excel. This works for a single c...
Format Cells as a Fraction in Excel Number Formatting
Macro: This free Excel macro will automatically format a selected cell or many selected cells in ...
Format Cells as a Percentage in Excel Number Formatting
Macro: This free Excel macro formats a selection of cells as a Percentage in Excel. This sim...