How To Delete Empty Rows In Excel

Share on facebook
Share on twitter
Share on pinterest

Sure, Excel is a great tool for listing, calculating, and storing your data. However, sometimes blank rows and columns in your list make it harder to focus on data and might cause some errors in your calculations. In this blog, we’ll teach you how to delete blank rows in Excel, and also how to preserve those rows which have data but include blank cells too. We’ll follow the instructions on Excel 2016, but they will work on other versions of Excel too.

If you want to delete a row, any row, in Excel, you can simply right-click on one of the cells, and in the drop-down list that appears, click on “Delete.”

right-click on one of the cells, and in the drop-down list that appears, click on “Delete.”

Then choose “Entire row,” and the whole row will be deleted:

after clicking on delete, choose “Entire row,” to delete the entire row

You can delete multiple rows just like this. Select all the rows that you want to delete, right-click on the selected area, and hit “Delete,” or simply press the Ctrl and Minus button.

There are other ways for deleting blank rows and cells in Excel. First, we are going to explain one of the easiest ways of deleting blank rows in Excel, and then we will cover one of the complete but longer methods which is very useful. We have shown both processes step by step, so that you can easily follow it up.

The Quick way to Delete Blank Rows In Excel

You can do this easily by following these few steps:

Step 1: Click on the “Find & Select” button on the Home tab and select “Go To Special” in the list that appears.

Click on the “Find & Select” button on the Home tab and select “Go To Special”

Step 2: In the next list that appears, select “blanks” and then “Ok”

select “blanks” and then “Ok” in the Go To Special list

By doing so, you’ll see that all the blank cells are selected

How to delete empty rows in Excel, quick method.

Step 3: click “delete” in the “Cells” group which is also in the Home tab and then click on “Delete Sheet Rows”

click “delete” in the “Cells” in the Home tab and then click on “Delete Sheet Rows

This method helps you delete any empty cell but there is a problem here. As you can see in the picture below, both the blank rows and the rows with some blank cells are selected and all of the blank cells are removed.

both the blank rows and the rows with some blank cells are selected and all of the blank cells are removed

This causes big errors in your data and mixes up every single row that has a blank cell. To prevent this, you should try the complete method that we have explained in the next paragraph. 

The Ultimate Solution To Delete Empty Rows In Excel!

By following these steps, you can delete any blank row in Excel without losing your data, messing your list up, or going through much trouble!

First, select all the columns that you want to remove blank rows from. In the example, you can see that columns A, B, C & D are selected. 

select all the columns that you want to remove blank rows from

Then go to the “Data” tab and click on “Filter.” 

go to the “Data” tab and click on “Filter.”

Then click the arrow on the column header; here you should uncheck the option “Select All” and check “Blanks” so that the cells containing data won’t be included.

click the arrow on the column header and uncheck the option “Select All” and check “Blanks”

As you can see, after choosing the filter, I clicked on the arrow on column D. After clicking on “ok,” you’ll see that only rows with blank cells on column D that have data on other columns are left.

only rows with blank cells on column D that have data on other columns are left.

Do the exact same thing for other columns too. Then, select all the rows you have filtered, go to the “Home” tab,  in the Cells group, click on the arrow under the “Delete” option and select “Delete Sheet Rows.”

select all the rows you have filtered, go to the “Home” tab, in the cells group, click on the arrow under the “Delete” option and select “Delete Sheet Rows

The last step is to hit “Clear Filter” in the “Data” tab, and that’s it! All the blank rows are gone safely. By the way, in some of the versions of Excel, like Excel 2016, the filter is cleared after deleting the rows and there is no need to do this last step.

Subscribe to our Newsletter

Share this post with your friends

Share on facebook
Share on google
Share on twitter
Share on linkedin

Leave a Reply

Your email address will not be published. Required fields are marked *