MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Line Chart Y Axis Question


Posted by Lisa on July 10, 2001 6:50 AM

I've built a line chart where the Y axis is in dollars. One value is very high on the chart and the remaining values are very low on the chart, leaving a huge space in the middle of the chart. Is there a way to put a break in the chart so that the middle part of the chart does not show and I have more detail on the upper and lower part of the chart? Any suggestions are appreciated! Thanks!


Posted by Mark W. on July 10, 2001 6:58 AM

> ...Is there a way to put a break in the chart...

No.

Have you considered using logarithmic scale?

Posted by Lisa on July 10, 2001 7:09 AM

No, can you explain how??

Posted by Mark W. on July 10, 2001 7:28 AM

Right-click the Y-axis, choose the Format Axis...
popup command, check "Logarithmic scale" on the
"Scale" tab. I trust that you understand that
zero values are incompatible with this scaling.

Posted by Lisa on July 10, 2001 7:32 AM

Thank you much....that did the trick!!

Posted by Eric on July 10, 2001 7:52 AM

Double click on the Y axis, select the scale tab, and check the "logarithmic scale" box

Another option is to plot the same data twice on the chart. Associate the first plot with the Y axis (the normal way graphs are created) and set the range so that most of the data is visible below where you want the axis break to occur (of course the extreme upper values will not be displayed as they are out of range). Then create a second series of the same data, right click on the new series, and under "format data series"-->select the "axis" tab and click on "plot data on secondary axis". A new axis shows up on the right side of the graph and you can customize its scale as well.

So to get Excel charts to emulate an axis break you can plot the same data twice, customize the Y axis range to fit the lower range of your data, then associate the second plot with a second Y axis and associate it with the upper range. The trick here is that the manual Y axis range you specify for the first (low range) Y axis should include an upper range (above the "axis break") that has enough room to contain ALL of the upper range data on the second Y axis with no overlap. As an example, let's say you have low range data from 0 to 100 and high range data from 400 to 500 all in the same series. Plot the series twice. Set the first Axis range from 0 to 200 and the second axis range from 300 to 500. Then all of the low range data falls into the bottom of the graph and the high range values are at the top.
Convoluted, but it works- hope this helps