Excel FILTER() returning 0 for blank cells

Question

I suspect this has been asked previously, but I can't find it.

FILTER() will often return a 0 for blank rows, even when a return string is specified.

Using filter() I am often getting a 0 return value for empty cells. Assume these 6 rows of data in column A:

abc
xyz
abc

xyz
abc

If I use

FILTER(A10:A15, A10:A15 <> "xyz", "")

I get back the following (sometimes):

abc
abc
  0
abc

This seems to be somewhat unpredictable. Having 0s show up where I don't want is a problem and requires extra logic or filtering. Is this a known issue? Other than explicitly filtering out blank cells, is there another way to solve this problem?

This seems to work to get rid of 0 returning for empty cells:

FILTER(A10:A15, (A10:A15 <> "xyz") * (A10:A15 <> ""), "")

This is returned:

abc
abc
abc

I can live with this solution but it should be unnecessary. I also end up explaining why I'm filtering empty cells to people over and over.

btw, filtering out 0 doesn't work. FILTER() seems to see the cell as a blank string when reading it, but not when outputting the result.

FILTER(A10:A15, (A10:A15 <> "xyz") * (A10:A15 <> 0), "")

This will return the original results with the 0.

If there is no better solution, is there an explanation?

Answer 1

Solution for mixed data ranges

Other solutions mentioned here, will not work correctly when your range contains mixed data. The solutions work for either all textual data or all numerical data. But these won't work for mixed data, as you can see in the comparison below.

This will work with mixed data:

=FILTER( IF(A10:A15="", "", A10:A15), A10:A15<>"xyz")

.

Comparing solutions

Note 1: Orange fields are incorrect/undesired, green ones are correct.

Note 2: "Waarde" is Dutch for "value" (error).

Original data and basic filter =FILTER(I3:K6; I3:I6 <> "")

FILTER(... & "") filter — =FILTER(I3:K6 & ""; I3:I6 <> "")

Same as with the SUBSTITUTE() solution.

VALUE(...) filter — =VALUE(FILTER(I3:K6; I3:I6 <> ""))

VALUE(FILTER(... & "")) filter — =VALUE(FILTER(I3:K6 & ""; I3:I6 <> ""))

✨ Perfect 🙏 filter — =FILTER(IF(I3:K6 = ""; ""; I3:K6); I3:I6 <> "")

Some explanation for the bot :)

If a cell in the range is empty, it will be an empty string ("") else it will use the original value. This way only specific cells will be replaced with textual empty cell, leaving numeric cells as-is for Excel to auto-interpret as numeric or other data.

Answer 2

FOR NUMRIC DATA YOU CAN DO LIKE THIS

IF(ISBLANK(FILTER(...)),"",FILTER(...))

Answer 3

Another, less elegant solution, is to replace empty cells in your source array with a space character " ".

I found this thread while looking for a quick answer while reformatting data. So adding a space was quicker that the other solutions. I really like that VALUE(FILTER( &"", answer as well!

Answer 4

I figured out what seems to be a different generic solution: replace empty stings in the first argument to single quotes, so generically:

=FILTER(SUBSTITUTE($X:$Z,"","'"),*whatever*,*whatever*)

or in the case of this question:

=FILTER(SUBSTITUTE($A10:A15,"","'"),A10:A15 <> "xyz", "")

I simply makes use of how Excel handles the single quote, I haven't found any downsides yet, though I didn't test it thoroughly.

Answer 5

You may add &"" in front of your array argument.

For example,

FILTER(A10:A15 &"", (A10:A15 <> "xyz") * (A10:A15 <> ""), "")

This will work fine with text values but it will convert your numeric data to text, in which case, you may convert it back to numeric using the VALUE function.

Suppose if A10:A15 was numeric data, you may use:

VALUE(FILTER(A10:A15 &"", (A10:A15 > 1000) * (A10:A15 <> ""), ""))

Answer 6

As explained here, the third argument (If_empty) is not for cases when there is a blank.

FILTER(array, include, [if_empty])

Where:
Array (required) - the range or array of values that you want to filter.
Include (required) - the criteria supplied as a Boolean array (TRUE and FALSE values). Its height (when data is in columns) or width (when data is in rows) must be equal to that of the array argument.
If_empty (optional) - the value to return when no entries meet the criteria.

Your function would return the string specified in If_empty if all the values in Array were "xyz". I.e., the criteria in Include would not evaluate to TRUE for any non-blank value in the range Array.

Also, it seems that your last formula does not anymore return zeros: