Dynamic Y-Axis On Charts

[joek128]

I have number of charts on Sheet1 of a workbook that are dynamic. However, the min and max y-values tend to fluctuate greatly when I adjust the parameters. The "Auto" option on the axis options page doesn't work very well, as it always sets the minimum of my bar charts to "0". This renders the charts useless if, for example, the data is between 100-105.

I was wondering if there was a way to make each chart automatically adjust the y-axis range to [(min - 10), (max + 10)]? In the example above, the min/max bounds of the y-axis would auto adjust to (90, 115).


Thanks!

 

[Jon Peltier]

In Set chart axis min and max based on a cell value, Mark of Excel Off The Grid revisited a technique first written about by John Walkenback in Modifying Shapes (And Charts) With UDFs in the Daily Dose of Excel blog. This technique uses a UDF (a VBA User Defined Function) to modify a chart's axis scale.

I've been working on enhanced versions of this UDF technique, including variations that (a) the user selects cells that contain axis scale parameters, perhaps pointing to cells containing formulas, and (b) which compute "nice" axis scales based on data in the chart, using algorithms like those I've written about in Calculate Nice Axis Scales in Your Excel Worksheet and Calculate Nice Axis Scales in Excel VBA. Perhaps I'll have a chance to blog about them soon.

 

[Jon Peltier]

Hi All,
it does seem, however, that nobody answered the REAL question: why does Excel (still at its version 2016!!) not have this magical feature of adusting the Y axis minimum to a non-zero value AUTOMATICALLY? Talking about line charts, for instance, this may result in a practical unreadability of the chart. In other words, the chart is useless.

Excel actually will under some conditions choose a non-zero axis minimum, as I describe in How Excel Calculates Automatic Chart Axis Limits. If the minimum value is greater than 5/6 of the maximum, Excel will select a non-zero minimum based on the values and the major unit on the axis. Microsoft describes this algorithm, but does not provide any insight into how Excel selects an automatic major unit, though it seems to be related in a complicated way to the size of the chart and of the plot area, the font size of labels, etc.