How to Delete Blank Rows in Excel Without Removing Data

If you’ve been struggling to delete blank rows in Excel without losing real data, start by filtering the column that every valid row should contain. This method shows the affected rows before Excel deletes anything.

Fix #1: Filter and delete blank rows

Filtering is the safest general-purpose method for lists and Excel Tables. It works especially well when one column, such as an ID or email address, should never be empty.

Save a backup copy of the workbook on an external hard drive or cloud backup service.

You’ll have an untouched copy if the wrong rows are removed.

Select the data range, including its header row.

Excel should highlight the entire list rather than a single column.

Select Home > Sort & Filter > Filter.

You can also press Ctrl + Shift + L. Filter arrows should appear beside the column headings.

Excel Home tab with Sort & Filter menu open and Filter highlighted

Open the filter arrow for a column that every valid row should contain.

The filter menu should list the values found in that column.

Clear Select All.

Select Blanks.

Select OK.

Excel should now display only rows where the chosen column is empty.

Excel column filter menu with only Blanks selected

Select the visible row numbers without selecting the header.

The full rows should be highlighted.

Select Home > Delete > Delete Sheet Rows.

The filtered rows should disappear. Don’t choose Delete Cells, which can shift remaining data out of alignment.

Excel Home tab Delete menu with Delete Sheet Rows highlighted while filtered blank rows are selected

Select Home > Sort & Filter > Clear.

Your complete dataset should return without the deleted rows.

Filtering one dependable column addresses the blank-row problem without treating every empty cell as a bad row. A missing phone number, for example, won’t cause Excel to delete a row that still contains a name and address.

Fix #2: Select blanks with Go To Special

Use Go To Special when a single required column reliably identifies unwanted rows. Avoid selecting the entire worksheet because Excel may find empty cells inside otherwise valid rows.

Select the data cells in the required column without selecting its header.

Only the column used to identify blank rows should be highlighted.

Press Ctrl + G.

The Go To dialog should open. F5 opens the same dialog on keyboards where that key isn’t assigned to another function.

Select Special.

Excel Go To dialog with the Special button highlighted

Select Blanks.

Select OK.

Excel should highlight every truly empty cell in the selected range.

Excel Go To Special dialog with Blanks selected

Select Home > Delete > Delete Sheet Rows.

Every row containing one of the selected blank cells should be removed.

Excel worksheet with blank cells selected and Home Delete menu showing Delete Sheet Rows

Press Ctrl + Z immediately if Excel removes populated rows. This method can select the wrong cells when the range contains merged cells or partially completed records.

Cells containing spaces or formulas such as ="" aren’t truly empty. Go To Special may leave those rows in place.

Fix #3: Identify completely blank rows with a helper formula

A helper column solves the harder case: a row should be deleted only when every data cell is empty. It also lets you inspect the result before removing anything.

Insert an empty column beside the dataset.

Enter a heading such as RowCount in the new column.

Enter =COUNTA(A2:Z2) in the first data row.

Replace A2:Z2 with the actual columns used by your dataset. The result should show how many non-empty cells are present in that row.

Excel worksheet with a RowCount helper column and COUNTA formula entered beside the first data row

Drag the fill handle down to copy the formula through the dataset.

Completely empty rows should show 0.

Select Home > Sort & Filter > Filter.

Open the filter arrow for RowCount.

Filter the column to show only 0.

Only rows with no counted values should remain visible.

Excel RowCount filter menu configured to display only zero values

Select the visible row numbers.

Select Home > Delete > Delete Sheet Rows.

Clear the filter.

Delete the RowCount helper column.

The original columns should remain, with the completely empty rows removed.

COUNTA counts spaces, hidden characters, and formulas returning empty text as content. If a row looks blank but its result exceeds 0, inspect those cells before deleting the row.

Fix #4: Sort blank rows together

Sorting handles simple lists where blanks occur in one dependable column. It changes the row order, so add an order column first if the original sequence matters.

Insert an empty column beside the dataset.

Name the column OriginalOrder.

Enter 1 and 2 in its first two data cells.

Select both numbered cells.

Drag the fill handle through the remaining rows.

Each row should now have a consecutive number.

Excel dataset with an OriginalOrder helper column containing consecutive row numbers

Select the complete dataset, including OriginalOrder.

Select Data > Sort.

Select My data has headers if the first row contains headings.

Choose a required data column under Sort by.

Select OK.

Rows with blank cells in that column should be grouped at one end of the dataset.

Excel Sort dialog with My data has headers selected and a required data column chosen under Sort by

Select the grouped blank row numbers.

Right-click the selection and choose Delete.

The blank rows should disappear.

Select the complete dataset again.

Select Data > Sort.

Sort OriginalOrder from smallest to largest.

The remaining records should return to their previous order.

Delete the OriginalOrder column.

I’d skip sorting unless the list is simple or you’ve added the order column. Filtering makes the rows scheduled for deletion easier to check.

Fix #5: Remove recurring blank rows with Power Query

Power Query handles imported reports that develop the same blank rows whenever they’re refreshed. It creates a repeatable cleanup process and leaves the source range available for review.

Select a cell inside the dataset.

Select Data > From Table/Range.

Confirm the range in the Create Table dialog.

Select OK.

The Power Query Editor should open with a preview of the data.

Power Query Editor showing an imported Excel table with blank rows visible in the preview

Select Home > Remove Rows > Remove Blank Rows.

Rows that are empty across the imported columns should disappear from the preview.

Power Query Editor Home tab with Remove Rows menu open and Remove Blank Rows highlighted

Select Home > Close & Load.

Excel should load the cleaned results into a worksheet.

Power Query takes care of the blank rows again when the query refreshes. This is a better fit than Excel macros for routine imported-data cleanup.

When blank rows won’t disappear

A row that looks empty may contain spaces, invisible characters, or formulas returning "". Click a suspicious cell and check the formula bar; COUNTA can also confirm that Excel still sees content.

If deleting rows doesn’t reduce an unusually large workbook, remove unused rows and columns beyond the real dataset. Save, close, and reopen the workbook so Excel can recalculate its used range.

Conclusion

Fix #1, filtering a required column, usually deletes blank rows in Excel with the lowest risk. If every imported file has the same gaps, Fix #5 solves the recurring cleanup with a refreshable Power Query instead of repeated manual edits.