How Excel Calculates Automatic Chart Axis Limits

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.

Peltier Tech Chart Utility

Comments

  1. Thanks, Jon! Looking forward to the super secret algorithms… :)

  2. 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. Also looking forward to your insight on axis scaling algorithms. I’ve made my own clumsy attempts that are in need of refinement.

  4. The min and max formulars for excel can be tricky, Thats why I wish someone would just write a book breaking down how to use each and every excel formula.

  5. 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. 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.

  7. I was struggling for hours trying to figure out the pattern here. Thanks.

  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. 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. 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.

  11. 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.

  12. Ravi -
    I’m working on an updated tutorial, but the technique in the following archived article may help:
    Link Chart Axis Scale Parameters to Values in Cells.

  13. 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

  14. Michael -
    It’s not just the min, it’s also the max, that influences Excel’s autoscaled axes.

Subscribe without commenting

Write a Comment

I welcome comments from my readers. If you have an opinion on this post, if you have a question, or if there is anything to add, I want to hear from you. Whether you agree or disagree, please join the discussion. If you want to include an image in your comment, post it on your own site or on one of the many free image sharing sites, and include a link in your comment. I'll download your image and insert the necessary html to display the image inline.

Subscribe without commenting

Peltier Tech Chart Utility

 

Create Excel dashboards quickly with Plug-N-Play reports.

 

Site by: Dawud Miracle, Business Coach & WordPress Websites