Filter Out Blanks in Excel With the FILTER Function

To filter out blanks in Excel, give the FILTER function a "not empty" test: =FILTER(A2:A10,A2:A10<>""). Every cell that holds something spills into the result, and the empty ones are skipped. This guide shows the formula for one column and for whole rows, plus the three things that trip people up: cells with hidden spaces, stray zeros and the #CALC! error.

The formula to filter out blanks in Excel#

FILTER returns the rows of a range that pass a test. To skip blanks, the test is "this cell is not equal to empty text":

=FILTER(array, include, [if_empty])
=FILTER(A2:A10,A2:A10<>"")

The <> sign means "not equal to", and "" is empty text. So A2:A10<>"" gives a list of TRUE and FALSE values, one per cell, and FILTER keeps the TRUE ones.

FILTER needs Excel 2021, Excel 2024 or Microsoft 365. You type it in one cell and the results spill into the cells below. If those cells are not empty, you get #SPILL! instead, so leave room. The FILTER function in Excel covers the function in general.

Filter out blanks from one column#

Here is a volunteer list with gaps in A1:B8:

In D2, type:

=FILTER(A2:A8,A2:A8<>"")

The result spills into D2:D5: Amara, Luis, Grace, Tomas. Add a name to A3 and it appears in the list at once, which is the big advantage over deleting rows by hand.

To count the names instead of listing them, use =COUNTA(FILTER(A2:A8,A2:A8<>"")), which returns 4. To sort them, wrap the formula in SORT: =SORT(FILTER(A2:A8,A2:A8<>"")).

FILTER is also the fix for a common UNIQUE problem. If you run =UNIQUE(A2:A8) on a column with gaps, the empty cells come back as a 0 in the list. Remove the blanks first and the 0 disappears:

=UNIQUE(FILTER(A2:A8,A2:A8<>""))

Filter out rows where one column is blank#

Return several columns but test only one, and you remove every row where that column is empty. To list the volunteers with their shifts, skipping rows with no name:

=FILTER(A2:B8,A2:A8<>"")

This spills a two-column result: Amara/Morning, Luis/Evening, Grace/0, Tomas/Evening. Row 6 is gone because its name is blank, even though it has a shift.

Notice Grace/0. Grace has no shift, and FILTER returns an empty cell as 0. The fix is in the section on stray zeros below.

Filter out rows that are completely blank, or not complete#

You can test several columns at once. In FILTER conditions, * works like AND and + works like OR. For a deeper look, see FILTER with multiple criteria.

Keep rows where any column has something (drop only the fully empty rows):

=FILTER(A2:B8,(A2:A8<>"")+(B2:B8<>""))

This keeps rows 2, 4, 5, 6 and 7. Only rows 3 and 8 are fully empty, so only they are removed.

Keep rows where every column is filled (drop any row with a gap):

=FILTER(A2:B8,(A2:A8<>"")*(B2:B8<>""))

This keeps only Amara, Luis and Tomas. Grace has no shift and row 6 has no name, so both drop out. Put each test in its own brackets, or Excel works out the comparison in the wrong order.

Why do blank-looking cells still show up?#

A cell that holds a space, or text copied from another system, looks empty but is not. A2:A8<>"" treats it as filled, so it appears in your result as an empty-looking row.

Use TRIM to remove spaces and LEN to count what is left:

=FILTER(A2:A8,LEN(TRIM(A2:A8))>0)

Now a cell with only spaces has a length of 0 after TRIM, so it is filtered out. TRIM does not remove the non-breaking space that web pages often use. If those are in your data, use SUBSTITUTE(A2:A8,CHAR(160),"") inside the TRIM as well.

You may be tempted to use ISBLANK, as in =FILTER(A2:A8,NOT(ISBLANK(A2:A8))). That works for truly empty cells, but ISBLANK returns FALSE for a formula that shows "", so those cells are kept. The <>"" test treats both the same way, which is usually what you want. The guide to ISBLANK, ISNUMBER and the other IS functions explains the difference, and the IS functions lesson lets you test it.

How to stop FILTER showing 0 for blank cells#

FILTER copies values, and the value of an empty cell is 0. Replace empty cells with empty text before filtering:

=FILTER(IF(A2:B8="","",A2:B8),A2:A8<>"")

The IF checks every cell in A2:B8. Empty cells become "" and the rest stay as they are. Now Grace's row shows an empty Shift instead of 0.

A custom number format such as 0;-0;;@ also hides zeros, but only on screen. The IF method changes the values, so a COUNTIF on the result counts correctly.

What if every cell is blank? Fixing #CALC!#

When no cell passes the test, FILTER has nothing to return and shows #CALC!. Use the third argument to show a message instead:

=FILTER(A2:A8,A2:A8<>"","No names yet")

The guide to fixing the #CALC! error covers the other causes.

If you don't have FILTER, you can still hide blanks. Click the filter arrow on the column header (Data > Filter) and untick (Blanks). To remove empty rows for good, select the column, press F5 (Mac: Ctrl+G), choose Special > Blanks and delete the rows. The guide on how to delete blank rows in Excel walks through it safely.

Practise it#

Try the exercise on this page: one FILTER formula removes the gaps from the volunteer list. Then practise the FILTER function in a live spreadsheet, where you type each formula and it checks it instantly. The FILTER function guide is a quick reference for the syntax when you need it.