Software

A quick way to delete blank rows in Excel

Deleting blank rows in an Excel data range is easy with this technique, but watch out for unintended consequences.

Blank rows aren't bad, but in most sheets, they're definitely undesirable. Excel uses blanks to determine data ranges, and a blank row in the wrong place will inhibit many built-in features. Fortunately, there's an easy way to remove blank rows from a data range, but this easy technique has the potential to destroy data, so you must be careful.

The first step is to select the data range. Using the simple sheet below (Figure A), you'd select A3:E14. As you can see, there are two blank rows (7 and 10). Admittedly, in a sheet this simple, you'd probably just delete the rows manually. That won't be as simple in a large data range that comprises thousands of rows!

Figure A

After selecting the data range, continue as follows:

  1. Press [F5].
  2. In the resulting Go To dialog box, click Special.
  3. Click the Blanks option and click OK. Doing so selects the blank cells (what you might think of as rows) in the selected range. In this case, that's A7:E7 and A10:E10.
  4. Now you're ready to delete the selected cells. On the Home tab, click the Delete dropdown in the Cells group and choose Delete Cells. Excel will display the Delete dialog box, with the Delete Cells Up option selected. Click OK. Or, press [Ctrl]+-. Excel will delete the blank cells from the selected data range.

Choosing Delete Cells deletes only the blank cells in the previously selected range. If you choose Delete Sheet Rows in step 4, you could potentially destroy data (often unseen) to the right. Choose carefully when using this option to delete blank rows when you really want to delete just the blank cells. It's easy to think in terms of rows and choose the wrong option!

About

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

4 comments
bhudding
bhudding

BAD BAD BAD... This is absolutely not how to delete blank rows.  It is how to delete blank cells and those are two vastly different things.

Rob-dawg!
Rob-dawg!

I agree with jbenton@... Additionally, if you need to end up with the rows in the original order, then here's what I'd do. 1. Add another column temporarily and populate it with integers 1 through n (the number of rows in the sheet having data). 2. Sort on column A:A. 3. Delete the rows containing just the temporary integers (they'll be at the top or bottom). 4. Sort on the temporary column 5. Delete temporary column. Your data are now in the original row order with blank rows deleted. If someone has a better way to accomplish this, then please post it. I love learning better/best ways to do stuff.

jbenton
jbenton

only going to work if you can guarantee that there are no other blank cells and that all the apparently blank ones really are blank what I would do in this case is to sort entries by column A:A

jbenton
jbenton

to avoid sorting altogether, you could add an extra column COUNTAing the no of entries in each row, autofilter for those = 0, select and delete, remove autofilter (I've added the "filter by current cell" and "toggle autofilter" buttons to my toolbar for situations like this) a few caveats: this will always delete the ENTIRE row; make sure you use COUNTA(), not COUNT() or you'll delete all the rows with only non-numeric data; deleting non-contiguous rows using autofilter can be slow for large lists

Editor's Picks