Thanks:  0
Likes:  0

1. Dynamic Y-Axis On Charts

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!

2. Re: Dynamic Y-Axis On Charts

I have an opposite problem—bar charts do not start at zero. I wonder where I changed that setting?

You can accomplish the dynamic range using VBA or formulas. Jon Peltier offers a VBA solution, http://peltiertech.com/link-excel-ch...lues-in-cells/
I'm not well-practiced in VBA so here's a solution that uses an added second series.

Excel 2012
ABCDEFG
1Original data
2Data1Data2SelectionData1 <— Dropdown
3Alfa102.8111207.472
4Bravo102.2775209.575Charted data
5Charlie104.0091202.5094Data1
6Delta101.3995209.9221Alfa102.8111
7Echo103.585206.3468Bravo102.2775
8Foxtrot102.4297203.5256Charlie104.0091
9Delta101.3995
10Echo103.585
11Foxtrot102.4297
12
13For axis scale
14xy
151000
161100

Sheet1

Worksheet Formulas
CellFormula
E15=INT(MIN(ChartValues) / 10) * 10
E16=10 + INT(MAX(ChartValues) / 10) * 10

Workbook Defined Names
NameRefers To
ChartValues=Sheet1!\$F\$6:\$F\$11

I use a dropdown data validation cell to choose between the two sets of data.

Copy cells E14:F16.
Select the chart.
From the menu bar, select Home >> Paste >> Paste Special...
Then OK these settings:

Select one of the new series bars and from the right-click menu, choose Change Series Chart Type. You should see both an x series and a y series. Change both to X Y Scatter with Straight Line. The chart now looks odd.

From the right-click chart menu, choose Select Data.
In the right-hand lower pane, select the y series and click Remove.
Select the x series and click on the Edit button.
In the pop-up box, delete the series name. Change Series X values to =Sheet1!\$E\$15:\$E\$16. Change the Series X values to =Sheet1!\$F\$15:\$F\$16.
OK out of the dialog box.

Add the secondary horizontal axis and remove the primary horizontal and the secondary vertical axes. The chart should adjust to the size.

Format the dummy line series to No line. Add secondary grid lines if needed.

3. Re: Dynamic Y-Axis On Charts

First of all, bar charts should start at zero. They convey values through their lengths, and cutting off the lengths will lead to misrepresentation, even if it seems that the axes are clearly labeled. If you need to show just a narrow range, consider chart type carefully.

Second, I wrote a tutorial, Consistent Axis Scales Across Multiple Charts, which is similar to ThisOldMan's hidden series approach. In this example I use cells that contain the minimum and maximum values of all charts, then plot these cells as hidden series, so that the autoscaling of all charts are based on the same minimum and maximum values.

4. Re: Dynamic Y-Axis On Charts

@Jon Peltier, I once cringed at bar and column charts that don't use a zero base. I continue to show people alternatives; they still choose the bar chart over, say, a dot plot. I now merely shrug.

Mr. Peltier's tutorial: Dot Plots in Microsoft Excel - Peltier Tech Blog; http://peltiertech.com/dot-plots-microsoft-excel/

5. Re: Dynamic Y-Axis On Charts

Yeah, I know. I am very often asked to help people with really bad charts. When I suggest alternatives, they ignore them. When I decline to show certain techniques, they get mad.

So I keep trying, and I continue not to show techniques which are known to be wrong. And like you, shrugging my shoulders and shaking my head.

6. Re: Dynamic Y-Axis On Charts

Originally Posted by Jon Peltier
First of all, bar charts should start at zero. They convey values through their lengths, and cutting off the lengths will lead to misrepresentation, even if it seems that the axes are clearly labeled. If you need to show just a narrow range, consider chart type carefully.

Second, I wrote a tutorial, Consistent Axis Scales Across Multiple Charts, which is similar to ThisOldMan's hidden series approach. In this example I use cells that contain the minimum and maximum values of all charts, then plot these cells as hidden series, so that the autoscaling of all charts are based on the same minimum and maximum values.
It's actually a stacked bar chart - in the form of a waterfall chart - which is meant to emphasize the difference in each variable. If the differences are small, let's say +/- 2 to a number as big as 100+, thwe cahrt is pretty useless as you can't tell the relative difference between variables...

7. Re: Dynamic Y-Axis On Charts

As with most of these rules/guidelines, when appropriate discretion is used, there is some flexibility.

A waterfall is one chart for which I will grudgingly relax the start-at-zero rule. This helps to compare the magnitudes of the small variations, but it hides the ability to scale the variations with the starting value. Maybe two charts would be better: a simple bar chart with the variations, both positive and negative, or perhaps all positive for easier comparisons with gains and losses in different colors, alongside a waterfall including zero that compares initial and final values with small variations.

It's a balancing act to show the data without obscuring or misrepresenting the data.

8. Re: Dynamic Y-Axis On Charts

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.
When it's down to produce a (complex) Excel reprort to be given ON A DAILY BASIS to customers, of course VBA cannot be the answer, as most companies cannot even open an Excel containing VBA code.
This is a mistery to me.
Ciao from Italy!

9. Re: Dynamic Y-Axis On Charts

Try this.

Code:
ActiveChart.Axes(xlValue) _
.MinimumScale = _
ActiveChart.Axes(xlCategory) _
.MinimumScale = _

10. Re: Dynamic Y-Axis On Charts

Originally Posted by ThomasOES
Try this.

Code:
ActiveChart.Axes(xlValue) _
.MinimumScale = _
ActiveChart.Axes(xlCategory) _
.MinimumScale = _
The OP was using a (stacked) column chart. Your code trying to rescale the xlCategory axis of a column chart will raise an error.

User Tag List

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•