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:
- Select the first data column.
- Click Find & Select from the Home Tab
- Click Go To Special, select Blanks then hit OK.
- 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:
- Insert a blank row above the data range.
- Select the entire data range including the blank row at the top.
- Click Remove Duplicates from the Data tab.
- 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?