Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

How to Remove Empty Rows in Excel (11 Easy Ways)

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.

How to Remove Empty Rows in Excel


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.

Sample dataset


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.

Using Context Menu to Remove Empty Rows

Now, right-click on the mouse then from the context menu select Delete.

Clicking Delete Option

Thus, it will delete the selected empty rows.

Final Output After Using Context Menu to Remove 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.

Using Keyboard Shortcut to Remove Empty Rows

Now, Hold the CTRL key and press minus(-)

Pressing Ctrl and -

Therefore, it will delete the selected empty rows.

Output After Using Keyboard Shortcut to Remove 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

Using Ribbon Command to Remove Empty Rows

As a result, it will remove the empty row.

Output After Using Ribbon Command to Remove Empty Rows

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

Using Go To Special Feature to Remove Empty Rows

A dialog box will pop up. From there select the Blanks option and click OK.

Selecting Blanks on Go To Special Window

Now, all the empty rows will be selected.

Selecting Empty Rows

Then, right-click on the mouse, and from the context menu select Delete.

Hitting Delete ButtonA dialog box will pop up. From Delete select the option Entire Row and finally click OK.

Delete Options

Hence, all the selected empty rows are removed.

Output After Using Go To Special Feature to Remove Empty Rows

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

Using Find Feature to Remove Empty Rows

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.

Find and Replace Window

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.

Click on Delete to Apply Find Feature to Remove Empty Rows

Again, a dialog box will pop up. From Delete select the option Entire Row and finally click OK.

Delete Options in Find Feature to Remove Empty Rows

Now, you will see that all the selected empty rows are removed.

Output After Using Find to Remove Empty Rows in Excel

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

Using Filter to Remove Empty Rows

Here, a Filter is applied.

Result After 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.

Using Blank Filter to Remove Empty Rows

Here, all the empty rows will be sorted. Now, by using the right side of the mouse select Delete Row from the context menu.

Deleting the Blank Rows

Again, a warning message will pop up, click OK to Delete entire sheet row.

Confirm to Remove Empty Rows

All sorted empty rows will be removed.

Empty Rows Removed

By left-clicking on the mouse on the empty rows number, you can get the non-empty rows back.

Output After Using Filter to Remove Empty Rows


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

Alternate Way of Using Filter to Remove Empty Rows

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.

Applying Blank Filter to Remove Empty Rows

Now, you will see all the rows except the empty rows.

Final Output

Read More: How to Filter and Delete Rows with VBA in Excel (2 Methods)


Similar Readings:


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<>""

Using Advanced Filter to Remove Empty Rows in Excel

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

Opening 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

Advanced Filter Options

Finally, click OK.

Thus, all the non-empty rows are copied to another location where empty rows are removed.

Output After Using Advanced Filter to Remove Empty Rows

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<>"")

Using FILTER Function to Remove Empty Rows

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.

Output of FILTER Function

Read More: Formula to Remove Blank Rows in Excel (5 Examples)


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

Using Sorting to Remove Empty Rows

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.

Deleting Sorted Empty RowsA dialog box will pop up, from there select the Entire Row option and finally click OK.

Delete Options

Therefore, all the selected empty rows are removed.

Output of Sorting Method

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

Using Power Query to Remove Empty Rows

A dialog box will pop up showing the selection then select My table has headers. Finally, click OK.

Selecting Table

➤ A new window will pop up.

Power Query Window

Here, select the cell range to remove the empty rows.
Now, open the Home tab >> from Remove Rows >> select Remove Blank rows

Remove Blank Rows From Power Query

Hence, all the empty rows are removed.

Result of Removing Blank Rows

If you want you can keep it on the worksheet. Select Keep from the dialog box.

Confirming Power Query Editor to Remove Empty RowsYou will find it from the power query to your working sheet.

Power Query Output

Read More: How to Delete Specific Rows in Excel (8 Quick Ways)


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.

Removing Empty Rows Using Hide

All the empty rows are selected.

Empty Rows are Selected

On the row number, right-click on the mouse and then select Hide.

Hiding Empty Rows

All the empty rows are hidden.

Output After Removing Empty Rows Using Hide

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.

Practice Sheet to remove empty rows


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

Shamima

Shamima

Hello! Welcome to my Profile. I am Shamima Sultana Rita. Currently, I am working and doing research on Microsoft Excel and here I will be posting articles related to this. My last educational degree was BSc and my program was Computer Science and Engineering from East West University-Bangladesh. I am a Computer Science graduate with a great interest in research and development. I love to learn new things. Data Analysis is one of my favorite fields as I love to extract patterns based on problems. Here, I'm trying to provide enriched quality content regarding Excel basics to Advanced. Always try to gather knowledge from various sources and try to make innovative solutions.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo