Auto Scaling of Y-axis

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a spreadsheet with multiple tabs for entering daily data - to populate
a chart on each tab. The spreadsheet data is backed up & then wiped out
monthly.

The chart keeps a daily information, as well as lines for 2 & 3 sigma limits
based on the previous month's data.

The Y-axis varies on each tab - depending on the difference between the
upper & lower sigma lines.

Since the sigma ranges change monthly, it doesn't work to just put in a set
y-axis min & max.

With auto-scale on, sometimes Excel re-scales after entering the daily
information, other times it doesn't (it sets the automatic minimum to zero,
when it should be around 400 or 600).

Is there a way to force Excel to rescale & not see zero?
 
Nichole,

You can auto-scale a chart with a single Y axes by using a macro.

To add the macro to your spreadsheet, open your spreadsheet and go to to
Tools -> Macro -> Visual Basic Editor. Go to the Visual Basic Toolbar and
hit Insert -> Module. You should have an empty code module called "Module1"
showing. Copy the following code into that module:

Sub AutoScaleYAxes()
Dim ValuesArray(), SeriesValues As Variant
Dim Ctr As Integer, TotCtr As Integer
With ActiveChart
For Each X In .SeriesCollection
SeriesValues = X.Values
ReDim Preserve ValuesArray(1 To TotCtr + UBound(SeriesValues))
For Ctr = 1 To UBound(SeriesValues)
ValuesArray(Ctr + TotCtr) = SeriesValues(Ctr)
Next
TotCtr = TotCtr + UBound(SeriesValues)
Next
.Axes(xlValue).MinimumScaleIsAuto = True
.Axes(xlValue).MaximumScaleIsAuto = True
.Axes(xlValue).MinimumScale = Application.Min(ValuesArray)
.Axes(xlValue).MaximumScale = Application.Max(ValuesArray)
End With
End Sub

After copying the code, go back to the Visual Basic Editor Toolbar and go to
File -> Close. You should be back to your regular spreadsheet view.

To scale the chart, activate the chart by clicking on it. Then run the
macro by going to the standard toolbar and hitting Tools -> Macro -> Macros.
Click on the macro that says "AutoScaleYAxes" and press the run button.

The macro scales the chart to the maximum and minimum data values. For the
purposes of spacing, if you want to scale the plot area maximum a little
higher and the plot area minimum a little lower then you will need to add a
number or formula to the Application.Min(ValuesArray) and
Application.Max(ValuesArray) lines. For example:

..Axes(xlValue).MinimumScale = Application.Min(ValuesArray) - 1
..Axes(xlValue).MaximumScale = Application.Max(ValuesArray) + 1

Depending on what your data really is the constants (+1 and -1 in this case)
may need to change. Or, you may need to add a formula to do the calculation.


The code above can be found in this Microsoft KnowledgeBase article located
at the following address:

http://support.microsoft.com/default.aspx?scid=kb;en-us;213644
 
Jon & John,

Thank you both for your replies. They are helpful!

Jon - I am extremely interested in the Dynamic Control Chart on your website
- it would work perfectly for the charts I've described here. However, I'm a
bit lost trying to figure them out. This would solve most of the problems
and a lot of extra work that is part of my charts.

Thanks.
 
Back
Top