If you import data from other systems or a user placed blanks rows so that it looked “nice”, you’ll invariably have to manipulate it get it the way you need before you can analyse it especially if you are going to use the Subtotal or Pivot Table. Data interspersed with blank cells/rows is one of the most common problems. Thankfully there is an easy method to get rid of these, like the blank rows below, in seconds. Imagine a large spreadsheet similar to one below:

How to Remove Blank Rows with Go To,
Special Command

  1. Select range of data containing spaces. Taking the example above just the cells A4:A34 (the first column will do).
  2. CTRL+G to open the Go To dialog box.
  3. Click on Special… button
  4. Choose Blanks and Click OK

Now the table should look like this:

 

To proceed with deleting the rows:

  1. Right click on the one of the highlighted cells
  2. Select Delete…
  3. Select Entire Row

Now your table is rid of blank rows!

Advertisements