How to Remove Empty Rows in Excel (11 Methods)

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.

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 datase

 

Download to Practice

Ways to Remove Empty Rows in Excel

1. Using Context Menu to Remove Empty Rows

You can use the context menu to remove empty rows 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.

1. Using Context Menu to Remove Empty Rows

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

Thus, it will delete the selected empty rows.

1. 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(-)

Therefore, it will delete the selected empty rows.

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.

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.

Now, all the empty rows will be selected.

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

Using Go To Special Feature to Remove Empty RowsA dialog box will pop up. From Delete select the option Entire Row and finally click OK.

Hence, all the selected empty rows are removed.

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.

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.

Using Find Feature to Remove Empty Rows

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

Using Find Feature to Remove Empty Rows

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

Using Filter to Remove Empty Rows

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.

Using 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.

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

Using Filter to Remove Empty Rows

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.

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

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.

Using Filter to Remove Empty Rows

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:


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

Using Advanced Filter to Remove Empty Rows

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 on 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.

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 include.

Finally, press the ENTER key. By applying the formula in one cell it will filter all the non-empty values.

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.

A dialog box will pop up, from there select the Entire Row option and finally click OK.

Using Sorting to Remove Empty Rows

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

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.

➤ A new window will pop up.

Using Power Query to Remove Empty Rows

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 the Keep from the dialog box.

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

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.

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

All the empty rows are hidden.

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.

Practice Sheet to remove empty rows

Conclusion

In this article, I’ve explained 11 methods to remove empty rows in Excel. You can follow any of the methods to remove empty rows. Also, I explained how you can 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