Excel Pivot Chart: remove zero values

0
votes

I have Pivot table with one Axis field (containing 13 elements 0-12) and two Values series. One of the series (A) has 13 values. The other series (B) has only 3 elements.

When I make a pivot table, I'm able to hide the zero values using this explanation :

Excel 2010: Hide 0 values in Pivot

However when I make a pivot chart (line chart) the series B keep showing zero (or a line dropping to the x-axis). I want the line of the series B to stop when there are no values (the line should be floating on the chart).

Anyone has an idea how to achieve this ?

2
excelchartspivot-table
I don't think that's possible. Maybe you could copy the pivot table to another sheet as values and delete the rows with no values and then run the chart? - Jerry

2 Answers

0
votes

I faced a similar issue where the Series drops down to Zero.

I have a Pivot Chart linked to a Pivot Table with multiple Series for 7 different Key Figures & other Fields. I pick up my source data from BEx/BI using Formula to bring in some extra Columns & apply some miscellaneous formatting. This data is then forms the basis for my Pivot Table & Pivot Chart.

I found that you cannot filter the Pivot Table as the Fields do not contain items. You cannot use NA() or even "" (double-quotes) using Formula - this is because when using different kinds of aggregation within the Pivot Table the NA() cannot be SUMMED and hence the Series is not displayed. It also appears that setting a Cell to blank using "" (double-quotes) in a Formula has no effect either. I even tried #DIV/0! errors & then hiding the errors - no luck.

As a workaround, the "only" way I have found so far that works is to manually Filter my final data input for the Pivot Table by zero and then simply delete the values. Refreshing the Pivot Table then allows the Pivot Chart to hide the blanks and the data points will not be displayed. This means that any Series with intermittent values are displayed correctly.

0
votes

Found a trick: in the data field use =if(x=0,na(),x/y). The graph will be fine but the table will look ugly (with #NAs). You can use conditional formatting on the table to set the font color as white when there is an error.

Voila!!!