How can I return blank cells as it is instead of printing as Zeros while using filter function

Question

["referring to the data shown in the image while using the filter function and getting the return values, How can I return blank cells as it is instead of printing as Zeros while using filter function"]

=FILTER(A2:C13,A2:A13=E1," ")

referring to the data shown in the image while using the filter function and getting the return values, How can I return blank cells as it is instead of printing as Zeros while using filter function

Answer 1

You could try:

---

• Formula used in cell E2

=LET(x,IF(A2:C13="","",A2:C13),FILTER(x,TAKE(x,,1)=E1))

---

Another possible way could be adding &"" in front of your array argument. Note that it will convert your numeric values to text so you can use a VALUE() function or multiple by 1 to convert it back to numeric when required for further calculation.

---

• Formula used in cell E8

=FILTER(A2:C13&"",A2:A13=E1," ")

---

Or, you can use as proposed by VBasic2008 Sir

---

• Formula used in cell E14

=FILTER(IF(A2:C13="","",A2:C13),A2:A13=E1," ")

---

Answer 2

There are multiple ways of solving it. I would say changing the Excel configuration would be the first option to consider to solve this issue since it doesn't require modifying the original formula: File -> Options -> Advanced and unmark the following highlighted option: Show a zero in cells that have zero value:

It applies only to the selected worksheet. Here is the output:

Not having this option available, for example when using Excel Web, then you need to use a formula. I prefer not to change the data type of the output and to do the transformation in the output rather than in the input, because the output is a subset of the input, so it would be more efficient to do any transformation in a reduced subset. For example:

=LET(f, FILTER(A2:C4, A2:A4=E1), IF(f="","",f))

or

=LET(f, FILTER(A2:C4, A2:A4=E1), IF(LEN(f),f,""))

Here is the output (having the referred Excel option marked):

By the way, this question should be marked as duplicated due to the following question: Excel FILTER() returning 0 for blank cells, but I like more the answers provided here than in the referred link and also the OP of this question was more precise in its formulation than in the referred question.

Answer 3

I solved it by using the IF function. =IF(FILTER(Array, Array Conditions)=0, "",FILTER(Array, Array Conditions))