How to remove blank rows from a table in Excel

Suppose you have a large table of data that is interspersed with lot of blank rows, how do you remove these blank rows?

One way would be to sort the table. But what if you don’t want to disturb the order of the data.

Well, here’s a quick and easy way to do it.

  • Select one of the columns in the table.
  • Press F5. This will open up the ‘Go To’ window.
  • At the bottom of the window you will find the ‘Special’ button. Click on it and in the resulting window choose the ‘Blanks’ option and click Ok. This will select all the cells which are blank in the column.
  • With the blank rows now targeted, go to the Excel home tab (Excel 2007 & 2010) and click on the small arrow beneath the delete button and from the options choose “Delete Sheet Rows” to delete the blank rows. Alternatively, if you have a older version of Excel, you could right click on one of the selected cells and in the context menu that pops up, click Delete and choose the ‘Entire rows’ option.