Our editors pick the products and services we write about. When you buy through our links, TechRepublic may earn a commission.

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.

Video: How to quickly delete blank rows in Excel

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.

excelblankrows-fig1-12002.jpg

After selecting the data range in Excel, 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]+-.
  5. Excel will delete the blank cells from the selected data range.

    LEARN MORE: Office 365 Consumer pricing and features
    excelblankrows-fig1-12002.jpg

excelblankrows-fig2-12003.jpg

excelblankrows-fig3-12004.jpg

excelblankrows-fig4-120051.jpg

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!

SEE: 10 Excel time-savers you might not know about (free PDF) (TechRepublic)

Submit your question

I answer readers' questions when I can, but there's no guarantee. Don't send files unless requested; initial requests for help that arrive with attached files will be deleted unread. You can send screenshots of your data to help clarify your question. When contacting me, be as specific as possible. For example, "Please troubleshoot my workbook and fix what's wrong" probably won't get a response, but "Can you tell me why this formula isn't returning the expected results?" might. Please mention the app and version that you're using. I'm not reimbursed by TechRepublic for my time or expertise when helping readers, nor do I ask for a fee from readers I help. You can contact me at susansalesharkins@gmail.com.

Editor's note on Feb. 11, 2019: This Excel tip was first published in January 2012. Since then, we have added a video tutorial version of the tip and updated the related resources.

Also see