How to Delete Empty Rows in Excel

You may have come across a situation in which there are hundreds of Blank rows scattering in between your work area on the spreadsheet. This becomes frustrating when you need to apply some filters/functions over data or do some analysis over the same.

Well, no need to get frustrated. In this post, we will tell you an easy way to delete empty rows in excel. Removing blank rows one by one can be a solution to this issue only if the dataset we are working is a smaller one.

Refer to the image below:

In the above image since we have a smaller dataset, we can manually select the Blank rows by holding Ctrl Key and selecting Rows 5,7,9.

Now, once the blank rows are selected, we can click on Delete Sheet Rows/Cells option under Cells Group in Home Tab. This will delete the blank rows.

Refer to the image below:


Imagine a dataset where there are thousands of rows, and there are blank rows between data scattered randomly. This will inhibit you to apply any formula to entire rows at once, or use the drag feature since excel treats blank rows to define range limit. For this case, we will use a built-in feature of excel (by selecting the data range and pressing F5 to open the Go To menu thereby selecting blanks) to select all the Blank Rows between the selected data range and delete them instantly.


Example Case for Understanding:


Let us now understand the functionality by taking an example of a Sales Report which lists Salesperson’s Name, along with their monthly sales revenue for a period of six months.


Refer to the image below:


Observe the blank rows which are present at random intervals in our Sales Report. Applying any formula would be a cumbersome task since excel considers blank row at the end of the data range. So, to avoid this problem we will use the Go To feature under Home Tab- Find and Select Menu, Editing Group.

Refer to the image below:


Select your entire data range and then Click on Go To Special menu item which will open a dialog box.

Refer to the image below:


Now, you need to tick the option stating Blanks as shown in the above image, and press Ok. You will observe that all the blank rows in the selected data range have automatically been selected.

Refer to the image below:


Now you need to right click on any row and select the Delete Sheet Rows from the Delete button under Cells Group.

Refer to the image below:


Note: On using Delete Sheet Rows option you should ensure that there is no data on that entire row even out of your work area, else it will get deleted.

If you are unsure about that you can use the Delete Cells options just above the Delete Sheet Rows menu item.

Refer to the image below:


As you can observe the above image, there is text cell I5, I7, I9. Now, if we use the option of Delete Sheet Rows, our data will also get deleted. So we need to take precaution in such cases, and use the option of Delete Cells, where you can further select the action such as Shift cells up or left or delete rows, etc. In this case, we can use shift cells up to delete blank cells and even retain the text usually unseen on the right-hand side of the sheet.


Refer to the image below:


As you can observe the above image, all the blank rows have been deleted from the work area, and now we can easily apply any formula to the dataset.

Once you have selected the data range of your work area including Blank Rows, you can also press F5 Key which will open a dialog box as below.

Refer to the image below:


You need to click on the Special button on the lower left corner of the dialog box which will take you to the same menu of Go To Special dialog box where you need to select blanks and click OK.

I hope you understand this feature now and do not forget to try out yourself for sample cases as described in the post.

Please comment for any queries you have or any valuable suggestions.

Happy Learning!



Tags: No tags

Add a Comment

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