Remove Blank Rows in Excel Without Deleting Your Data (FILTER and Power Query)

The promise, in one sentence: your original sheet stays exactly as it is. You get a clean list somewhere else — a FILTER spill or a Power Query output table — and you can always go back to the original.

Verification note: These steps describe standard Excel behaviour. They have not yet been verified against a live Excel 365 file for this site, and no sample workbook is published yet. Work on a copy of your file and check the kept/dropped result row by row before trusting any method on an original.

Step 0: check for fake blanks before you filter anything

A row can look blank and still contain something. Those are “fake blanks”, and every blank-row method behaves differently on them. Check for them first, on your own data, before you pick a method.

Click a cell and read the formula bar: an empty formula bar means the cell is truly empty; a formula bar showing a space, or a formula, means it is not. Then use a helper column to test a whole row at once. For a row covering columns A to E, type:

=COUNTA(A2:E2)

COUNTA counts non-empty cells. A truly blank row returns 0. A row that returns 1 or more contains something — even if you cannot see it. To find what it contains, check the length of a single suspicious cell:

=LEN(A2)

A truly empty cell returns 0. A cell containing a single space returns 1. A cell containing a formula that returns "" (empty text) also returns 0 by LEN, but COUNTA still counts it as non-empty, because the cell contains a formula. That combination — LEN 0, COUNTA 1 — is the signature of a formula returning empty text.

What is actually in the row	How to spot it	What happens if you ignore it
Nothing at all	Formula bar empty; COUNTA = 0; LEN = 0	Nothing — this is a real blank row, and every method removes it.
A space (or several spaces)	Formula bar shows a space; LEN = 1 or more; COUNTA = 1 or more	FILTER and Power Query may keep the row, because the row is not empty by their definition. Clean it first.
A non-breaking space (CHAR 160, common in web/ERP imports)	Looks like a space; LEN = 1; TRIM alone does not remove it	Same as a space, but harder: ordinary TRIM does not remove CHAR(160). You need SUBSTITUTE for it.
A formula returning `""`	Formula bar shows the formula; LEN = 0; COUNTA = 1	A “remove empty rows” step that checks values may keep the row, because the cell contains a formula. A FILTER on a helper column handles it if the helper is written the right way.
A zero	Cell shows 0; COUNTA = 1	A zero is data, not a blank. If your clean list drops zeros, you have defined “blank” too broadly.
Only some columns filled	COUNTA is greater than 0 but less than the full width	This is a partially-filled row, not a blank row. Whether to keep it depends on your definition, not on any tool's default.

Cleaning a fake blank before you filter. For space-type fake blanks, the standard cleaning combination is TRIM (removes leading, trailing, and extra internal spaces) plus CLEAN (removes non-printing characters), with SUBSTITUTE added for non-breaking spaces, because TRIM alone does not remove CHAR(160):

=TRIM(CLEAN(SUBSTITUTE(A2,CHAR(160)," ")))

Apply that to a helper column, or clean the source copy, and then filter on the cleaned result. You can also clean pasted text first with the Data Cleaner.

Define “blank row” first

All columns empty — the strictest definition. A row with even one filled cell is kept.
Key column empty — for example, a row with no customer name is blank for your report, even if a notes column contains something.
Helper column is zero — you define blank by a COUNTA helper and filter on that helper.

Write the definition down before you filter. Most “it deleted the wrong rows” stories are definition stories, not tool stories: the tool did what it was told, and what it was told was not what the user meant.

Method 1: FILTER (newer Excel)

FILTER creates a new, live list that updates when the source changes, and it never touches the source. That is why it is the first method on this page.

Version note — read this before you start. FILTER is a dynamic-array function in newer Excel (Excel 365 and later versions that include dynamic arrays). If you do not see FILTER in your version, skip to Method 2 (Power Query).

How it works, in outline. You add a helper column that marks each row as “keep” or “drop”, then FILTER returns only the keep rows to a new location. For a source range A2:E100 and a helper column in F (where F contains the COUNTA test from Step 0, i.e. the number of non-empty cells in that row), the pattern is:

=FILTER(A2:E100, F2:F100>0)

That returns every row where the helper is greater than 0 — that is, every row that contains at least one value — and drops every row where the helper is 0. The result “spills” into neighbouring cells: do not type anything into the spill area, or Excel will show a spill error. If you see a spill error, clear the cells the result needs and try again.

Why the helper column matters. Filtering directly on “not blank” is fragile when fake blanks are present, because a space or a ="" formula is not blank by Excel's definition. The helper column lets you define blank once and then filter on that definition, so the result matches what you meant.

What FILTER does not do. It does not change the source, it does not save a cleaned copy unless you copy and paste the spill result as values somewhere, and it will recalculate if the source changes. For a one-off report, that is usually what you want. For a repeatable monthly clean, Power Query (Method 2) is the better fit, because it records the steps.

Method 2: Power Query (also the path for older Excel)

Power Query loads your source, applies a recorded series of steps, and loads the result to a new table. The source stays intact, the steps are repeatable, and it is available in older Excel versions where FILTER is not.

Select your source range (or, better, format it as an Excel Table first, so new rows are picked up on refresh).
Go to Data → From Table/Range. Power Query opens.
Use the Remove Rows → Remove Blank Rows step — and then check the result against your fake-blank rows before you trust it.
Load the result to a new worksheet (Close & Load To → Table, new worksheet), not back over the source.

The check that matters. Power Query's “Remove Blank Rows” removes rows that Power Query considers blank. Whether it removes a space row, an NBSP row, or a ="" row depends on how Power Query sees those values after loading, and that is exactly what you must test on your own file rather than assume. If a type survives, add a cleaning step in Power Query (for example, a Replace Values or Trim step) and check again.

When to choose Power Query over FILTER. Choose Power Query when: you are on an older Excel version without FILTER; the clean needs to be repeated monthly on new data; or the cleaning involves several steps that you want recorded as a repeatable recipe. Choose FILTER when: you have it, you want a live list that updates instantly, and the clean is a single filter.

What NOT to do if you want to keep your data

The most common deletion method is Go To Special → Blanks → Delete. It is fast, and it is the method most deletion guides lead with. It is not the method for this page's promise, for two reasons:

Go To Special selects blank cells, not necessarily fully-blank rows. Selecting blanks in a column and deleting can shift cells in ways you may not intend if only some cells in a row are blank.
Deletion cannot be undone after the file is saved and closed. There is no “clean view” to go back to.

If you actually want to delete, do it on a copy, define “blank row” first, and check the result row by row before saving.

Google Sheets note

This page is Excel-first. Google Sheets has its own FILTER function, but its syntax and behaviour on fake blanks differ from Excel's, and Power Query does not exist in Sheets. Do not present Excel FILTER steps as Sheets steps.

← Back to home