Excel data cleaning
How to Remove Blank Rows in Excel (Fast, Without Deleting Real Data)
Select your data, press F5 then Special, choose Blanks and click OK. Excel selects every empty cell; right-click one of them, choose Delete, then Entire row. This is safe only if a row is blank all the way across. If a row has a value in any column, this method would delete it too, so use the filter method below instead.
Blank rows break sorting, filtering, pivot tables and imports. They usually come from exports, copied tables and rows someone cleared instead of deleting. The fast method has one trap that deletes real data, so read the warning before you use it.
Method 1: Go To Special (rows that are empty across)
- Click in your data and press Ctrl+A to select it.
- Press F5, click Special, choose Blanks, then OK.
- With the blank cells selected, right-click one of them and choose Delete.
- Choose Entire row and click OK.
Warning: this selects every blank cell, not every blank row. A row with a name but no phone number has a blank cell, and the steps above would delete the whole row. Use this method only when the rows to remove are empty in every column, or work on a single column that is always filled in.
Method 2: a helper column and a filter (the safe way)
Add a column next to your data that counts how many cells in each row have something in them:
=COUNTA(A2:H2)Change H to your last column. Fill down. A result of 0 means the row is empty across. Then filter that helper column to show only 0, select the visible rows, right-click a row number, choose Delete Row, and remove the filter. Rows that have even one value are never touched.
Method 3: sort the blanks to the bottom
If the order of your rows does not matter, sort by a column that is always filled. Excel puts empty cells last, so the blank rows gather at the bottom where you can delete them in one go. If the order does matter, add a numbering column first (1, 2, 3...) and re-sort by it afterwards to restore the original order.
Method 4: Power Query (repeatable)
On the Data tab choose From Table/Range. In the Power Query window open Remove Rows on the Home tab and choose Remove Blank Rows, then Close & Load. Power Query only removes rows that are blank in every column, and it repeats the step when you refresh, which is useful for an export you receive every week. See cleaning data with Power Query for more.
Rows that look empty but are not
Sometimes a row looks blank yet Go To Special does not find it. The cells contain a space, an apostrophe, a formula that returns an empty string, or an invisible character. Excel counts those as filled. To see it, check the length of the cell with =LEN(A2): anything above 0 is not truly empty. Clean the cells first with =TRIM(CLEAN(A2)), which is covered in removing extra spaces in Excel, and then remove the rows.
Always keep a copy
Deleting rows cannot always be undone once the file is saved and closed. Save a copy of the file first, or work on a copy of the sheet. Then compare the row count before and after: it should drop by exactly the number of blank rows you expected.
A whole file at once
DecodingPro reads every sheet, ignores rows that are truly empty, trims invisible spaces that make a row look filled, and flags cells that are empty where the rest of the column is filled, rather than deleting anything for you. Every change is logged.
You start with 100 free credits. Every change is logged, and anything unclear is flagged, never guessed.
Fix a spreadsheetQuestions people ask
How do I delete all empty rows in Excel at once?
Select the data, press F5, Special, Blanks, then right-click a selected cell, Delete, Entire row. Only do this if the empty rows are blank in every column, otherwise use a COUNTA helper column and a filter.
Why did Excel delete rows that had data?
Go To Special selects blank cells, not blank rows. Deleting the entire row for a blank cell removes the row's other values too. Use the COUNTA helper column method to be safe.
Why can't Excel find my blank rows?
The cells are not truly empty. They hold a space, an apostrophe or a formula returning an empty string. Check with =LEN(A2), clean the cells, then try again.
Can I remove blank rows automatically every time I get a file?
Yes, with Power Query: the Remove Blank Rows step is saved with the query, so it runs again on Refresh.