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

Download Practice Workbook

Remove Empty Rows.xlsx

Method 1 – Using Context Menu to Remove Empty Rows

Select the empty row by left-clicking on the mouse in the row number.
Hold the CTRL key and select the row number to select multiple rows.

Right-click and select Delete from the context menu.

It will delete the selected empty rows.

Method 2 – Using Keyboard Shortcut

Before removing the empty rows, select the empty row by left clicking on the mouse in the row number.
Hold the CTRL key and select other empty rows.

Hold the CTRL key and press minus(-)

It will delete the selected empty rows.

Method 3 – Using Ribbon Command

To remove an empty row using ribbon, select the empty row or rows. We have selected row 5.
Open the Home tab >> from Delete group >> select Delete Sheet Rows

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. Follow the procedure shown in Method 1.

Method 4 – Using Go To Special Feature

Select the rows to apply Go To Special. We have selected the range B3:B17.
Open the Home tab >> from Editing group >> go to Find & Select >> select Go To Special

A dialog box will pop up.

Select the Blanks option and click OK.

All the empty rows will be selected.

Right-click and select Delete from the context menu .

A dialog box will pop up.

From Delete select the option Entire Row and click OK.

All the selected empty rows will be removed.

Method 5 – Using Find Feature

Select the range to apply Find. We have selected the range B3:B17.
Open the Home tab >> from Editing group >> go to Find & Select >> select Find

In the Find dialog box, select Sheet in Within and in Search select By Rows and in Look in select Values.
Checkmark on the Match entire cell contents and click on Find Next.

Press ALT + A to select all the found values in the dialog box.

All the empty rows will be selected.

Right-click and select Delete from the context menu.

In the Delete pop-up, select the option Entire Row and click OK.

All the selected empty rows will be removed.

Method 6 – Using Filter to Remove Empty Rows

Select the range to apply Filter. We have selected the range B3:B17.
Open the Data tab >> select Filter

A Filter will be applied.

Select a column header and right-click.
In the Filter Options, Deselect all the values except Blanks.
Click OK.

All the empty rows will be sorted.

Right-click and select Delete Row from the context menu.

Click OK to Delete entire sheet row.

All sorted empty rows will be removed.

You can get the non-empty rows back by left-clicking on the mouse on the empty rows number

An Alternate Way

Select the range to apply Filter. We have selected the range B3:B17.
Open the Data tab >> select Filter

Select a column header and right click on the mouse to see the Filter Options.
Select all the values except Blanks and click OK.

It will filter out the empty rows.

Similar Readings:

Method 7 – Using Advanced Filter to Remove Empty Rows

Select any cell and enter the following formula.

=D4<>""

Select the range to apply Advanced Filter. We have selected the range B3:B17.
Open the Data tab >> select Advanced Filter

A dialog box will pop up.

➤ Select copy to another location in Action
➤ Select the List range. It automatically selected the range B4:B17, since we applied Advanced Filter selecting this range.
➤Select the Criteria range. We have selected the range F3:F4.
➤In copy to, enter the location. We have used H4

Click OK.

All the non-empty rows are copied to another location where empty rows are removed.

Method 8 – Using FILTER Function

Select any cell to place the resultant value. We have selected the F4 cell.
Enter the following formula.

=FILTER(B4:D17, B4:B17<>"")

Press ENTER.

It will filter all the non-empty values.

Method 9 – Using Sorting to Remove Empty Rows

Select the rows you want to Sort. We have selected the range B3:D17.
Open the Data tab >> select A to Z Sort

All the rows will be sorted from A to Z so that the empty rows will be at the bottom.

Right click and select Delete.

A dialog box will pop up.

Select the Entire Row option and click OK.

All the selected empty rows will be removed.

Method 10 – Using Power Query to Remove Empty Rows

To use the power query, first select the cell range. We have selected the cell range B4:D17.
Open the Data tab >> select From Table/Range

In the pop-up dialog box, check My table has headers and click OK.

A new window will pop up.

Select the cell range to remove the empty rows.
Open the Home tab >> from Remove Rows >> select Remove Blank rows

All the empty rows will be removed.

To keep your changes, select Keep from the dialog box.

You will find it from the power query to your working sheet.

Method 11 – Removing Empty Rows Using Hide

Select the empty rows of your dataset.
Hold the CTRL key and select the row numbers.
In row 18, press CTRL+SHIFT+Down Arrow to select all the empty rows from row 18.