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.

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.

Eric says

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

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.

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.

Hannah Hinkson says

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.

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

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.

Anonymous says

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

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.

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.

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.

Jon Peltier says

Mark:

Link Chart Axis Scale Parameters to Values in Cells (VBA).

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.

Jon Peltier says

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.

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

Jon Peltier says

Michael –

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

Zeyad says

Hello,

how the major unit is determined automatically?

thanks!

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

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.

Jon Peltier says

What was mentioned is that there is no documentation anywhere for the major unit.

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!

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.

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.

Jon Peltier says

Mark –

“How is “major unit” defined?”

That’s a very good question. Nobody knows. It seems related to numerous factors, including the size of the chart, and the font size of the axis tick labels.

You could use a VBA solution that always makes sure the axis has four intervals. Use Calculate Nice Axis Scales in Excel VBA for reference.