An Alternate Tip: Removing Blanks Rows from a List in a Spreadsheet

You were handed a spreadsheet of exported data from some legacy system. The data aren’t in one continuous list but rather blank rows appear randomly throughout. You’d really like to get rid of those blank rows.

This isn’t a new problem for Excel. The most common approach to remove these blanks is to:

  1. Select the first data column.
  2. Click Find & Select from the Home Tab
  3. Click Go To Special, select Blanks then hit OK.
  4. Click Delete Sheet Rows from the Delete dropdown on the Home tab.

In fact, ExcelAddict.com called this method the fastest way to solve this problem. 

Well, I think I might have a faster method, if only because it has one to two fewer clicks (but no fewer steps). My method also has a potential fatal flaw, which I’ll go into in a moment.

Here’s how my alternative method works:

  1. Insert a blank row above the data range.
  2. Select the entire data range including the blank row at the top.
  3. Click Remove Duplicates from the Data tab. 
  4. Delete the blank row at the top. 

This works because Excel considers all those blank rows to be duplicate rows. 

(Remember, Excel will consider one blank row as unique among the duplicates. If you don’t put that blank row at the top, a blank row will appear somewhere in your data range.)

Pretty neat, right? So here’s the obvious flaw: if you have rows filled with duplicate data — and you need to keep those rows in the dataset — then this method won’t work. All but one of those rows will be deleted. 

In practice though, I haven’t run into a problem where Remove Duplicates was the wrong choice. Usually, I have blank rows in my data range because my data was exported from a database. And, because it came from a database, it also usually has a primary key (which makes each row unique). 

OK, so why use Remove Duplicates over deleting spreadsheet rows? Well, the first reason is that deleting rows from a noncontiguous range appears to be an irreversible action. In other words, you can’t undo it if you make a mistake. CTRL+Z however will reverse a Remove Duplicates operation.

The second reason is that my five minutes of (and admittedly unscientific) experimentation suggests Remove Duplicates is faster. No rows are deleted when using Remove Duplicates resulting in a quicker operation. 

What do you think? 

Advertisements

3 thoughts on “An Alternate Tip: Removing Blanks Rows from a List in a Spreadsheet

  1. Pingback: Remove blank rows from a list | Godick

  2. Bryon Smedley

    Unless I’m missing something, the only fault I can see with this method is that if you have duplicate entries in your data that you do not wish to alter, the Remove Duplicates technique has the potential of altering the data in an undesirable way.

    Reply
    1. Jordan G Post author

      Right, well, this technique is squarely aimed at those who want to remove duplicate entries. Obviously, I wouldn’t recommend this if you want to keep them…. but say you change your mind right after your remove duplicate entries – this method will allow you to get back to them with a simple undo!

      Reply

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s