Search code examples
excelexcel-formulaoffice365dynamic-arrays

Excel Dynamic Array to include blank cells

Is there a way to keep the empty cells when filtering data with the FILTER() function? As for now, Excel is outputing the value 0 when an empty cell occurs.

For Example if this is a table in Excel (named tbl):

Name  | City  | Street
----------------------
name1 | city1 | street1
name2 | city1 |

The function:

=FILTER(tbl;tbl[City]="city1")

Returned as above but with "0" instead of an empty cell.

Solution

  • Answering my own question, since I solved it myself and there may be more people having the same issue.

    You can use:

    =FILTER(IF(tbl="";"";tbl);tbl[City]="city1")

    To get an empty cell instead of a "0".