Also, be sure to check out our comprehensive guide to using Excel for more useful tips.
Back up first
As with any editing of important documents you should make a copy of your spreadsheet before beginning. Removing blank rows is a destructive act, and if you mistakenly enter the wrong commands then it could delete info that you wanted to keep.
Can I remove blank rows automatically?
The first thing you need to tell Excel is which areas of the spreadsheet it should include when searching for blank rows. To do this select a data range by highlighting the relevant cells. On this spreadsheet we’ve selected A1 to G22.
Next press F5 and you’ll see a dialog box appear entitled Go To.
At the bottom of the box is a button marked Special. Click this and you’ll be presented with a number of options to choose from. As we’re searching for empty rows the one we want is Blanks. Click this and then OK.
On your spreadsheet you should see that all the empty rows Excel has found are now highlighted in blue. Give them a quick check to see if everything is how you want it. If so, you can remove them.
To do this you’ll need to make sure the Home tab is selected at the top of the screen, then go over to the right and look for the Delete option in the Cells section. Clicking the arrow at the bottom of this opens a dropdown menu from which you’ll need to select Delete Cells rather than the more obvious Delete Sheet Rows.
A new pop-up window will appear asking how you wish to arrange the space left behind by the missing rows. The default is Shift cells up which is the one to choose unless you have specific needs otherwise.
Click OK and the rows will now disappear. You’ll still see the blue highlighted areas, but once you click on any cell they will vanish, leaving you with a new, compact table of data and no empty rows.
Martyn has been involved with tech ever since the arrival of his ZX Spectrum back in the early 80s. He covers iOS, Android, Windows and macOS, writing tutorials, buying guides and reviews for Macworld and its sister site Tech Advisor.
