Categories
Excel Training Hints & Tips

How to Delete Blank Rows in Excel

Quickly and easily delete blank rows and columns in Excel

It’s not uncommon to end up with blank cells or rows in an Excel worksheet. If you need to delete these to maintain accuracy, especially when calculating the average value, the quick way is to filter out all blank cells / rows and delete them with one click.

Choose the column you want to filter, go to Data->Filter, after the downward button shows, undo Select All and then pick up the last option, Blanks. All blank cells will show immediately. Now, the blank cells / rows can be deleted easily.

delete_cells_rows
Delete blank rows in Excel

Another way to delete blank rows is to first select blank cells inside the data using the Go To Special feature. It works as follows:

GTS

This tool is opened by pressing F5 or by clicking Home, Find & Select, Go to Special. In the Go to Special box, select the Blanks option, then click OK. You will notice that all blank cells in and around the data are now selected.

SelectedCells

At this point, to delete the empty rows, go to the Home ribbon and click the drop-down of the Delete button in the Cells group. In the drop-down list, click Delete Sheet Rows.

DSR

The data will now have no blank rows inside.

NBR

Use this method only for simple tables with a couple of dozens of rows that fit within one screen. The main reason is that if a row with important data contains just one blank cell, the entire row will be deleted.

Delete blank rows in Excel using COUNTBLANK

Use this method if you have a table with numerous empty cells scattered across different columns, and you need to delete only those rows that do not have a single cell with data in any column.

1

In this case we do not have a key column that could help us to determine if the row is empty or not. So we add the helper column to the table:

Step 1. Add the “Blanks” column to the end of the table and insert the following formula in first cell of the column: =COUNTBLANK(A2:C2).
This formula, as its name suggests, counts blank cells in the specified range, А2 and C2 are the first and last cell of the current row, respectively.

2

Step 2. Copy the formula throughout the entire column.

3

Step 3. Now we have the key column in our table. Apply a filter to the “Blanks” column to show only rows with the max value (3). Number 3 means that all the cells in a certain row are empty.

4

Step 4. Then select all the filtered rows and remove whole rows .
As a result, the empty row (row 5) is deleted, all the other rows (with and without blank cells) remain in place.

5

Step 5. Now you can remove the helper column. Or you can apply a new filter to the column to show only those rows that have one or more blank cells.
To do this, uncheck the “0” checkbox and click OK.

6

7

 Additional Resources

Deleting rows of Data

Delete cells, rows, or columns

By Richard Bailey

I love what I do; I get to work with an outstanding team to help hundreds of people with their challenges. I’ve learnt a lot from the teams I’ve worked with, no matter the size or industry we all have challenges to overcome, difficult customers, creating a budget or keeping a project on track.