How to Properly Delete Blank Rows in Excel

Data rarely comes perfectly clean, without errors and well organized. Often, you will have to take steps to ensure that your data is in the best working order, One of the biggest issues is having blank rows in Excel that interrupt your data. Of course, if your data set is very small, you can simply scroll and delete each individual row.  However, this is extremely time consuming for large tables.

Here is some data with blank rows in Excel. Follows the steps below to eliminate these spaces.
remove rows from your data

There are spaces that are visible in row  4,5,19 and 32. This can be removed quickly just by eyeballing it. However, try the steps below:

I. Removing Blank Rows with Find & Select

1. Click Find & Select
2. Click to Go to Special
3. Choose  Blanks
4. Click OK and then all the blank rows/cells  will be highlighted
5. Choose the Delete under Cells section on the Home Tab
6. Click Delete Sheet Rows

When you select Blanks, all the blanks rows will be highlighted

You can now delete these highlighted rows by selecting Delete in the Home tab under the Cells section. This action will delete all the blank cells that were highlighted.

II. Delete Blank Rows with Filter

An old school method of deleting blank rows is to simply add a filter and then delete the rows. Follow the instructions below:

1. Highlight your entire data you want to clean.  If you have a large data set you can press CTRL + SHIFT + DOWN to highlight all of the rows and CTRL + SHIFT + RIGHT to cover all the columns.
2. Click the Data menu from the top navigation.
3.Click the filter icon and deselect Blanks. Now you can copy and paste the values over to another tab.
4. Alternatively, you can just select blanks in the filter..
5. Once you have select blanks, you will be left with empty rows, look at the row number highlighted in blue to know where your data stops.

Use filter to delete blanks in excel rows

Once you select only blanks you will be left, with nothing in the rows. You can see these cells highlighted in blue. You can just delete these.

Watch the Video On Removing Blank Rows in Excel

Other Useful Tutorials:
70 Excel Shortcuts