Auto Scaling of Y-axis
 

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...b;en-us;213644

----
Regards,
John Mansfield
http://www.pdbook.com

"Nicole" wrote:

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?

Nicole -

Alternatively, set up a few cells with formulas that use an algorithm to pick
pleasing axis parameters, and use a technique like this:

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

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

Nicole wrote:

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?

Also, Tushar Mehta has an automated solution on his web site. Go to
http://tushar-mehta.com and look for the Autochart Manager.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

Jon Peltier wrote:

Nicole -

Alternatively, set up a few cells with formulas that use an algorithm to
pick pleasing axis parameters, and use a technique like this:

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

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

Nicole wrote:

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?

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.

Looked at AutoChart Manager
http://www.tushar-mehta.com/excel/so...rt/index.html?

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article ,
says...
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.