My pivot chart has the wrong Y axis values but correct data point values

Question

I created a pivot chart based on some raw data for the x axis (dates) and 4 calculated fields for the Y values.

The values on resulting lines are correct (see the data label at the end of the line) but the Y axis is off by about 100, but not off by any consistent amount. I have played with auto axis on and off, turn log scale on and off. All to no avail.

Does anyone have any thoughts?

Answer 1

You are using a Stacked Line chart. This causes your data points to accumulate amongst all the series of data. So, your purple line which should have the value of $53 is actually being reflected as a $53 delta on top of ($43 (green) + $30 (red) + $19 (blue)). If you change the chart type to Line, it will fix this.

Answer 2

Have you tried manually modifying the y_min, y_max, y_step values ? (in y axis properties)

Btw, what is a "pivot chart" ... this is a regular line plot, is it not ?

Answer 3

Very strange indeed. Have you tried moving the chart to a new sheet (ie so it is a chart sheet, not an object on a worksheet)? What happens if you change the chart type to a column (for example)? and back?

The lowest (blue) series seems about right - the $19 is just below the $20 gridline. The spacing between them also looks OK - 11, 13, 10. Except the 19 would be a little low compared to those space values, as if the Y axis did not have a true zero.

Daft question time - why use a Pivot Chart at all if you have all these data points? Why not go for a standard XY chart?

What Excel version is this? (looks like 2007, but could be 2010 I guess) Can you share a (sanitised) version of the data?