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:
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:
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?
Please log in again. The login page will open in a new window. After logging in you can close it and return to this page.