VBA To Re-Scale Every Excel Graph's Y-Axis Bounds Based On Chart Data Points

VBA Macro Change Y-Axis Minimum Bound Chart Automatically

What This VBA Code Does

This VBA macro code will cycle through every graph on the ActiveWorksheet and adjust the Y-axis bounds based on the Minimum and Maximum values found within the chart's data set. The VBA code also allows you to add some padding to the bounds, so the upper bounds of the chart doesn't equal the highest data point.

By default, Excel automatically chooses the bounds of the y-axis. Manually, you would have to right-click on each chart's Y-axis and format the axis. If you implement the below VBA macro code, you can bypass this manual work and save yourself a lot of clicks (especially if you have a ton of charts to modify)!

In the Inputs Section of the VBA code shown below, you can choose to leave the Max or the Min bounds set to Auto. I recommend leaving the Max = False, as Excel does a pretty good job of adjusting the upper bounds on its own.

Sub ReScaleChartAxis()
'PURPOSE: Change Y-Axis Min/Max Bounds based on chart data (custom for every chart on sheet)
'SOURCE: www.TheSpreadsheetGuru.com/the-code-vault

Dim cht As ChartObject
Dim srs As Series
Dim rng As Range
Dim ScaleMax As Boolean
Dim ScaleMin As Boolean
Dim chtMIN As Long
Dim chtMAX As Long
Dim Padding As Long

'Inputs (True = On / False = Off)
  ScaleMax = False
  ScaleMin = True
  Padding = 500

'Optimize Code
  Application.ScreenUpdating = False
  Application.EnableEvents = False

'Loop through each chart in activesheet
  For Each cht In ActiveSheet.ChartObjects
    'Loop through each series in chart
      For Each srs In cht.Chart.SeriesCollection
        'Gather all data points used in chart
          If rng Is Nothing Then
            Set rng = Range(Split(srs.Formula, ",")(2))
            Set rng = Union(rng, Range(Split(srs.Formula, ",")(2)))
          End If
      Next srs
      'Determine Max/Min of data points used in chart
        chtMIN = Application.WorksheetFunction.Min(rng)
        chtMAX = Application.WorksheetFunction.Max(rng)
    With cht.Chart.Axes(xlValue)
      'Adjust Y-Axis Max Value
        If ScaleMax Then .MaximumScale = chtMAX + Padding
      'Adjust Y-Axis Min Value
        If ScaleMin Then .MinimumScale = chtMIN - Padding
    End With

  Next cht

'Reset Optimization
  Application.EnableEvents = True
  Application.ScreenUpdating = True

End Sub

The Results Can Be A Game Changer!

By re-scaling your Y-axis, you can completely change the impact of your chart. What might seem like small increases when the Y-axis starts at 0, can really be large variance that really shine through when you start the Y-axis at 9,500.

If I'm giving you a chart of how much your expenses are increasing year over year, which chart below scares you more?

FYI Your answer should be the one on the right :)

Click to enlarge

How Do I Modify This To Fit My Specific Needs?

Chances are this post did not give you the exact answer you were looking for. We all have different situations and it's impossible to account for every particular need one might have. That's why I want to share with you: My Guide to Getting the Solution to your Problems FAST! In this article, I explain the best strategies I have come up with over the years to getting quick answers to complex problems in Excel, PowerPoint, VBA, you name it

I highly recommend that you check this guide out before asking me or anyone else in the comments section to solve your specific problem. I can guarantee 9 times out of 10, one of my strategies will get you the answer(s) you are needing faster than it will take me to get back to you with a possible solution. I try my best to help everyone out, but sometimes I don't have time to fit everyone's questions in (there never seem to be quite enough hours in the day!).

I wish you the best of luck and I hope this tutorial gets you heading in the right direction!

~ Chris