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?
- All Excel LOOKUPs Explained - May 26, 2020
- How to: Power Query File From Folder - April 21, 2020
- Oz’s Excel Tip: Keep a Workbook for Random Data in Excel - January 23, 2020
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.
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!