Dynamic Y-Axis On Charts

joek128

New Member
Joined
Jul 22, 2016
Messages
23
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!
 
Last edited:

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
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-chart-axis-scale-to-values-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
Cell Formulas
RangeFormula
E15=INT(MIN(ChartValues) / 10) * 10
E16=10 + INT(MAX(ChartValues) / 10) * 10
Named Ranges
NameRefers ToCells
ChartValues=Sheet1!$F$6:$F$11

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

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


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.

lDyNE4q.jpg


Link: https://www.dropbox.com/s/fzt2c7525oqf9vg/dynamic_axis_bar_chart.xlsx?dl=0
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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...
 
Upvote 0
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.
 
Upvote 0
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!
 
Upvote 0
Try this.

Code:
ActiveChart.Axes(xlValue) _
    .MinimumScale = _
    WorksheetFunction.Min(Range("Your range here")) * 0.95
ActiveChart.Axes(xlCategory) _
    .MinimumScale = _
    WorksheetFunction.Min(Range("Your range here")) * 0.95
 
Upvote 0
Try this.

Code:
ActiveChart.Axes(xlValue) _
    .MinimumScale = _
    WorksheetFunction.Min(Range("Your range here")) * 0.95
ActiveChart.Axes(xlCategory) _
    .MinimumScale = _
    WorksheetFunction.Min(Range("Your range here")) * 0.95

The OP was using a (stacked) column chart. Your code trying to rescale the xlCategory axis of a column chart will raise an error.
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,236
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top