• Around The HomeToggle Menu

    • Entertainment
    • Productivity
    • Smart Home
  • FamilyToggle Menu

    • Parenting
    • Toys
    • Pets
    • Travel
  • Product ReviewsToggle Menu

    • Phones
    • Tablets
    • Laptops
    • Desktops
    • Wearables
    • Audio
    • Cameras
    • Headphones
    • Printers
    • Smart Home
    • TVs
    • Gaming and Video
  • One Cool ThingToggle Menu

    • Frugal Tech
    • Kickstarters
    • Videos
Techwalla
  1. Home
  2. Around The Home
  3. Productivity
  4. How to Delete Blank Rows in Excel

How to Delete Blank Rows in Excel

By: Aaron Parson
  • Share
  • Share on Facebook

In Microsoft Excel, the "Del" and "Backspace" keys erase the contents of the current selection, but do not erase the cells themselves. In most cases, this distinction doesn't make a difference, but to get rid of extraneous blank rows, you need to delete the actual cells. Excel 2010 and 2013 have several methods for finding and deleting blank rows -- some more automated, others better suited for handling complicated spreadsheets. Before starting, especially when working with an automatic method, save a backup copy of your workbook in case you later discover that you erased important data.

The table with number  , stock market analyze.
Empty spreadsheet rows can inflate Excel file size.
credit: gibgalich/iStock/Getty Images

Manual Removal

To delete any row by hand, right-click the row heading -- its number on the side of the spreadsheet -- and choose "Delete." To erase multiple rows at once, hold "Ctrl" and click each row's heading to select them. Right-click anywhere on the selected rows and click "Delete" to remove all of the rows. To speed up the process when looking through long spreadsheets, delete one row normally, then as you select each subsequent row, press "Ctrl-Y," which repeats the last action. If you make a mistake, press "Ctrl-Z" to undo the error.

Video of the Day

Delete Table Rows

Deleting an entire row works even if your data is in a table, but if you have data both inside and outside the table, you might need to delete the row from the inside exclusively. To remove blank rows in the table, hold "Ctrl" and click one cell in each row you want to remove. On the Home tab, click to open the drop-down menu beneath "Delete" in the Cells section and choose "Delete Table Rows."

Filter Blank Rows

Turning on filters helps identify blank rows in spreadsheets where a few blank cells might not indicate a completely empty row. If you're working in a table, you already have filters enabled. Otherwise, select your data, open "Sort & Filter" in the Editing section of the Home tab and choose "Filter." Click the drop-down filter menu on your first column, uncheck "(Select All)" to remove all of the checkboxes and then check "(Blanks)." Press "OK" to see only the rows where the first column is blank. If some of the rows still have data in other columns, repeat the filter in each column until you only see completely blank rows. At that point, select and delete the rows as usual. Reopen "Sort & Filter" and choose "Clear" to remove the blank row filter or choose "Filter" to turn off filtering entirely.

Automatic Detection

Excel includes the ability to automatically select every blank cell in a range of data, making deletion much faster. This method deletes every row that contains any blank cells, however, making it a poor choice for partially filled spreadsheets. Start by selecting your data -- do not select the entire sheet with "Ctrl-A" or Excel will freeze when trying to search for blank cells. Open "Find & Select" from the Editing section of the Home tab and click "Go To Special." Choose "Blanks" and press "OK" to select every blank cell in the data. Open the "Delete" drop-down menu from the Home tab and choose "Delete Sheet Rows" to erase every row containing a selected cell.

Show Comments

Related Articles

How to Lock & Unlock an Excel Spreadsheet

How to Lock & Unlock an Excel Spreadsheet

Around The Home
Productivity
By: David Weedmark
How to Create Animated Pie Charts in PowerPoint

How to Create Animated Pie Charts in PowerPoint

Around The Home
Productivity
By: Carol Finch
How to Compare Cells in Excel

How to Compare Cells in Excel

Around The Home
Productivity
By: Aaron Parson
How to AutoFit Column Width in Excel

How to AutoFit Column Width in Excel

Around The Home
Productivity
By: Matt Skaggs
How to Find the Most Common Numbers in an Excel Program

How to Find the Most Common Numbers in an Excel Program

Around The Home
Productivity
By: Melissa Kelly
How to Insert a Row or Column in an Excel Spreadsheet

How to Insert a Row or Column in an Excel Spreadsheet

Around The Home
Productivity
By: Carol Finch
  • HOW WE SCORE
  • ABOUT US
  • CONTACT US
  • TERMS
  • PRIVACY POLICY
  • COPYRIGHT POLICY
  • Advertise

An error occurred. Try again later.

Thanks for signing up!
© 2019 Leaf Group Ltd. Leaf Group Media

Get great tech advice delivered to your inbox.

Keep your family productive, connected, entertained, and safe.

Please enter a valid email.