Use VBA To Automatically Adjust Your Charts Y-Axis Min and Max Values
Why Manually Adjust Your Y-Axis?
By default, Excel will automatically decide the minimum and maximum values of your vertical (Y) axis. For the most part this will visually work for graphing your data, but there are certain instances where Excel’s guessing may make your chart look weird or prevent it from telling the story you are trying to portray. Let’s look at a couple of examples to show you what I mean.
The Waterfall Chart Example
Waterfall charts are used to show causes for the ups and downs (variances) between two data points. They are very good ways to quickly display root causes for changes in metrics and are used quite frequently in the corporate world.
The two charts below are the same exact charts except the one on the left has the minimum value for the Y-Axis starting at $0 while the one on the right has the minimum value starting at $450. It should be pretty apparent that the chart on the right with the $450 Y-axis starting point is much more presentable than the one starting at $0.
Now most chart experts would argue that even though the chart on the right looks more appealing, it skews the perception on how big of an impact the $10, ($5), and $20 changes are in relation to the $500 starting point. And this brings us to a fork in the road where you will need to make a decision based on your audience. If you are going to be presenting a chart to an audience of finance-minded executives or managers, chances are they are going to know how big of a deal a $10 change in your sales is. However, if you were a journalist writing a piece on the economy for the general public you might not want to risk this distorted view as some in your target audience may not be able to process both the visual and the data to come to a conclusion.
In case you were wondering, these charts were created with my Waterfall Chart Creator add-in for Excel. If you like Waterfalls you’ll love this add-in (there’s even a free trial!)
If you wanted to play it safe, you might display your chart with an added metric to right size the data. I would add percent change to the graphic as shown below.
The Year-Over-Year Trends Example
There will be times as an analyst where you are not trying to create a pretty chart, you are just trying to simply “analyze” data! This can often times take the form of looking a seasonal trends over multiple years to ensure a forecast is directionally correct.
Let’s take a look at the below two line charts that are analyzing crop yields (ie what percentage of your land created food you could sell). This might be useful if you are trying to determine if a 2016 forecast looks in line with historical trends.
The problem that you might pick up on while looking at the first chart is depending on your data, it may be very difficult to look at the changes for the trended years. By changing your Y-Axis Min/Max (effectively zooming in), you can clearly see in October your 2016 forecast might need a little adjusting.
Again, if you were to present this chart you would need to have confidence that your audience is savvy enough to read the scale and realize the axis starts at 70% instead of 0%.
To bring attention, you could try highlighting the 70% with a box.
How Do I Manually Adjust The Y-Axis Min/Max?
Here are the steps:
- Select the Y-Axis
- Right-Click the highlighted Y-Axis and select Format Axis… from the menu
- Make sure you are within the Axis Options section of the Format Axis Pane
- Within the Bounds section, manually input your Minimum and Maximum axis limits
Getting VBA To Do The Work For You
Adjusting your minimum and maximum bounds to your chart axis is pretty straight-forward, however it can get rather cumbersome if your data is frequently changing (think dashboard) and you are required to keep adjust your axis bounds to make the chart useful.
This is where VBA can be extremely useful! Below is a VBA macro I created that will go throw all the charts on your worksheet and adjust the y-axis according to the minimum and maximum values within the charts specific data. There is even an input to add a little extra padding to your axis so the axis’ bounds is a bit under or over your min/max data points.
Sub AdjustVerticalAxis()
'PURPOSE: Adjust Y-Axis according to Min/Max of Chart Data
'SOURCE: www.TheSpreadsheetGuru.com
Dim cht As ChartObject
Dim srs As Series
Dim FirstTime As Boolean
Dim MaxNumber As Double
Dim MinNumber As Double
Dim MaxChartNumber As Double
Dim MinChartNumber As Double
Dim Padding As Double
'Input Padding on Top of Min/Max Numbers (Percentage)
Padding = 0.1 'Number between 0-1
'Optimize Code
Application.ScreenUpdating = False
'Loop Through Each Chart On ActiveSheet
For Each cht In ActiveSheet.ChartObjects
'First Time Looking at This Chart?
FirstTime = True
'Determine Chart's Overall Max/Min From Connected Data Source
For Each srs In cht.Chart.SeriesCollection
'Determine Maximum value in Series
MaxNumber = Application.WorksheetFunction.Max(srs.Values)
'Store value if currently the overall Maximum Value
If FirstTime = True Then
MaxChartNumber = MaxNumber
ElseIf MaxNumber > MaxChartNumber Then
MaxChartNumber = MaxNumber
End If
'Determine Minimum value in Series (exclude zeroes)
MinNumber = Application.WorksheetFunction.Min(srs.Values)
'Store value if currently the overall Minimum Value
If FirstTime = True Then
MinChartNumber = MinNumber
ElseIf MinNumber < MinChartNumber Or MinChartNumber = 0 Then
MinChartNumber = MinNumber
End If
'First Time Looking at This Chart?
FirstTime = False
Next srs
'Rescale Y-Axis
cht.Chart.Axes(xlValue).MinimumScale = MinChartNumber * (1 - Padding)
cht.Chart.Axes(xlValue).MaximumScale = MaxChartNumber * (1 + Padding)
Next cht
'Optimize Code
Application.ScreenUpdating = True
End SubI will typically link this macro up to a button on the spreadsheet or assign it to a spreadsheet event (such as “Before Double-Click” or “Change”). That way you can get all your charts adjusted quickly without having to manually go in and execute the macro every time.
Here is a simple example of how you might trigger the AdjustVerticalAxis macro above through the Worksheet_Change event.
Private Sub Worksheet_Change(ByVal Target As Range)
'PURPOSE: Run AdjustVerticalAxis macro when Cell C2 is changed
If Target = Range("C2") Then Call AdjustVerticalAxis
End SubGet The VBA Example File
If you need a little head start or are not comfortable with VBA quite yet, I have put together a great little example Excel file with the VBA code covered in this article. The example workbook covers both a button-triggered macro and an event-triggered macro.
If you would like to get a copy of the Excel file I used throughout this article, feel free to directly download the spreadsheet by clicking the download button below.
After 10+ years of creating macros and developing add-ins, I've compiled all the hacks I wish I had known years ago!
Keep Learning
Chris Newman
Chris is a finance professional and Excel MVP recognized by Microsoft since 2016. With his expertise, he founded TheSpreadsheetGuru blog to help fellow Excel users, where he shares his vast creative solutions & expertise. In addition, he has developed over 7 widely-used Excel Add-ins that have been embraced by individuals and companies worldwide.