Jun 14 2022 02:33 PM
I have a workbook with a series of tabs (worksheets) that use the FILTER() function to draw data from a "master" tab. I'm trying to plot 2 series of data on a chart and one of the series has a lot of blank cells. However, these are shown as 0 on the chart even though the Hidden and Empty Cells setting means they shouldn't be (https://support.microsoft.com/en-us/office/display-empty-cells-null-n-a-values-and-hidden-worksheet-...).
I suspect it is because the FILTER() function brings through the data as something different to a blank cell and confuses Excel...but unsure. Anyone encountered this issue?
Jun 14 2022 09:34 PM
Solution@damienid As you have discovered, FILTER returns 0 (zero) when it encounters an empty cell. Zero is neither empty nor null. For your chart to work, you need to replace the empty cells in your table by #N/A's. Mocked up a small example to illustrate what I mean. See attached.
Jun 14 2022 10:17 PM
@Riny_van_Eekelen Great, thank you. I had tried to use #N/A before but had done it wrong so thanks for the example using the NA() function which looks like it has done the trick!
Jun 15 2022 01:38 AM
I would argue the FILTER does return a blank, though that is not how it appears on the worksheet. The formula
= LET(
filtered, FILTER(Table1[[Ref1]:[Ref2]], Table1[Item]<>""),
IF(filtered="", NA(), filtered)
)
discriminates between blanks and zeros in the source data.
Jun 14 2022 09:34 PM
Solution@damienid As you have discovered, FILTER returns 0 (zero) when it encounters an empty cell. Zero is neither empty nor null. For your chart to work, you need to replace the empty cells in your table by #N/A's. Mocked up a small example to illustrate what I mean. See attached.