Unwanted Zeros in Excel Chart Displayed Values
 

Hi, this is a very specific problem I have with an excel bar chart.
There must be a hero somewhere with a solution.

I am charting cells containing excel functions of the type
=IF(A1="","",A1)
=IF(A2="","",A2)
etc
Where cells A1, A2 can contain real number values, genuine zeros, or
genuine blanks

I have to show the Values on the chart above each bar.
The Real numbers and zeros display correctly but if there is a genuine
blank i still see an unwanted "0.0" value label on my chart. I cannot
entertain deleting them individually as I have a huge piece of macro
driven work which will print many thousands of different charts.

Furthermore, using N/A() does NOT work..e.g.
=IF(A1="",N/A(),A1)
as this then Displays an "N/A" on the chart instead. Also unwanted. I
want nothing on my chart at all for a blank.

I have looked high and low for a solution fruitlessly

Any help would be greatly appreciated

thanks
Daniel

Unwanted Zeros in Excel Chart Displayed Values
 

Hi Daniel,
Try ToolsOptionsChartPlot Empty cells as= not plotted (leave gaps)

Ken Johnson

Unwanted Zeros in Excel Chart Displayed Values
 

Hi Daniel,

Or use the autofilter to hide the blanks then ToolsOptionsChart tick
in Plot visible cells only.

Ken Johnson

Unwanted Zeros in Excel Chart Displayed Values
 

Hi Ken,

the chart already has that setting, unfortunately it sees the function
in there and decides that doesn't count as empty and plots.

Daniel

Unwanted Zeros in Excel Chart Displayed Values
 

Again, not that simple. The data itself is in large pivot tables and
cannot be filtered. The data in these pivot tables is dynamic and
changes all the time.