In a large worksheet, there remains a possibility of having empty rows. From this article, you will get to know how to remove empty rows in excel. The end result will look something like this.
Download Practice Workbook
11 Easy Ways to Remove Empty Rows in Excel
To make this explanation easier I’m going to use a sample dataset of a person’s personal information. There are 3 columns in the dataset where I kept some rows empty. These columns are Name, Email, and Address.
1. Using Context Menu to Remove Empty Rows
You can use the context menu to remove empty rows in Excel using the Delete command. You can remove one row or multiple rows at a time.
First, select the empty row by left-clicking on the mouse in the row number.
If you want to select multiple rows then you can use the CTRL key.
Hold the CTRL key and select the row number to select multiple rows.
Now, right-click on the mouse then from the context menu select Delete.
Thus, it will delete the selected empty rows.
Read More: How to Delete Empty Rows at Bottom in Excel (7 Ways)
2. Using Keyboard Shortcut
You also can use the keyboard shortcut to delete empty rows.
Before removing the empty rows, select the empty row by left clicking on the mouse in the row number.
If you want to select multiple rows then you can use the CTRL key.
Hold the CTRL key and select the row number.
Now, Hold the CTRL key and press minus(-)
Therefore, it will delete the selected empty rows.
Read More: How to Use VBA to Delete Empty Rows in Excel
3. Using Ribbon Command
In the ribbon, there has a Delete group where you can find the Delete Sheet Rows.
To remove an empty row using ribbon, first, select the empty row or rows. Here, I selected row 5.
Then open the Home tab >> from Delete group >> select Delete Sheet Rows
As a result, it will remove the empty row.
If you want to select multiple rows then you can use the CTRL key.
Hold the CTRL key and select the row number. You can follow section 1 to do that.
Read More: Delete Row If Cell is Blank in Excel (4 Methods)
4. Using Go To Special Feature
You can use the Go To Special option to remove empty rows.
First, select the rows to apply Go To Special.
Here, I selected the range B3:B17.
Then, open the Home tab >> from Editing group >> go to Find & Select >> finally select Go To Special
A dialog box will pop up. From there select the Blanks option and click OK.
Now, all the empty rows will be selected.
Then, right-click on the mouse, and from the context menu select Delete.
A dialog box will pop up. From Delete select the option Entire Row and finally click OK.
Hence, all the selected empty rows are removed.
Read More: How to Delete Rows in Excel: 7 Methods
5. Using Find Feature
You also can use the Find command from the ribbon to remove empty rows.
First, select the range to apply Find. Here, I selected the range B3:B17.
Now, open the Home tab >> from Editing group >> go to Find & Select >> finally select Find
A dialog box will pop up to find. From there select Sheet in Within and in Search select By Rows and in Look in select Values.
Mark on the Match entire cell contents and finally click on Find Next.
Press ALT + A to select all the found values in the dialog box.
All the empty rows will be selected then by using the right side of the mouse select Delete from the context menu.
Again, a dialog box will pop up. From Delete select the option Entire Row and finally click OK.
Now, you will see that all the selected empty rows are removed.
Read More: How to Filter Data and Delete Rows with Excel VBA (5 Examples)
6. Using Filter to Remove Empty Rows
You also can use the Filter command from the ribbon to remove empty rows.
Firstly, select the range to apply Filter. Here, I selected the range B3:B17.
Secondly, open the Data tab >> then select Filter
Here, a Filter is applied.
Now, select a column header and right click on the mouse to see the Filter Options.
Then, Deselect all the values except Blanks then click OK.
Here, all the empty rows will be sorted. Now, by using the right side of the mouse select Delete Row from the context menu.
Again, a warning message will pop up, click OK to Delete entire sheet row.
All sorted empty rows will be removed.
By left-clicking on the mouse on the empty rows number, you can get the non-empty rows back.
An Alternate Way
There is another way to remove empty rows.
Again, select the range to apply Filter. Here, I selected the range B3:B17.
Then, open the Data tab >> then select Filter
Now, select a column header and right click on the mouse to see the Filter Options.
Then, select all the values except Blanks then click OK.
Now, you will see all the rows except the empty rows.
Read More: How to Filter and Delete Rows with VBA in Excel (2 Methods)
Similar Readings:
- How to Delete Row Using Macro If Cell Contains 0 in Excel (4 Methods)
- Delete Unfiltered Rows in Excel Using VBA (4 ways)
- How to Delete Multiple Rows in Excel at Once (5 Methods)
- VBA to Delete Every Other Row in Excel (6 Criteria)
7. Using Advanced Filter to Remove Empty Rows
You also can use the Advanced Filter to remove the empty rows.
To use the Advanced Filter here, I created criteria using a formula.
Select any cell and type the following formula.
=D4<>""
Here, this formula will check the value is non-empty so that while using Advanced Filter it can copy only the non-empty rows.
First, select the range to apply Advanced Filter. Here, I selected the range B3:B17.
Then open the Data tab >> then select Advanced Filter
A dialog box will pop up.
➤ Select copy to another location in Action
➤ Select the List range. Here, it automatically selected the range B4:B17, since we applied Advanced Filter selecting this range.
➤Select the Criteria range. I selected the range F3:F4. I need to select empty F3 also because otherwise, it will not relate that which column to perform.
➤In copy to give the location. I used H4
Finally, click OK.
Thus, all the non-empty rows are copied to another location where empty rows are removed.
Read More: How to Delete Selected Rows with Excel VBA (A Step-by-Step Guideline)
8. Using FILTER Function
You can use the FILTER function to remove empty rows.
It will work on Microsoft 365 or later.
First, select any cell to place the resultant value.
Here, I selected the F4 cell.
Then, type the following formula.
=FILTER(B4:D17, B4:B17<>"")
Here, in the Filter function used the cell range B4:D17 as an array and B4:B17 non-empty cell values as included.
Finally, press the ENTER key. By applying the formula in one cell it will filter all the non-empty values.
Read More: How to Delete Blank Rows in Excel?
9. Using Sorting to Remove Empty Rows
You can use the Sort command to remove empty rows.
First, select the rows you want to Sort.
Here, I selected the range B3:D17
Now, open the Data tab >> select A to Z Sort
Now, all the rows will be sorted from A to Z so that the empty rows will be at the bottom.
Then, right click on the mouse and select Delete.
A dialog box will pop up, from there select the Entire Row option and finally click OK.
Therefore, all the selected empty rows are removed.
Read More: How to Use Macro to Delete Rows Based on Criteria in Excel (3 Ways)
10. Using Power Query to Remove Empty Rows
Power Query has the dedicated command to Remove Blank Rows.
To use the power query, first select the cell range. I selected the cell range B4:D17
Now, open the Data tab >> then select From Table/Range
A dialog box will pop up showing the selection then select My table has headers. Finally, click OK.
➤ A new window will pop up.
Here, select the cell range to remove the empty rows.
Now, open the Home tab >> from Remove Rows >> select Remove Blank rows
Hence, all the empty rows are removed.
If you want you can keep it on the worksheet. Select Keep from the dialog box.
You will find it from the power query to your working sheet.
11. Removing Empty Rows Using Hide
In case you want to remove all the empty rows of a worksheet then you can use the Hide command.
First, select the empty rows of your dataset.
To select multiple rows then you can use the CTRL key.
Hold the CTRL key and select the row number.
Now, in row 18 press CTRL+SHIFT+Down Arrow to select all the empty rows from row 18.
All the empty rows are selected.
On the row number, right-click on the mouse and then select Hide.
All the empty rows are hidden.
Read More: How to Delete Hidden Rows in Excel (3 Methods)
Practice Section
In the worksheet, I’ve provided an extra practice sheet so that you can practice these explained methods on how to remove empty rows in excel.
Conclusion
In this article, I’ve explained 11 methods on how to remove empty rows in Excel. You can follow any of the methods to remove empty rows. Also, I explained how you could hide unwanted empty rows. In case you have any confusion or question regarding these methods you may comment down below.
Related Articles
- Excel Delete Rows in a Range with VBA (3 Easy Ways)
- Excel VBA Code to Delete Rows Based on Multiple Cell Value (3 Criteria)
- Macro to Delete Row in Excel If Cell is Blank
- How to Delete Every Other Row in Excel
- How to Delete Rows in Excel without Affecting Formulas (2 Quick Ways)
