July 31

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? 

Jordan Goldmeier
Latest posts by Jordan Goldmeier (see all)



You may also like

April 9, 2011

How to Create a Rollover Effect in Excel: Execute a Macro When Your Mouse is over a Cell

October 26, 2014

Monte Carlo Simulation Formula in Excel – Tutorial and Download

September 17, 2012

The Excel Rollover Mini FAQ
  • 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!

  • {"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}

    Free Power User Quick Guide

    Discover The BEST Ways To Use Lookups And Conditional Calculations Quickly And Easily With This Reference Guide -- You Won’t Want This To Leave Your Side

    With so many ways to use Excel, it can be difficult to memorize all of the key functions, calculations, and techniques you can employ to meet your goal: simply get the job done.