**Determine Maximum and Minimum Y Axis Value of an Excel Chart**

**Determine Chart Axis Interval (or Major Unit)**

It’s interesting to determine how Excel automatically sets the maximum Y Axis value of a chart. In *default* mode, Excel auto scales the Y axis – i.e. the maximum and minimum Y axis values are automatically scaled up or down as the chart’s data range get modified. While this works wonders under most scenarios, if one needs to synchronize another chart or a variable to the chart’s y-axis or gridlines, it can be a bit of an issue. The article below describes a ‘generic’ approach to replicating Excel’s auto scale algorithm. Note that the article below describes only a generic approach, one that works for the most part but not for all cases.

For basic charts (column, bar, line, x-y scatter, area), Excel’s auto scaling algorithm is fairly simple:

## Maximum and Minimum Chart Y Axis Value

### Maximum

**Maximum Chart Y Axis Value** = First Major Unit __above X__

where **X** = 1.05 * Highest Value of Input Data – 0.05 * Lowest Value of Input Data

### Minimum

If (Highest Value of Input Data – Lowest Value of Input Data)/Highest Value of Input Data > 16.6667%

**Minimum Chart Y Axis Value** = 0

If (Highest Value of Input Data – Lowest Value of Input Data)/Highest Value of Input Data < 16.6667%

**Minimum Chart Y Axis Value** = First Major Unit __below X__

where **X** = 1.5 * Lowest Value of Input Data – 0.5 * Highest Value of Input Data

** 16.6667% = 100/6*

Bear in mind that this approach works when the highest and lowest values in the data range are > = 0. For other combinations, refer support.microsoft.com/kb/214075

## Y Axis Interval or Major Unit

In the above section, we were able to determine the maximum Y-axis value of a chart. We also saw that it is dependent on the Major Unit, which incidentally, also auto scales. Microsoft does not provide a direct answer. What is however *most commonly observed* is that all major units start with the numbers 1, 2 or 5. We’ll refer to this as the “**1, 2, 5 rule**“. This rules extends on both side of the decimal place – the first non zero number has to be 1, 2 or 5. This means, for example, that gridlines will typically be spaced at a distance of 0.0001, 0.001, 0.01, 0.1, 1, 10, 100 , 1000 units (you can replace 1 with 2 or 5 and the same rule will still apply).

But…this leads to another interesting problem. We know that the number of gridlines are not constant. For given max-min Y axis value range, how do we know whether the chart with use a 1.X, 2.X or 5.X as the major unit?

Another observation comes in handy, the number of gridlines vary but within a permissible limit. The number of gridlines is typically between 5 and 10.

Given both these assumptions, we can construct a grid which can throw up the right value for the y axis major unit (interval). The trick is to shortlist all the permissible 1.X, 2.X or 5.X intervals and then within that set, pick up the exact interval that will limit the number of gridlines between 5 and 10.

**Example** : Let’s create a bar chart with the following data points {1, 2, 5, 3, 4}. The maximum data range value is 5 and minimum is 1. By applying our existing logic, the difference between max and min is > 16.666% and therefore:

**Maximum value** : First Major Unit above 5.2 (5.2 = 5 * 1.05 – 0.05 * 1)

**Minimum value** : 0

Once we shortlist permissible intervals, we can then determine that a major unit value of 1 will allow us to neatly fit 6 gridlines. The closest alternatives would have been 12 or 3 gridlines, both of which are impermissible. Having identified the major unit as 1, the next gridline above 5.2 is 6. That’s the maximum Y axis value.

**Note**: Bear in mind that we are still talking about default rendering options – as we resize the chart, the maximum Y axis value and the number of gridlines will change.

You can download an experimental worksheet containing **a grid for determining Y axis maximum value and major unit** here or click on the button below:

Wow – SO nice to see you back posting! Hope that you’ve been OK in the interim. Have missed your always helpful posts. Take care!

May 25th, 2014 at 3:05 pmSuch a pleasant surprice. Glad you are back.

May 25th, 2014 at 7:18 pmAlways looking forward to your new posts.

This is very informative and helpful! After reading this post, I was able to determine a simpler way of figuring out the major unit:

First, take your Data Range Max Value and normalize it to between 1 and 10. In other words, strip away the exponent (329 becomes 3.29) , but remember what it was for later!

If this number is between 1 and 2/1.05 (roughly 1.905), then the major unit is 0.2 * X, where X is the power of 10 in the original data range max value.

If it is between 2/1.05 and 5/1.05, then the major unit is 0.5 * X.

If the value is between 5/1.05 and 10/1.05, then the major unit is 1 * X.

Lastly, if the value is greater than 10/1.05, the major unit is 2 * X.

Now we don’t have to use complicated Excel lookup formulas!

June 27th, 2016 at 6:10 pmVery useful knowledge you share to us

April 7th, 2020 at 8:46 pm