Use VBA To Automatically Adjust Your Charts Y-Axis Min and Max Values

Automatically Adjust Chart Y-Axis Limits Excel

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.

Auto Adjust Y Axis Vertical Bounds

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:

  1. Select the Y-Axis
  2. Right-Click the highlighted Y-Axis and select Format Axis... from the menu
  3. Make sure you are within the Axis Options section of the Format Axis Pane
  4. 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 Sub

I 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 Sub

Get 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 a Event Triggered macro.

As always, in order to download this or any example file from this website, you will need to be a subscriber of my free tips newsletter.  If you click the green button below you can easily sign up and you will be emailed the password to get into the subscribers-only area of this website.

 
 Already Subscribed? Click HERE to log-in to the "Example Files" section

 Already Subscribed? Click HERE to log-in to the "Example Files" section

 

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 :)