Hardly, you will ever get a dataset or an Excel spreadsheet that does not require many things to do. Otherwise, people always have to set, organize and manage the whole data in the sheet to make it look presentable. The data on a sheet could have errors, unwanted values and entries, extra cells or pages, hidden values, and much more that you need to reset carefully.
Besides most things, you must understand how to remove blank rows in Excel when you have no other choice except to remove unwanted empty rows. Removing blank rows is mandatory, especially when you the data to be in the best working mode. A small dataset can be arranged easily, whereas a large dataset needs time and effort. And you cannot manually delete rows one by one from the sheet.
For this, we have found some useful hacks, so that you can conveniently remove blank rows in Excel.
How to Remove Blank Rows in Excel Manually
The very first method you will see here is how you can manually delete those empty rows from the sheet. Hearing the term manual might upset you, but don’t worry this method is neither lengthy nor difficult. Removing a couple of rows is easier with a manual approach. Let’s dive into it:
Choose the blank rows you need to delete. Press and hold the CTRL key from the keyboard and click on the row to select it.
After selecting rows, now right-click and choose the Delete option given in the menu.
Apart from this, you can even delete rows with the ribbon command. Open the Home tab and choose the Delete option and then click on Delete Sheet Rows option.
Furthermore, you can try a keyboard shortcut as well. using this shortcut you cannot remove rows only but you can also remove columns and cells.
Press CTRL + – on the keyboard and that’s it.
You will see the desired outcome.
Let’s move on to our next method.
How to Remove Blank Rows in Excel with the Context Menu
With the help of the context menu, you can easily remove many adjacent rows all at once. Let’s find out the steps:
With a mouse, drag across the row headings or you can choose the first row heading. Now, press SHIFT and click the last row heading.
Right-click on the row headings and you will see a drop-down menu.
Choose the Delete option.
When you need to delete multiple non-adjacent rows with a context menu, you will have to follow these steps:
Firstly, select the non-adjacent rows that needed to be deleted. Click on the first row heading and then press CTRL–click the headings of other rows that you need to select.
Now, right-click on the row headings and you will see a drop-down menu. Choose the Delete option.
That’s it!
How to Delete Empty Rows by Sorting in Excel
You must sort down the data so that you can get an idea of what kind of values are there in rows and columns. Undoubtedly, when you sort the data all the empty rows appear at the end of the sheet and you can get rid of them easily. Below are some easy-to-follow steps:
Choose the whole data range.
Click on the Data tab from the Ribbon.
Choose Sort from the Sort & Filter menu. You will see a dialog box from where you need to select My data has headers.
Moreover, select the field or column that does not have any data and choose the right sorting option, for instance, A-Z, smallest to largest, ascending to descending, etc.
Click OK and all blank rows will appear at the end of the dataset.
How to Delete Rows with Keyboard Shortcut
Using keyboard shortcuts is the all-time favorite thing for Excel users because it helps them save time and improve productivity. One keyboard shortcut is mentioned-above, here you will see another shortcut command.
Using the below-given keyboard shortcut command, you can remove multiple adjacent rows:
Choose the first row heading and press SHIFT – click the last row.
Press CTRL + – to delete the selected rows.
On the other hand, when you want to remove multiple non-adjacent empty rows, you can follow the below-given command.
First of all select non-adjacent rows and click on the first row heading. Press CTRL–click to select the other rows.
Press CTRL + – to delete the entire selection of rows.
That’s it!
How to Delete Blank Rows with Go To Special
You can delete a few rows manually as you can see the method is purely justified. What if your data contains many empty rows side by side or randomly? You cannot perform this task manually because it could be a real headache. Well, don’t worry we have come up with another useful method to remove many rows.
Choose a column that contains empty rows, for this select the first cell and then press, and hold the SHIFT key. It will select the last cell in the row.
Now, open the Go To Special command and select all the blank cells. Click on the Home tab and then choose the Find & Select option. Now click on the Go To Special command given in the menu.
You can use a keyboard shortcut as well for the Go To menu. Press CTRL + G and it will open the GO To menu. Now, click on the Special option and it will open up the Go To Special menu.
Choose the Blanks option and then press OK.
Now, delete all the selected rows. For this:
You can use any method to delete selected rows and then right-click on the Delete option.
Click on the Home tab and then the Delete option.
Now, choose Delete Sheet Rows.
Press “+” from the keyboard.
Choose the Entire row given in the Delete menu and press OK.
Within a few seconds, you can easily find and delete unwanted empty rows from the sheet. And it does not matter how many blanks you have in the dataset. Excel lets you do it without any trouble.
