Make pie chart ignore blank cells (but with formula) or #N/A-cells

[boeb]

Hi,

I'm working on a project to publish some data compiled in Excel (using Excel-online embedded function) and need to display the data into different charts, pie charts among others.
I want the tables to be interactive, to when the user input data (eg. a percentage) in a simplified Pivot-table. I want to chart the rows only which has values.

My current solution (as using macro etc with Excel-online is not possible) is using this formula retrieving data from Table 1 (name range table 4) into a new table and then creating charts. However, I cant get the pie chart to ignore the blanks.
=IF(Table4[@[Ange procent]]<>0;Table4[@[Ange procent]];NA())

Please see my attached screenshot.

I tried to experiment with the "Show empty cells as gaps/zero" but I believe that would only work with other type of charts and does not make Excel ignore the cells.

Any guidance much appreciated,
Tobey

 

[Saba Sabaratnam]

You could bring the data to the second table and hide all rows with zero data. See below.

Kind regards

Saba