Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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?

Image link

share|improve this question

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.

share|improve this answer
comment from Mark Harnett: Stacked line it was. Thanks. – quack quixote May 27 '10 at 23:00
Thank you, I'd made the same mistake and was going crazy. – Todd Pierzina Nov 8 '11 at 16:22

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 ?

share|improve this answer
A pivot chart is a chart tied to a pivot table, which is a particular object in Excel (and other spreadsheets) that allows easy on-the-fly switching of variables among different axes, handy auto-aggregations, etc. – phoebus Jan 16 '10 at 0:29

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?

share|improve this answer

Workaround: Create a new series (stacked format) on the primary axis that is equal to the series with the highest values in the data set (the purple data set in the example above).

Once created, colour this new line (or bar in my case) the same as the background (border too) so it does not appear on the graph. Now switch all the series you had before to the secondary axis. This will create two axes on the graph, hopefully with the proper one (in terms of scale) on the right.

Now just colour the axis you don't want the same as the background. Don't delete it - it will muck it all up.

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.