Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: Dynamic Y-Axis On Charts

  1. #1
    New Member
    Join Date
    Jul 2016
    Posts
    23
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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!
    Last edited by joek128; Oct 1st, 2016 at 03:59 PM.

  2. #2
    Board Regular
    Join Date
    Jan 2014
    Location
    Pittsburgh
    Posts
    1,072
    Post Thanks / Like
    Mentioned
    10 Post(s)
    Tagged
    1 Thread(s)

    Default 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.

    Create your dynamic chart.
    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.



    Link: https://www.dropbox.com/s/fzt2c7525o...hart.xlsx?dl=0

  3. #3
    MrExcel MVP
    Join Date
    May 2003
    Location
    USA
    Posts
    4,553
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    1 Thread(s)

    Default 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.
    Jon Peltier
    Peltier Technical Services, Inc.
    Try Peltier Tech Charts for Excel

  4. #4
    Board Regular
    Join Date
    Jan 2014
    Location
    Pittsburgh
    Posts
    1,072
    Post Thanks / Like
    Mentioned
    10 Post(s)
    Tagged
    1 Thread(s)

    Default 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. #5
    MrExcel MVP
    Join Date
    May 2003
    Location
    USA
    Posts
    4,553
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    1 Thread(s)

    Default 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. #6
    New Member
    Join Date
    Jul 2016
    Posts
    23
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Dynamic Y-Axis On Charts

    Quote Originally Posted by Jon Peltier View Post
    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. #7
    MrExcel MVP
    Join Date
    May 2003
    Location
    USA
    Posts
    4,553
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    1 Thread(s)

    Default 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.
    Jon Peltier
    Peltier Technical Services, Inc.
    Try Peltier Tech Charts for Excel

  8. #8
    New Member
    Join Date
    Feb 2017
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #9
    Board Regular
    Join Date
    Aug 2017
    Posts
    84
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Dynamic Y-Axis On Charts

    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

  10. #10
    MrExcel MVP
    Join Date
    May 2003
    Location
    USA
    Posts
    4,553
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Dynamic Y-Axis On Charts

    Quote Originally Posted by ThomasOES View Post
    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.
    Jon Peltier
    Peltier Technical Services, Inc.
    Try Peltier Tech Charts for Excel

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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