Formatting charts to not show large numbers but still scale

[dorokhin]

I'm having some trouble getting my charts to show up like I want them.

I'm graphing percentage increases and generally the numbers are between 60 and 150. However, sometimes there's numbers like 200, 300 and a small few numbers as high as 5000.

I want my graph to auto scale the y axis if the numbers are reasonably small (most of them are) but I'd like to set a cut off point at the high end if the data set being graphed includes one of the outliers. So I want it to auto scale but not any higher than, say, 300. Is there any way to do this?

 

[Gerald Higgins]

How about using another column to capture your data IF it meets a certain criteria (such as being below the limit you decide for outlying values) and base your chart on that ?

For example, let's say your data of percentage increases is in Col A, starting on row 2.
In B1, input the highest value you want to chart.
Then in B2, insert this formula and copy down as required.

=if(a1>$b$1,$b$1,a1)

Then set your data series to use Col B instead of Col A.

 

[klb]

Set a Max number for that axis.

 

[Jon Peltier]

You could adapt this example not to show a break if the maximum is not too extreme:

http://peltiertech.com/Excel/Charts/BrokenYAxis.html