When you’re working with large datasets in Excel, chances are you’re going to end up with blank cells or entire rows one way or the other. They can look unsightly and even interfere with your data analysis. Depending on your needs, there are a few excellent ways to remove blank rows in Excel.
Method 1 – Excel Remove Blank Rows With the Filter Command
If you want to remove rows that are entirely empty (i.e. all the cells you need in a row are blank), you can use the Excel filters to highlight them and then remove them.
Step 1. Select the columns that you need to filter (those that have data in them). You can do that by clicking and dragging over the column headers.
Step 2. With the columns selected, click on the “Filter” option in the “Data” tab.
Step 3. The first rows with data (table headers) will get dropdown icons. Click on the dropdown in the first header.
Step 4. In the box for filtering items, uncheck “Select All” then check “Blanks,” then click on “OK.”
The table now displays only the empty values in the first column.
Step 5. Repeat the filtering process for all other columns, selecting to show only blank cells every time.
The rows marked in a light blue color for their row numbers are the rows you need to delete.
Step 6. Select the rows that you want to delete by clicking on the first blank row’s header and dragging down.
Step 7. Press the “Delete” button or choose the “Delete Sheet Rows” option in the “Home” tab.
Step 8. Select the “Filter” option in the “Data” tab again to undo the filter and display your table without empty rows.
Method 2 – Excel Remove Empty Rows that Contain Some Data with Go To Special
The first method is perhaps the most versatile as it allows you to selectively check each column that needs to be removed. However, if you have a large dataset and want to delete all rows that have any blank cell in them, there’s a faster way. Note that this will remove all rows that have at least one blank value, so make sure the table has all the correct information.
Step 1. Select the table that you want to filter by clicking and dragging from one of its corners to the diagonally opposing one.
Step 2. In the “Home” tab, under the “Find” menu, choose “Go to Special” or use the keyboard shortcut “Alt + H + F + D + S.”
Step 3. The “Go To Special” menu will pop up. Choose “Blanks” from the left-hand side and select “OK.”
Step 4. You should see a selection of empty cells within the table. Select “Delete Cells” in the “Home” tab and choose “Delete Sheet Rows.”
This will remove all the rows that have at least one cell selected. You can undo this action immediately if you made a mistake, but will have to go to version history if you happen to catch an error later.
Thanks for your feedback!
