How To Delete/Remove Blank Rows & Columns In Excel 2010 Spreadsheet

By Aun on Jan 07 2010 10 Comments

Here is a small tip for those using Excel 2010 who want to remove all blank rows in an easy way without having to remove each blank row manually. Please note that this tip applies to removing multiple columns as well.

First select the portion of the spreadsheet from where you wish to remove the blank rows and then hit the Home tab. Here navigate to the Editing options and choose the Go To Special option under the Find & Select option.

Go To Special

In the Go to Special dialogue box, choose Blanks and hit OK.

Blanks Excel 2010

All the blank rows will be selected.

Now choose the Delete Sheet Rows option under the Delete drop down box.

Delete Sheet Rows

If you want to remove blank columns, select the portion of the spreadsheet from where you want to delete the blank columns, follow all steps as listed above and finally in the end select the ‘Delete Sheet Columns’ option.

For more, learn how you can delete duplicate rows in Excel.

  • GVENO

    I followed your instructions and all rows were deleted, not just the blank ones.

  • Steve

    Excellent. Worked exactly as you said. Thanks

  • Gopi

    It works. Thanks for the tip.

  • Brian

    Appears to only work if there are no blanks in a given row, otherwise any row containing one blank cell gets deleted. Anyone know of a way to delete only those rows that are completely blank and leave the rows with some content and some blank cells?

    • Kate

      Seek out asap-utilities.com. It has a delete all blank rows command that deletes all the blank rows in 3 EZ selections, with no “editing,” as in the command above.

  • rizwan

    what if i want to delete only those cells which are completely blank instead of partially blank ones??? need urgent help

  • LL

    Was going nuts trying to delete MANY hidden blank columns, great tip, worked like a gem, thanks!

  • kjk

    2010 didn’t delete rows. Vertical scroll bar still goes to Row 1048576. Even after save, exit, reboot, restart and re-open.

    I think there is a bug.

  • Big D

    DANGER! DANGER, WILL ROBINSON, DANGER! DO NOT USE THIS IF YOU HAVE ANY ROWS IN THE SELECTED AREA THAT ARE ONLY PARTIALLY BLANK!

    This works . . . BUT . . . it will also remove rows in the selected area that have ANY blank cells in the row . . . if 1 or more cells has information, BUT there is ONE BLANK CELL . . . IT WILL REMOVE THE ENTIRE ROW . . . you can lose lots of information.

  • evelynm

    There is not a shortcutkeys or quick command to delete all the blank rows in Excel. Though the steps are provided in detail above, if you can find a way to delete them in on click, that will be great. Kutools for Excel is the Excel application i get in the search. It provides a button to delete blank rows, very fast and intuitive. Besides, it is a collection of tools that will deal with some repetitive work in Excel, such as rename multiple sheets, sort sheets , and so on. I am still keeping in trying the app.