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.”
Then choose “Entire row,” and the whole row will be deleted:
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.
Step 2: In the next list that appears, select “blanks” and then “Ok”
By doing so, you’ll see that all the blank cells are selected
Step 3: click “delete” in the “Cells” group which is also 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.
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.
Then 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.
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.
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.”
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.