XL: How Chart Axis Limits Are Determined

ID: Q101939

The information in this article applies to:

Microsoft Excel 98 Macintosh Edition
Microsoft Excel 97 for Windows
Microsoft Excel for Windows 95, versions 7.0, 7.0a
Microsoft Excel for Windows, versions 3.x, 4.x, 5.x
Microsoft Excel for Windows NT, version 5.0
Microsoft Excel for the Macintosh, versions 3.x, 4.x

SUMMARY

This article explains how the automatic minimum and maximum limits for chart axis scales are computed in different versions of Microsoft Excel.

MORE INFORMATION

In the following examples, the following names are used to describe certain values.


   Name   Description
   -------------------------------------------------------------------


   yMax   The maximum y value used in your chart.


   yMin   The minimum y value used in your chart.


   xMax   The maximum x value used in your chart. This applies only to
          charts that use x values, such as scatter and bubble charts.

   xMin   The minimum x value used in your chart. This applies only to
          charts that use x values, such as scatter and bubble charts.

When you create a chart in Microsoft Excel, there are three possible scenarios that may apply to your data:

The yMax and yMin values are both non-negative (greater than or equal to zero). This is Scenario One.

The yMax and yMin values are both non-positive (less than or equal to zero). This is Scenario Two.

The yMax value is positive, and the yMin value is negative. This is Scenario Three.

The major unit used by the y-axis is automatically determined by Microsoft Excel, based on all of the data included in the chart. The following scenarios use this default major unit.

Scenario 1: Microsoft Excel 97 and Microsoft Excel 98 Macintosh Edition

In Microsoft Excel 97 and Microsoft Excel 98 Macintosh Edition, the following applies:

If the chart is a 2-D area, column, bar, line or x-y scatter chart, the automatic maximum for the y-axis is the first major unit greater than or equal to the value returned by the following equation:

yMax + 0.05 * ( yMax - yMin )

Otherwise, the automatic maximum for the y-axis is the first major unit greater than or equal to yMax.

If the difference between yMax and yMin is greater than 16.667 percent of the value of yMax, the automatic minimum for the y-axis is zero.

If the difference between yMax and yMin is less than 16.667 percent of the value of yMax, the automatic minimum for the y-axis is the first major unit less than or equal to the value returned by the following equation:

yMin - ( ( yMax - yMin ) / 2 )

Exception: If the chart is an x-y scatter or bubble chart, the automatic minimum for the y-axis is the first major unit less than or equal to yMin.

Scenario 1: All Earlier Versions of Microsoft Excel

In all earlier versions of Microsoft Excel, the following applies:

The automatic maximum for the y-axis is the first major unit greater than or equal to yMax.

If the difference between yMax and yMin is greater than 16.667 percent of the value of yMax, the automatic minimum for the y-axis is zero.

If the difference between yMax and yMin is less than 16.667 percent of the value of yMax, the automatic minimum for the y-axis is the first major unit less than or equal to the value returned by the following equation:

yMin - ( ( yMax - yMin ) / 2 )

Exception: If the chart is an x-y scatter or bubble chart, the automatic minimum for the y-axis is the first major unit less than or equal to yMin.

Scenario 2: Microsoft Excel 97 and Microsoft Excel 98 Macintosh Edition

In Microsoft Excel 97 and Microsoft Excel 98 Macintosh Edition, the following applies:

If the chart is a 2-D area, column, bar, line or x-y scatter chart, the automatic minimum for the y-axis is the first major unit less than or equal to the value returned by the following equation:

yMin + 0.05 * ( yMin - yMax )

Otherwise, the automatic minimum for the y-axis is the first major unit less than or equal to yMin.

If the difference between yMax and yMin is greater than 16.667 percent of the value of yMin, the automatic maximum for the y-axis is zero.

If the difference between yMax and yMin is less than 16.667 percent of the value of yMin, the automatic maximum for the y-axis is the first major unit greater than or equal to the value returned by the following equation:

yMax - ( ( yMin - yMax ) / 2 )

Exception: If the chart is an x-y scatter or bubble chart, the automatic maximum for the y-axis is the first major unit greater than or equal to yMax.

Scenario 2: All Earlier Versions of Microsoft Excel

In all earlier versions of Microsoft Excel, the following applies:

The automatic minimum for the y-axis is the first major unit less than or equal to yMin.

If the difference between yMax and yMin is greater than 16.667 percent of the value of yMin, the automatic maximum for the y-axis is zero.

If the difference between yMax and yMin is less than 16.667 percent of the value of yMin, the automatic maximum for the y-axis is the first major unit greater than or equal to the value returned by the following equation:

yMax - ( ( yMin - yMax ) / 2 )

Exception: If the chart is an x-y scatter or bubble chart, the automatic maximum for the y-axis is the first major unit greater than or equal to yMax.

Scenario 3: Microsoft Excel 97 and Microsoft Excel 98 Macintosh Edition

In Microsoft Excel 97 and Microsoft Excel 98 Macintosh Edition, the following applies:

The automatic maximum for the y-axis is the first major unit greater than or equal to the value returned by the following equation:

yMax + 0.05 * ( yMax - yMin )

The automatic minimum for the y-axis is the first major unit less than or equal to the value returned by the following equation:

yMin + 0.05 * ( yMin - yMax )

Scenario 3: All Earlier Versions of Microsoft Excel

In all earlier versions of Microsoft Excel, the following applies:

The automatic maximum for the y-axis is the first major unit greater than or equal to yMax.

The automatic minimum for the y-axis is the first major unit less than or equal to yMin.

X-axis Limitations

The above information also applies to charts that use x values, such as x-y scatter charts and bubble charts. For these types of charts, substitute xMax and xMin for yMax and yMin in the above scenarios.

Additional query words: XL98 XL97 XL7 XL5 4.00 4.0a 4.00a chartwizard


Keywords          : kbtool xlchart 
Version           : WINDOWS:3.0,4.0,4.0a,5.0,5.0c,7.0,7.0a,97; MACINTOSH: 3.0,4.0,98
Platform          : MACINTOSH OS/2 WINDOWS 
Issue type        :

Last Reviewed: April 6, 1999