How to Delete Blank Rows in Excel

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.

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.

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."

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.

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.