30 Advanced Excel Tips for 2026

Excel Champs / Range / How to Delete Blank Rows in Excel
By Puneet Gogia (Microsoft MVP)

How to Delete Blank Rows in Excel

In an Excel spreadsheet, there could be blank rows in the data set that make navigating around the data difficult. Excel also uses blank cells and rows to determine the ranges, so they can interrupt formulas, sorting and filtering too.

But, users can quickly delete these blank rows as Excel has several ways to delete them. We have quick and easy steps below on how to delete the blank rows in Excel.

Delete Blank Rows using the Filter Option

The filter option for deleting the blank rows is one of the easiest ways to remove or delete the blank rows from your data set.

  1. First, select the entire data range including blank rows and then go to the “Data” tab and click on the “Filter” icon under the “Sort & Filter” group.
  2. Or, select the entire data range including blank rows, and then press shortcut keys “Ctrl + Shift + L” to apply the filter.
    select-the-range-including-blank-rows
  3. Once you are done with any of the above steps, you will get the filters added to the headers of the data range.
  4. Now, click on any of the applied filter buttons and then uncheck the “Select All” option from the filter drop-down menu to unselect all the pre-selected options then checkmark only the “Blanks” option and then press OK.
    click-the-filter-button
  5. At this point, you will get the blank rows filtered and you just need to select them all and right click and select “Delete Row” or press the “Ctrl + -” keys, and your blank rows will get deleted.
    filtered-blank-rows
  6. In the end, click on the applied filter button again and checkmark “Select All” or simply press the “Ctrl + Shift + L” keys to remove the filter, and you will get your data back without the blank rows.
    checkmark-select-all-option

When you delete a row, Excel deletes the entire row of the spreadsheet. That means even if you have data only in columns A to D, Excel deletes the whole row from column A to the last column of the sheet, and you can lose any values in that row outside of your data set.

So, it is recommended to convert your data set into table format using the “Ctrl + T” keys as in a table, Excel deletes the rows within the table only.

Delete Blank Rows using the Sort Option

  • First, select the entire data range including blank rows, and go to the “Data” tab.
  • After that, click on the “Sort” icon under the “Sort & Filter” group and you will get a “Sort” dialog box opened.
  • Now, select the column to sort by and click OK. (In this example we have selected the data sort by “Months”)
    blank-rows-using-sort-option
  • At this point, you will get your data sorted with the blank rows moved to the end of the data set.
    sorted-data-without-blank-rows

Keep in mind that this method changes the order of your data. If you need to keep the original order, add a column with serial numbers before you sort, and sort by that column again once the blank rows are at the end.

Delete Blank Rows Manually

Deleting the blank rows manually is a good option to choose when you have a small data set with a few blank rows and where you can easily identify those blank rows on the screen.

  • Select the blank rows one by one, right-click, and select Delete to delete the selected blank row.
  • Or, select all the blank rows together and then right-click and select Delete to delete all the selected blank rows in one go.
delete-blank-rows-manually

You can use the shortcut keys Shift + Space to select the row and then Ctrl + - keys to delete that selected row.

Delete Blank Rows Using Go To Special

  • First, select the entire data range and go to the “Home” tab and then click on the “Find & Select” icon.
  • After that, click on the “Go To Special” option from the drop-down list.
    go-to-special-option
  • Once you click on “Go To Special” you will get the “Go To Special” dialog box opened.
  • Now, select the “Blanks” option radio button and click OK and you will get the blank rows selected.
    select-blank-option
  • In the end, right-click and select the “Delete” option, and you will get the “Delete” dialog box, which asks how to shift the cells.
  • Select “Shift cells up” (or “Entire row”) and click OK, and you will get your blank rows deleted.
    select-shift-cells-up

Note: Go To Special selects every blank cell, not only fully blank rows. So, if a row has data but one of its cells is empty, that cell gets selected too, and “Shift cells up” moves the cells below it up, which can mess up your data. Use this method only when the blank cells are complete blank rows.

Delete Only Fully Blank Rows with a Helper Column

When some rows are only partly empty, use a helper column to find the rows that are completely blank. In the first empty column next to your data (say E2, for data in A to D), enter the formula below and drag it down:

=COUNTA(A2:D2)=0

It returns TRUE only for rows where all four cells are empty. Now, filter the helper column for TRUE, delete those rows, remove the filter, and delete the helper column. Note that COUNTA counts a cell with a space as not empty. If your blank rows can have spaces in them, use this formula instead:

=SUMPRODUCT(--(LEN(TRIM(A2:D2))>0))=0

Delete Blank Rows with VBA

If you clean data like this often, a macro can do it in one click. The following code checks every row of the used range from the bottom up and deletes the rows that are completely empty:

Sub DeleteBlankRows()
Dim i As Long
With ActiveSheet.UsedRange
For i = .Rows.Count To 1 Step -1
If WorksheetFunction.CountA(.Rows(i)) = 0 Then .Rows(i).EntireRow.Delete
Next i
End With
End Sub

It loops from the last row to the first, because deleting a row moves the rows below it up. Save your file before you run it, as you can’t undo a macro.

Remove Blank Rows with Power Query

If your data comes from an import that you refresh, load it into Power Query (Data ➜ From Table/Range), and then go to Home ➜ Remove Rows ➜ Remove Blank Rows. Close & Load the data, and the blank rows are removed every time you refresh.

Frequently Asked Questions

Can I undo deleting blank rows?

Yes, with Ctrl + Z, as long as you deleted them with the filter, sort, Go To Special or manual method. Rows deleted with a macro can’t be undone.

Why are my blank rows not filtered as Blanks?

The cells look empty but have something in them, like a space or a formula that returns “”. Use the helper column with LEN and TRIM to find those rows.

Will deleting blank rows break my formulas?

Formulas that refer to the deleted rows directly show a #REF! error. Formulas that refer to a range, like SUM(B2:B20), adjust on their own, so they keep working.