How to Remove Blank Rows in Excel

That warning is the whole reason this page exists. The Go To Special trick is the most widely repeated answer to this question, and on tidy demonstration data it works perfectly. On real data with a half-filled column, it deletes far more than you asked.

Why Go To Special is risky

Consider a sheet where column D holds an optional note, filled in for only some rows.

Row	A	B	C	D (optional)
2	Ali	London	440	—
3	—	—	—	—
4	Sara	Leeds	512	Priority

Select the range, press F5 → Special → Blanks, then Delete Sheet Rows, and Excel removes row 2 as well as row 3. Row 2 contained a blank cell in column D, and that was enough.

Nothing warns you. The rows are gone, and on a large sheet you may not notice until much later.

Method 1: helper column with COUNTA (recommended)

Explicit, reversible, and it cannot delete a row that contains data.

1. In the first empty column beside your data, enter =COUNTA(A2:F2), adjusting the range to your columns.
2. Fill it down the full height of the data.
3. COUNTA counts non-empty cells, so a genuinely blank row returns 0.
4. Select your headers and apply Data → Filter.
5. Filter the helper column to show only 0.
6. Select the visible row headers, right-click → Delete Row.
7. Clear the filter and delete the helper column.

Before step 6, glance at what is on screen. If any row shows data, your COUNTA range is wrong — it is not covering every column. That check takes two seconds and is the reason this method is safe.

Method 2: filter for blanks directly

Quicker, and reliable when one column is always populated for real rows.

1. Select your data and apply Data → Filter.
2. Open the dropdown on a column that should never be empty.
3. Untick Select All, then tick only (Blanks).
4. Select the visible rows, right-click → Delete Row.
5. Clear the filter.

This deletes rows where that column is empty, which is what you want provided the column is genuinely mandatory. If it is optional, you are back to the Go To Special problem.

Method 3: sort them to the bottom

Sorting moves empty rows to the end of the range, where you can select and delete them in one go.

This changes the order of your data permanently, so only use it when the order does not matter, or add an index column first (1, 2, 3…) so you can sort back afterwards.

Method 4: Power Query

Best when this is a recurring import rather than a one-off tidy-up.

Select the data, use Data → From Table/Range, then in the Power Query editor choose Home → Remove Rows → Remove Blank Rows, and load the result back. The step is saved, so refreshing the query re-applies it to next month's file automatically.

Note that Power Query treats a row as blank only when every column is empty — the behaviour you almost always want.

Rows that look blank but are not

If a row survives every method above, it is not empty. Common causes:

• A space character typed into a cell. COUNTA counts it as content, correctly.
• A formula returning "". The cell displays nothing, but it holds a formula, so COUNTA counts it. Use =COUNTIF(A2:F2,"?*")+COUNT(A2:F2) if you need to treat those as blank.
• Formatting only — borders or a fill colour with no content. These do not affect COUNTA but do inflate the used range; see why your Excel file is slow and large.

Method comparison

Method	Safe on partial data	Best for
COUNTA helper column	Yes	Any sheet — the default choice
Filter for (Blanks)	Only on a mandatory column	Quick tidy where one column is always filled
Sort to bottom	Yes	When row order does not matter
Power Query	Yes	Repeating imports
Go To Special → Blanks	No	A single always-filled column only

Frequently asked questions

How do I remove blank rows in Excel?

Add a helper column with =COUNTA(A2:F2), filter it to 0, and delete the rows shown. COUNTA counts non-empty cells, so only genuinely empty rows return 0.

Why did Go To Special delete rows that had data?

Because it selects blank cells, not blank rows, and Delete Sheet Rows then removes every row containing any selected cell. One empty cell in an optional column is enough to delete an otherwise full row.

Is there a safe way to use Go To Special for this?

Yes — select only a single column that is filled for every real row, such as an ID. Blank cells in that column genuinely indicate blank rows. The danger comes from selecting the whole table.

How do I delete blank rows without changing the order?

Use the COUNTA helper column with a filter, or Power Query. Both preserve order. Sorting blank rows to the bottom does not, unless you add an index column first so you can sort back.

Why won't a row delete even though it looks empty?

It is not empty. Usually a space character, or a formula returning "" — which displays as nothing but is still content. Select the row and check the formula bar cell by cell.

How do I remove blank rows in a large file?

Power Query handles large data efficiently and saves the step, so it re-applies on refresh. For a one-off, the COUNTA and filter method is fine on tens of thousands of rows.

Does deleting blank rows reduce file size?

Only slightly on its own. If the file is unexpectedly large, the cause is usually formatting applied far beyond your data — press Ctrl + End to check.

Conclusion

Use the COUNTA helper column. It takes a minute longer than the Go To Special trick and cannot silently delete rows containing real data — which the popular method absolutely can.

If you do use Go To Special, select one always-filled column rather than the whole table, and check what the filter is showing before you delete anything.

Related: how to remove spaces in Excel covers cleaning the text itself, and removing duplicates covers the other common tidy-up.