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

boeb

New Member
Joined
Aug 19, 2020
Messages
2
Office Version
  1. 2016
Platform
  1. Windows
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
 

Attachments

  • Pie chart - NA values.PNG
    Pie chart - NA values.PNG
    10.7 KB · Views: 11

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

Saba Sabaratnam

Active Member
Joined
May 26, 2018
Messages
403
Office Version
  1. 365
You could bring the data to the second table and hide all rows with zero data. See below.

1625346409963.png


1625346453351.png


Kind regards

Saba
 

Forum statistics

Threads
1,148,250
Messages
5,745,638
Members
423,965
Latest member
visionquest1972

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top