Peltier Tech

Peltier Technical Services - Excel Charts and Programming

How Excel Calculates Automatic Chart Axis Limits

by 35 Comments

Monday, February 27, 2012 by Jon Peltier
Peltier Technical Services, Inc., Copyright © 2023, All rights reserved.
 

Excel’s automatic axis scaling often seems somewhat mysterious, and it’s not easy to find information about it. Microsoft has a couple articles in the MSDN knowledge base, How Chart Axis Limits Are Determined and XL2000: How Chart Axis Limits Are Determined, but the most recent of these was directed at Excel 2000. The algorithms described in these articles are unchanged in Excel 2002 and 2003, and seem to be the same in Excel 2007 and 2010 as well.

The Automatic Axis Scaling Rules

I won’t go into all scenarios, but will discuss cases where the minimum and maximum values are both greater than zero. The behavior is the same for Y values in line, column, bar, area, bubble, and XY charts, and for X values in XY and bubble charts (substitute X for Y in this discussion for X axes). Y axes in other chart types (particularly 3-D charts, which you should avoid anyway) may behave slightly differently.

The automatic maximum Y axis scale value is the first major unit above Ymax + (Ymax – Ymin)/20, where Ymax is the maximum Y value, and Ymin is the minimum Y value or the minimum Y axis setting if it has been fixed.

If Ymin is less than 5/6 of the Ymax, the automatic minimum Y axis scale value is zero. If Ymin is 5/6 of Ymax or greater, then the automatic minimum Y axis scale value is the first major unit less than or equal to Ymin – (Ymax – Ymin)/20 (the two MSDN articles have an unfortunate typographical error, showing the divisor equal to 2, not 20).

The automatic axis maximum value is not particularly confusing, but the automatic minimum certainly can be.

The automatic minimum of zero persists for Ymin up to nearly 5/6 of Ymax, which means the bottom nearly 5/6 of the chart will have no data. This is a lot of blank space for a line or XY chart.

However, for Ymin greater than 5/6 of Ymax, the automatic minimum is greater than zero, which is a no-no for column, bar, and area charts. Read Bar Chart Value Axis Scale Must Include Zero if you don’t remember why.

Given these algorithms, one can determine how Excel is likely to scale their axes, with one glaring exception. There is no information anywhere about how Excel calculates the automatic axis major unit spacing. It seems to be an intricate function of overall chart area size, plot area size and position within the chart area, tick label font and font size as well as bold and italic settings, tick label number format, window zoom setting, and probably more parameters I can’t think of.

Demonstration

The following data will be used to show this behavior in Excel 2010. The XY charts used will use column A as X values, and one of the columns B, C, or D as Y values.

The values in column B are generic positive Y values, with the maximum substantially greater than the minimum. The values in columns C and D are very similar. In column C, the minimum is just under 5/6 of the maximum (the maximum is just over 1.2 times the minimum), while in column D, the minimum is exactly 5/6 of the maximum (the maximum is exactly 1.2 times the minimum). These ratios are calculated in C5:D6.

Data for axis autoscaling example

This XY chart shows the general behavior. Ymax + (Ymax – Ymin)/20 = 3.1, so the automatic maximum is the first major unit greater than this, or 3.5. Ymin is much lower than 5/6 of Ymax, so the automatic minimum is 0.

If we fix the axis major unit at 1, the automatic maximum is the first major unit greater than 3.1, or 4. The minimum is unchanged.

If we fix the axis major unit at 0.25, the automatic maximum is the first major unit greater than 3.1, or 3.25. The minimum is unchanged.

If we fix the axis major unit at 0.1, the automatic maximum is the first major unit greater than 3.1, or 3.2. The minimum is unchanged.

If we fix the axis minimum at 2, Ymax + (Ymax – Ymin)/20 changes to 3.05. If we keep the axis major unit at 0.1, so the automatic maximum is the first major unit greater than 3.05, or 3.1.

If Ymin is less than 5/6 of Ymax, the automatic minimum is zero. I carried this to the extreme of 2.49999999999999, the maximum digits used by Excel, just to test the accuracy of the stated percentage in the MSDN articles.

If Ymin is exactly 5/6 of Ymax, the automatic minimum is not longer zero, but is instead the first major unit below Ymin – (Ymax – Ymin)/20. The difference between this minimum and the minimum in the above chart is drastic, given the minor change in Ymin.

It’s useful to understand Excel’s automatic axis scaling rules. It also is good to know how to compute your own axis scale parameters. Upcoming posts will show user defined functions in VBA and worksheet formulas to compute axis scale parameters.

More Axis Scale Articles

Posted: Monday, February 27th, 2012 under Chart Axes.
Tags: .
Comments: 35

Peltier Tech Charts for Excel

Microsoft MVP Logo

Reader Interactions

Comments

  2. bill says

    this is a great article. i have tried to “manage” axis min/max with some sucess using formulas and VBA to force the calculated min/max limits. i recently ran into issues because i was unable to figure out how to apply my min max formulas when y-axis min could be above zero or below zero (depending on the data set) and i needed to leave the lower third of the chart free for x-axis “category” labels (labels are formulas that also vary by dataset) coming in as point labels to a second data series where y value is exactly one quarter of the min/max calculation.

  3. JimC says

    Also looking forward to your insight on axis scaling algorithms. I’ve made my own clumsy attempts that are in need of refinement.

  5. Alan Parry says

    See link below to download a picture and a sample file.
    I have a chart that displays a blip in the middle of the data although all the data is exactly the same. The data is created on a another sheet using formulas, and then pasted into column D using “paste values”. If I look at the maximum auto setting for the y-axis the value is -212.1499999……. I have shown the values with 30 decimal places so that one can see all the values are the same. However if I now copy cell D5 down to D6:D25 I get a straight line; Maximum y-axis value is now zero. Although one can’t see any difference, there is obviously a difference in values whether one pastes values or types them in. Any ideas why there is this anomaly ? Thanks for your time, Alan Parry

    http://www.4shared.com/folder/80D1hRhR/Excel.html

  6. Jon Peltier says

    The data differs because of the error converting base 2 computer values to base 10 values. It’s not Excel, it’s how computers work. The error is in the 15th or 16th total digit, not the 30th decimal digit (this many digits makes no sense: try to enter your 16 digit credit card number as a numeric value: Excel turns the last digit to a zero). The chart is struggling to plot such miniscule differences on its axis. I can’t see the actual data, because 4shared wants me to create an account to download the Excel file.

  8. Joan M. Claveria says

    Excellent job! It has allowed me to go one step further in a situation I am faced to rather frequently, but still not reach the end.
    I often find clarifying to allow the user to zoom in into different areas of a complex XY chart to better appreciate some details. Ideally without the need for her/him to go into changing the chart axis limits each time. The user can define his range of interest by entering min and max values of let’s say the Y axis (alternately it could be the X axis) in specific cells. Then, by means of X and Y dynamic ranges referenced in the chart SERIES statement, the chart is updated and focuses on the area specified by the user. However, up to now, the zooming feature may or may not be that good: depending on how the 5/6 rule you found out applies to the specific ranges, the (0,0) point appears in the chart, and if this happens the zooming is rather poor.
    I work ofthen with a chart type in which Y (celsius temperatures) range from 0 to ~900ºC and X are percentages (0-100%). I found a very straightforward way of getting rid of the 5/6 rule inconvenience: adding 1000 or 10000 to both Y and X values: the zooming is always maximum, filling practically all of the chart area. However there is still a draw back: the axis value labels show now big numbers which make no sense to the user. I miss a kind of formatting the numbers such that the first digit (if I added 1000) or the two first digits (if I added 10000) remain hidden or blanked.
    Thanks for your time.

  9. Jon Peltier says

    Joan –

    The usual approach in this case is to hide the built-in axis labels, then add XY series with hidden points where you want the labels to appear, and add data labels to these points. However, these added points interfere with the calculated axis limits.

    I will often use axes with a scale of 0 to 1 (where 0 is the minimum and y the maximum of the scale I wish to use). I scale my data accordingly so the points are plotted along the 0 to 1 scale where they fit between min and max (so 700 on a scale of 600 to 1000 falls at 0.25). Then I add labels as described above.

  10. Mark Reece says

    I use the Candlestick charts a lot and the rules you seem to get 2 whole major units below below the minimum value.

    On the scaling of the tick size – it seems to me that Excel aims for about 5-11 major ticks on the vertical axis – and that doesn’t seem to vary if I stretch the chart excessively.

    But my wish: to be able to substitute my own values for the min, max and number of tick values on each axis.

  12. ravi says

    if the high low equation of Microsoft is based on 5/6 ratio as explained then in candle stick charts many view failures will be seen. Please suggest a way out as my chart loads data from multiple sheets based on a drop down list box selection.

  14. Michael Dunning says

    I am trying to get the x-axis to do the same, not go to zero when not applicable. It seems it is random when it does. I.e. , if the X data begins at 1000, I would think the scale would automatically start around 900.

    Thanks

  17. Jon Peltier says

    Zeyad –

    In the paragraph above “Demonstration”, I point out that there is no information about how the major axis tick spacing is determined, but I do list some of the factors that affect it

  18. Zeyad says

    Hello,

    My question was on how the major unit can be determined, and not why that was not mentioned In the paragraph above “Demonstration”. Thanks, anyway, for your help.

  20. Kyle says

    If once upon a time you had, for example, 5000 rows of values entered in Column A, after you delete them you are screwed. Even if you delete the entire column and delete any charts the automatic range of any new charts is screwed. It will always remember the original number of elements in that column when it figures out the scale. Your only choice then is to use manual scaling. This can be very inconvenient, and like tons of other things in Microsoft Office, EXTREMELY ANNOYING!

  21. Jon Peltier says

    Kyle –

    You must have had the range hard-coded somewhere. Are you using VBA to get the max and min of the range? If the range is hard-coded in VBA, that’s the problem. You need to add some smarts to the code so it determines the extent of the data.

  22. MarkB says

    “The automatic maximum Y axis scale value is the first major unit above Ymax + (Ymax – Ymin)/20…”

    How is “major unit” defined? We are trying to make a homemade svg bar chart for a webpage.

    We have a bar chart with purchases $ figures for the last 5 months. These 5 different colored bars could each be anything between 0 and 1 billion.

    We also only ever want 4 intervals since the chart will always be 300px by 300px.

  24. Marc Kupper says

    Thank you – that article was informative as I was scratching my head wondering under what conditions the minimum axis value on a chart was dropping to zero as I displayed various ranges of data.

    In the past few months Microsoft seems to have gone on the warpath and is deleting many of the older knowledge base articles. Unfortunately, both KB101939 and KB214075 about “How Chart Axis Limits Are Determined” that you referred to in the first paragraph are now gone.

  25. Jon Peltier says

    Microsoft’s web site suffers from link rot worse than any other major site. Using my best Goggle-fu, I can’t find a link for this topic on their site. I also can’t find the article archived on my hard drive, which surprises me. I didn’t realize that Microsoft could delete content from private computers as well as from their web site.

  26. Jon Peltier says

    I have found the old knowledge base article, and I reproduce it below. It is Microsoft’s copyrighted content, and I only reproduce it here because Microsoft seems to have abandoned it. If that is incorrect, I will gladly take this down and replace it with a working link.

    Q101939 How Chart Axis Limits Are Determined 
----------------------------------------------------------------------- 
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 : 
Technology : 

                      Last Reviewed: April 6, 1999 
    (c) 2000 Microsoft Corporation. All rights reserved. Terms of Use.

  27. THOMAS RUDSKI says

    Jon, TIA for all your advice.. read thru a lot of your suggestions and tried them, don’t fix ‘my issue’. even tried the ‘Save as Template’, but same results.

    On a ‘new’ Chart #2, the ‘AXIS Option BOUNDs’ I have: MIN = 0 MAX = 1200, next to these boxes/elements is the word ‘AUTO’. I have another Chart #1 where the vert/hort doesn’t change/readjust the BOUNDS when data is changed, the ratio/apex is locked in. That Chart #1 has ‘RESET’ next to the the boundary limits box. Want to lock the axis values at x=0-1200 & y=0 to -2000 and not change when the data is changed. even tried a ‘new label’ with x=1200 y= -2000 but the axis changed, not lock in at 1200 -2000… Tom

  28. THOMAS RUDSKI says

    Jon, don’t waste your time. I was able to ‘Fix’ the x+y min/max and make the ‘Auto’ to ‘Reset’.

    Take the horz or vert MIN value (i.e. zero) make it (.1) or (-.1). then click option below to ‘set axis to zero’. you sill see the ‘Auto’ turn to ‘Reset’. Then MAX: make it (.1) less than current value i.e. 1200.0 = 1199.9, click option below to ‘set Max value’ the ‘Auto’ turns to ‘Reset’.

    changed both my x & y data values, the axis value did not change (1199.9 & -2000) and the ‘bullet’ answer ‘moved’ where I expected it be…. Thanks..

  29. Jon Peltier says

    Thomas –

    Want to know an easier way? if the current automatic maximum is 1200.0 and I want to lock it in, I add a zero to make it 1200.00. Likewise I change the automatic minimum from 0.0 to 0.00 to make it stick. Excel drops the zero I’ve added, but changes the Auto label to a Reset button.

    In 2007 & 2010, Excel actually had a box you could check to make the setting stick. This seemed straightforward to me, but people would change the number (which also checked the box), then they would uncheck the box.

  30. THOMAS RUDSKI says

    Thanks for the quick response. yes, a Option to ‘fix/lock it’ would be to easy, seeing that there’s 8M options with Charts. and both our ‘fixes’ are not documented, anywhere, except your Site. I also ran ‘record marco’ and when you ‘click’ the word ‘Auto’: it says the value. …. is ‘set =True’ meaning you can set to ‘False’. but you never see that option. or u have to run a macro to set it…

    Tom

Trackbacks

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Peltier Tech Charts for Excel