How to Delete Empty Rows in Excel

When importing and copying tables into Excel, you’re likely to end up with a lot of empty rows/cells. Blank rows aren’t bad, but in most sheets, they can be very annoying. They make it difficult for you to navigate data around and they inhibit many built-in Excel table tools from recognizing your data range correctly.

If you only have a few empty rows, you can easily delete these blank rows manually, but if you are dealing with hundreds of blank rows scattered all over the dataset, it will take you forever to delete all of them. However, there are much easier and fast ways to do it. In this tutorial, we will provide some guidelines on how to delete empty rows in Excel.

Manually Deleting Blank Rows in Excel

The easiest way to remove blank rows is to select the blank rows manually and delete them. If you only need to remove a few blank rows, the manual way is the quickest way to do it.

Select a row by clicking on the row number on the left side of the Excel window. To select multiple rows, press the Ctrl key and click on the row numbers.

Then in the ‘Home’ tab under the ‘Delete’ drop-down in the Cells group, press ‘Delete Sheet Rows’. Once you click this option, it will delete all the selected blank rows in excel.

Or you can also right-click anywhere in the selected rows and select ‘Delete’ in the context menu. It will remove the selected blank rows and the rows below the deleted rows will move up.

How to Quickly Remove Blank Rows in Excel Using ‘Go To Special’ Tool

If you have a spreadsheet containing hundreds of blank rows deleting them manually is a time-consuming process. However, there is a much faster way to do this. You should use the Find & Select feature to quickly select all empty rows and eliminate them at once.

First, select the entire data set or a specific range of the data from which you want to delete blank rows.

Then go to the ‘Home’ tab, click the ‘Find & Select’ option and choose ‘Go to Special’.

In the Go To Special dialog box, select ‘Blanks’ and press ‘OK’.

This will select all the blanks rows in your spreadsheet at once. Now it’s easy to delete them.

Next, right-click on any of the selected cells and select ‘Delete’ from the context menu.

In the Delete dialog box, select ‘Entire row’ and click ‘OK’.

You can also choose the ‘Shift cells up’ option, this won’t delete the blank rows but makes the non-empty rows move up into the empty cells.

This will remove all the blank rows from the data set.

Or once the empty rows are selected navigate to Home > Delete > Delete Sheet Rows. This will give you the same result.

Remove Blank Rows in Excel by Using Filter Functionality

All of the above methods will help you remove any row with blank cells. But Excel sheets can have rows where only some rows are fully empty while others have some non-empty cells in them. So you have to use the Filter function to delete only the rows with all blank cells but save the rows which have both data and blank cells.

Select the data range and on the ‘Data’ tab, click the ‘Filter’ icon in the Sort & Filter group.

Or you can also select the filter option by using a keyboard shortcut:Ctrl+Shift+L.

After this select, all the data columns will have drop-down buttons.

Select a column and click the arrow within the column header and uncheck the ‘Select All’ checkbox, scroll down to the end of the list and check the ‘Blanks’ checkbox, then press ‘OK’. And repeat this for other columns.

Doing this will hide all the blank rows in the dataset, without actually deleting them.

Then, the rows numbers of the empty rows will turn blue.

Select the filtered rows, right-click, and select ‘Delete Row’.

Then, go back to the ‘Data’ tab and switch off ‘Filter’.

You will notice that the rows with a few blank cells remain, but the entire blank rows are deleted.

Delete Blank Rows by Using Excel Find Functionality

Find and Replace is similar to the ‘Go To Special’ command. The Find function finds all empty cells in the data and provides a quick way to delete them.

First, select your data set, and in the ‘Home’ tab under the ‘Find & Select’ option click on ‘Find’.

You can also press Ctrl + F to open the Find and Replace dialog box.

In the Find dialog, leave the Find what field blank and click, the ‘Options’ button.

Then, select ‘Values’ from the ‘Look in’ dropdown and click ‘Find All’. Leave Within and Search fields with defaults ‘Sheet’ and ‘By Rows’.

Once you click the ‘Find All’ button, all the blank rows will be displayed at the bottom of the dialog box. Press CTRL + A to select them all and then click ‘Close’ to close the box. Then without clicking anywhere go to Home > Delete > Delete Rows.

Now all the selected rows will be deleted.

Delete Blank Rows in Excel using COUNTBLANK Function

The COUNTBLANK function in Excel allows you to count the number of blank cells in a range. You can use this function if you have a dataset with numerous blank cells in multiple columns, and you need to delete only those rows with all empty cells or rows that have a certain number of empty cells.

The syntax of COUNTBLANK function:

=COUNTBLANK(range)

The following table is an example where you might want to count the number of dates the sales managers didn’t have any sales (blank cells):

The following COUNTBLANK formula returns the number of cells that are empty (i.e. cells with nothing in them) B2:G2:

=COUNTBLANK(B2:G2)

Enter this formula in cell H2 (temporary column – Blanks). As you can see, there are 2 blank cells in the row (B2:G2), so the formula returns 2 as the result.

Copy the formula throughout the entire column using fill handle.

If the formula returns ‘0’, it means there is no blank cell in the row. Also, the formula always returns the highest number against the fully empty rows.

Next, we apply a ‘Filter’ to the ‘Blanks’ column to remove rows with all empty cells and rows with 4 or more empty cells. To apply filters, select the range and go to Data > Filter.

We want to remove the sales managers who have made sales for only 2 or fewer days or no sales at all. So, click on the down arrow in column H. In the filter menu, uncheck 4, 5, 6, while ensuring 0,1,2 and 3 remain checked and click ‘OK’ to apply.