How to Delete Blank Rows in Excel 2016?

Blank rows or cells in a table create a lot of confusion for excel built-in functions to identify the data in a correct manner. So, to clean the data in the table we must delete blank rows present in a table that creates a disturbance while analysing the data.

Well, if you are working with a large table in Excel and you keep on adding or removing entries in a table, definitely there will be blank cells or entire blank rows created in the table. And now, you have to delete blank rows but how?

We will be discussing three methods:

1. Using Go To Special
2. Using Filter
3. Using Formula

Steps to Delete Blank Rows in Excel 2016:

Using Go To Special

Note: Do not use this method to delete blank rows as this also deletes the rows which have some column entries remaining.

Step 1: Select the entire table and click Go To Special…. from Find & Select dropdown menu in the Editing group of Home tab.

Step 2: A Go To Special dialog box will pop out. Here, under the Select section, you have to select Blanks and click OK.

Step 3: The above step would highlight all the blank cells in the table and automatically it highlights the entire blank rows also.

Step 4: Now, to delete the blank rows click the Delete dropdown menu under Cells group of Home tab. It will show some delete options among them click the Delete Sheet Rows.

Step 5: As you can see in the picture below it has also delete those rows which have some column entry remaining. Like, it has deleted the entries of Davis Clawson, Jenny & Annie Rotus which has some column entries remaining. Hence, it is advised not to delete blank rows using this method.

Using Filter

Step 1: Select the entire table from where you want to delete blank rows. Now, to apply a filter for sorting the blank rows, click on Filter in Sort and Filter group of Data tab.

Step 2: You can see that the filter has been applied column headers of your table.

Click on the anyone of your column header filter. For example, I have clicked on the Sales. Among all the entries in the column select Blanks and deselect the other entries and click OK.

Step 3: Now in the picture below you can observe that it has filtered those rows from the Sales column which has a blank entry i.e. row 2, 5, 7, 9, 13.

To delete the rows, select all the blank rows which has no column entries remaining and right-click on it and click on Delete Row.

Using Formula

It is the best method to delete blank rows from the table in Excel.

Step 1: Take a column just next to the last column of your table and name it. For example, I have named it as Blank Cells.

In the next row of the new column type the formula:

=COUNTA(A2:D2)

This formula counts all the non-empty entries of the selected range in the formula.

Drag down the formula till the last row entry of your table. And you will find 0 in the rows that have all column entries empty.

Step 2: Now, apply a filter for column Blank Cells. For that select the Blank Cell column entries and click on the Filter button in the Sort & Filter group of Data tab.

Step 3: Now, click on the drop-down arrow of the Blank Cells column and select 0 and uncheck all other entries and click OK.

Step 4: Step 3, will filter all the rows having 0 in Blank Cell column. Now, you can easily delete all these rows, select all the filtered blank rows and right click and delete using delete row option.

This is all about how you can delete blank rows from a large table in excel. You can explore more method for doing the same.