Pivot Chart

Question

Hi,

I have created a combination pivot chart in Office 365. The first data series is a set of numbers that are always positive and I have plotted them on a secondary axis. The second data series are percentages and I have plotted them as a stocked colum chart on primary axis. They are always positive. The third data series are again percentages but might be positive or negative.(It is a calculation that subtracts .75 from second data series to see whether the pass rate percentage is higher (=) or lower ) negative than 75%) I plotted these as a stacked column chart. The problem I have is that some of the positive values in the first data series are showing below 0 on the secondary axis. Is there any way I can change settings so that the numbers in the first data series always appear above the x axis, in other words the 0 secondary axis appears beside X axis and not below it. It looks like they are being affected by the negative percentage values on the primary axis but I want them to be independent of that axis. Please see image where I want the 6,053 the 6,284 the 42 etc to appear about the x axis line. Thanks in advance.

Answer 1

Thanks for all the help Hans. Any help on this much appreciated. I'm trying to set up a list of product buttons and attach a macro to each one that select the product from the slicer and applies the appropriate axes values but the code below won't work.

I created a pivot chart with primary and secondary axes. I have a slicer beside chart that is a list of products. Because the primary axis may be negative in some cases I need to apply different minimum and maximum values for each axis for each product. I tried recording a macro that would run that would A. select the product from the slicer and B. set the appropriate primary and secondary axes min and max settings but when I try to run the macro it won't work correctly, in other words it won't just select 2D Design exclusively and apply axes settings.

Without a macro if I pick another product the axes stay the same as the last product which distorts the display with numbers, especially when negative on primary axis.

Sub TwoD()

'

' TwoD Macro

'

With ActiveWorkbook.SlicerCaches("Slicer_Module112")

.SlicerItems("2D Design").Selected = True

End With

ActiveSheet.ChartObjects("Chart 3").Activate

ActiveChart.Axes(xlValue, xlSecondary).Select

ActiveChart.Axes(xlValue, xlSecondary).MinimumScale = -500

ActiveChart.Axes(xlValue, xlSecondary).MaximumScale = 900

ActiveChart.Axes(xlValue).Select

ActiveChart.Axes(xlValue).MinimumScale = -0.6

End Sub

Answer 2

Many thanks Hans, I'll do that.

Answer 3

That requires a trick. See Conditional Formatting – Chart Data Labels

Answer 4

Many thanks for the reply. I just wondered would it be possible to have conditional formatting on the data labels as in screenshot above but negative percents red background white text, positive percents green background white text. I need it to be dynamic so that if I can a module in the slicer the formatting automatically changes. Thanks in advance.

Answer 5

Set the minimum of the primary value axis to -40% and the maximum to 120%

Set the minimum of the secondary value axis to -40000 and the maximum to 120000.

Since the +/- have the same ratios, the 0 will be in the same position on both axes.