Delete Blank Rows in Excel: Search and Destroy

exceldeleteblankrowsI don’t know what’s more disheartening: wasting time in Excel, or learning how easily you could have avoided it.  If you’re not careful, you could get lost in virtual spreadsheets forever.  But today I’m going to trim the list of potential death traps by one and teach you how to delete blank rows in Excel, no matter how many exist or how distantly they are spaced.  Just try not to die when you see how easy it is.

If you want to ensure that Excel never, ever gets the best of you again, here’s a great way to master Excel in no time flat.

To begin, you will need a typical spreadsheet.  Ideally, something you’ve been working on for months and which, in the name of progress, you have neglected to maintain as you push forward.  Doubtless there will be hundreds, if not thousands, of empty rows just waiting to turn your formulas into hash-tags.  It might look something like this (note: unless you’re running a version of Excel from the 90s, this is a universal fix):

Screen Shot 2013-12-12 at 6.52.40 PM

The first thing you will want to do is select the region from which you would like to eliminate blank rows.  In my case, I’m going to select everything visible.  Now, click Edit on the title bar and select Go To.  A dialogue box will appear:

Screen Shot 2013-12-12 at 10.40.28 AM

Click Special.  Another dialogue box will appear.  Select the empty bubble next to Blanks and press OK:

Screen Shot 2013-12-12 at 10.40.52 AM

This will select all the blank cells in the region that I selected at the beginning:

Screen Shot 2013-12-12 at 6.55.02 PM

Pretty cool, right?  All that is left to do is go back to Edit on the title bar and click Delete.  Before all those blank rows vanish in a glorious blink of the eye, you will have the option to select how you would like the table to react once the cells are gone: shift cells up, shift cells down, etc.  Only you can decide that one.

Well, that does it.  You’ll sleep well tonight knowing you’ll never again have to hunt down blank rows (or cells, or columns, for that matter) one by one.  Don’t like to sleep?  I’d call you crazy, but amazingly you’re not alone.  Here’s a top-ranked tutorial to help you get more energy, with less time in bed.