SOLVED

Empty cells are displayed in a chart when using FILTER() function, when I don't want them to be

%3CLINGO-SUB%20id%3D%22lingo-sub-3507739%22%20slang%3D%22en-US%22%3EEmpty%20cells%20are%20displayed%20in%20a%20chart%20when%20using%20FILTER()%20function%2C%20when%20I%20don't%20want%20them%20to%20be%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3507739%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20workbook%20with%20a%20series%20of%20tabs%20(worksheets)%20that%20use%20the%20FILTER()%20function%20to%20draw%20data%20from%20a%20%22master%22%20tab.%20I'm%20trying%20to%20plot%202%20series%20of%20data%20on%20a%20chart%20and%20one%20of%20the%20series%20has%20a%20lot%20of%20blank%20cells.%20However%2C%20these%20are%20shown%20as%200%20on%20the%20chart%20even%20though%20the%20Hidden%20and%20Empty%20Cells%20setting%20means%20they%20shouldn't%20be%20(%3CA%20href%3D%22https%3A%2F%2Fsupport.microsoft.com%2Fen-us%2Foffice%2Fdisplay-empty-cells-null-n-a-values-and-hidden-worksheet-data-in-a-chart-a1ee6f0c-192f-4248-abeb-9ca49cb92274%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3Ehttps%3A%2F%2Fsupport.microsoft.com%2Fen-us%2Foffice%2Fdisplay-empty-cells-null-n-a-values-and-hidden-worksheet-data-in-a-chart-a1ee6f0c-192f-4248-abeb-9ca49cb92274%3C%2FA%3E).%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20suspect%20it%20is%20because%20the%20FILTER()%20function%20brings%20through%20the%20data%20as%20something%20different%20to%20a%20blank%20cell%20and%20confuses%20Excel...but%20unsure.%20Anyone%20encountered%20this%20issue%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3507739%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3508568%22%20slang%3D%22en-US%22%3ERe%3A%20Empty%20cells%20are%20displayed%20in%20a%20chart%20when%20using%20FILTER()%20function%2C%20when%20I%20don't%20want%20them%20to%20be%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3508568%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1424386%22%20target%3D%22_blank%22%3E%40damienid%3C%2FA%3E%26nbsp%3BAs%20you%20have%20discovered%2C%20FILTER%20returns%200%20(zero)%20when%20it%20encounters%20an%20empty%20cell.%20Zero%20is%20neither%20empty%20nor%20null.%20For%20your%20chart%20to%20work%2C%20you%20need%20to%20replace%20the%20empty%20cells%20in%20your%20table%20by%20%23N%2FA's.%20Mocked%20up%20a%20small%20example%20to%20illustrate%20what%20I%20mean.%20See%20attached.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3508690%22%20slang%3D%22en-US%22%3ERe%3A%20Empty%20cells%20are%20displayed%20in%20a%20chart%20when%20using%20FILTER()%20function%2C%20when%20I%20don't%20want%20them%20to%20be%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3508690%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3E%26nbsp%3BGreat%2C%20thank%20you.%20I%20had%20tried%20to%20use%20%23N%2FA%20before%20but%20had%20done%20it%20wrong%20so%20thanks%20for%20the%20example%20using%20the%20NA()%20function%20which%20looks%20like%20it%20has%20done%20the%20trick!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3509204%22%20slang%3D%22en-US%22%3ERe%3A%20Empty%20cells%20are%20displayed%20in%20a%20chart%20when%20using%20FILTER()%20function%2C%20when%20I%20don't%20want%20them%20to%20be%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3509204%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20would%20argue%20the%20FILTER%20does%20return%20a%20blank%2C%20though%20that%20is%20not%20how%20it%20appears%20on%20the%20worksheet.%26nbsp%3B%20The%20formula%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3E%3D%20LET(%0A%20%20%20%20%20%20filtered%2C%20FILTER(Table1%5B%5BRef1%5D%3A%5BRef2%5D%5D%2C%20Table1%5BItem%5D%26lt%3B%26gt%3B%22%22)%2C%0A%20%20%20%20%20%20IF(filtered%3D%22%22%2C%20NA()%2C%20filtered)%0A%20%20)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3Ediscriminates%20between%20blanks%20and%20zeros%20in%20the%20source%20data.%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

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?

3 Replies
best response confirmed by damienid (New Contributor)
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.

@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!

@Riny_van_Eekelen 

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.