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.
More Axis Scale Articles
- Calculate Nice Axis Scales with LET and LAMBDA
- Calculate Nice Axis Scales in Your Excel Worksheet
- Calculate Nice Axis Scales in Excel VBA
- Chart UDF to Control Axis Scale
- How Excel Calculates Automatic Chart Axis Limits
- Reciprocal Chart Axis Scale
- Custom Axis Labels and Gridlines in an Excel Chart
- Custom Axis, Y = 1, 2, 4, 8, 16
- Logarithmic Axis Scales
- Link Excel Chart Axis Scale to Values in Cells
- Consistent Axis Scales Across Multiple Charts
- Gantt Chart with Nice Date Axis
- Select Meaningful Axis Scales
- Bar Chart Value Axis Scale Must Include Zero
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.
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.
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.
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.
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
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..
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.
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
Frank Schnorbus says
Although this is an old conversation, it has helped me considerably. I am using Power Automate Desktop and am trying to set my Chart Vertical Axis values via PAD. The first obstacle was to “get” to that axis by sending keystrokes, which can be done using the Excel Keyboard Shortcuts. I did it in three “Alt” steps:
This selects the chart:
{Alt}PAP{Tab}{Tab}{Tab}{Tab}{Down}{Down}{Return}
This selects the Vertical Axis:
{Alt}JAEvert{Return}
And this selects the Major box for the lines between Min and Max:
{Alt}JAM{Tab}{Tab}{Tab}{Tab}{Down}{Down}{Down}{Down}
I found that starting at the top with Min and working my way down to Major caused the Reset to periodically auto-switch to Auto. It seemed to be random and sporadic, but now I see it’s set by Excel. When it did that it would jam PAD up by sending too many, or too few, keystrokes to get to the next box because it skips “Auto” but doesn’t skip “Reset”.
If, however, you start at the bottom with Major, then do Max, then do Min, the Auto/Reset boxes all stay at Reset.
I hope this helps the next person dealing with this seemingly erratic behavior.