MVP #85: How To Select, Delete or Replace All Blank Records In Excel

First, I have got to thank my fellow Excel consultant, Olukunle Babajide, for opening my eyes to this interesting use of Go To Special. Seeing him do it was a lightbulb moment for me and I have used it to solve many issues that my usual method would have taken me many extra steps to fix.

What do you do when you have blank rows in a table of thousands of rows? That is what I usually have when I copy daily stock price or Crude Oil price from the Bloomberg Terminal into Excel, the weekend days come up as blank rows. Or in your case, it could be a malformed table someone sent you and you need to remove those blank rows.

Well, today is your lucky day! I will be sharing with you a neat and quick way to get it done. Below is a snapshot of the table I will be using for illustration and as you can see, it has a few blank rows.

To clean off (delete those rows), I select the entire messed up table and navigate to Find & Select, Go To Special.

In the Go To Special dialog box that pops up, select Blanks.

And you'll see all the Blanks selected. Now you can use Delete Cells or the keyboard shortcut (CTRL + -) to wipe off those rows by indicating Shift cells up.

And voila! It is done.

And rather than deleting those blank records, you can also replace them with any values of your choice all at once, using CTRL + Enter.