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.
'PURPOSE: Change Y-Axis Min/Max Bounds based on chart data (custom for every chart on sheet)
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
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)))
'Determine Max/Min of data points used in chart
chtMIN = Application.WorksheetFunction.Min(rng)
chtMAX = Application.WorksheetFunction.Max(rng)
'Adjust Y-Axis Max Value
If ScaleMax Then .MaximumScale = chtMAX + Padding
'Adjust Y-Axis Min Value
If ScaleMin Then .MinimumScale = chtMIN - Padding
Application.EnableEvents = True
Application.ScreenUpdating = True
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 :)
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!