This post may contain affiliate links. Please read my disclosure for more info.
Advertisement:

Set chart axis min and max based on a cell value

Chart Axis Mix & Max

Chart Axis Mix & Max

Not again…” I think to myself every time I change the minimum and maximum values of a chart axis.  It’s such a tedious task, and I know I’ll be doing it again at a future point.  It only takes a few seconds, but all that time starts to add up.  There are various chart objects we can link to worksheet cells; source data, chart titles and data labels can all be linked to cells, but the chart axis is set by hardcoding a number into the Format Axis options window.

Well… I’m not so easily defeated.  I decided to build a more dynamic solution.  I turned to my old friend VBA, and started to tinker.  Below you’ll find the result of that tinkering; a formula which exists on the worksheet to control the min and max values of a chart axis.  Link that formula to a cell and suddenly it is possible to set the chart axis based on a cell value.

Hopefully, by the end of this post, you too can share in the automatic cell linked bliss I now experience.

If you’re not familiar with VBA, don’t worry, I’ll talk you through it step-by-step..

 

The solution

I wanted a solution which:

  • Updates automatically whenever data changes
  • Does not require user interaction – i.e. no button clicking, but updates automatically when the worksheet recalculates
  • Easily portable between different worksheets

I think I managed to achieve this.

User Defined Functions (UDFs for short) are just like normal Excel functions, such as VLOOKUP or SUM, but they have been created using VBA.  UDF’s are intended to be custom worksheet functions to calculate a cell value.  Whether on purpose or by accident, Microsoft have made it possible to control various objects with UDFs.  For example, it is possible to change a worksheet’s tab color, or to change a chart title (next week’s post).  Thankfully, the minimum and maximum values of the chart axis are controllable using a UDF. Horray!

 

The solution in action

The animated gif below shows the solution in action.  The values in the cell are automatically applied to the chart.  Whether these values are typed in the cell or created using formulas, they will update the chart.

Chart Min Max In Action

 

Creating the User Defined Function

To create the UDF click Developer -> Visual Basic (or shortcut ALT + F11).  Follow these instructions if the Developer Tab is not visible.


Advertisement:

Developer Visual Basic

The Visual Basic Editor window will open, click Insert -> Module

Chart Min Max Insert Module

Add the code below to the Module as shown.  The code for UDFs must be within a standard module to work.

Chart Min Max UDF Code

 

The VBA Code

Here is the VBA code to copy into the Module.

Function setChartAxis(sheetName As String, chartName As String, MinOrMax As String, _
    ValueOrCategory As String, PrimaryOrSecondary As String, Value As Variant)

'Recalculate the formula every time
Application.Volatile

'create variables
Dim cht As Chart
Dim valueAsText As String

'Set the chart to be controlled by the function
Set cht = Application.Caller.Parent.Parent.Sheets(sheetName) _
    .ChartObjects(chartName).Chart

'Set Value of Primary axis
If (ValueOrCategory = "Value" Or ValueOrCategory = "Y") _
    And PrimaryOrSecondary = "Primary" Then

    With cht.Axes(xlValue, xlPrimary)
        If IsNumeric(Value) = True Then
            If MinOrMax = "Max" Then .MaximumScale = Value
            If MinOrMax = "Min" Then .MinimumScale = Value
        Else
            If MinOrMax = "Max" Then .MaximumScaleIsAuto = True
            If MinOrMax = "Min" Then .MinimumScaleIsAuto = True
        End If
    End With
End If

'Set Category of Primary axis
If (ValueOrCategory = "Category" Or ValueOrCategory = "X") _
    And PrimaryOrSecondary = "Primary" Then

    With cht.Axes(xlCategory, xlPrimary)
        If IsNumeric(Value) = True Then
            If MinOrMax = "Max" Then .MaximumScale = Value
            If MinOrMax = "Min" Then .MinimumScale = Value
        Else
            If MinOrMax = "Max" Then .MaximumScaleIsAuto = True
            If MinOrMax = "Min" Then .MinimumScaleIsAuto = True
        End If
    End With
End If

'Set value of secondary axis
If (ValueOrCategory = "Value" Or ValueOrCategory = "Y") _
    And PrimaryOrSecondary = "Secondary" Then

    With cht.Axes(xlValue, xlSecondary)
        If IsNumeric(Value) = True Then
            If MinOrMax = "Max" Then .MaximumScale = Value
            If MinOrMax = "Min" Then .MinimumScale = Value
        Else
            If MinOrMax = "Max" Then .MaximumScaleIsAuto = True
            If MinOrMax = "Min" Then .MinimumScaleIsAuto = True
        End If
    End With
End If

'Set category of secondary axis
If (ValueOrCategory = "Category" Or ValueOrCategory = "X") _
    And PrimaryOrSecondary = "Secondary" Then
    With cht.Axes(xlCategory, xlSecondary)
        If IsNumeric(Value) = True Then
            If MinOrMax = "Max" Then .MaximumScale = Value
            If MinOrMax = "Min" Then .MinimumScale = Value
        Else
            If MinOrMax = "Max" Then .MaximumScaleIsAuto = True
            If MinOrMax = "Min" Then .MinimumScaleIsAuto = True
        End If
    End With
End If

'If is text always display "Auto"
If IsNumeric(Value) Then valueAsText = Value Else valueAsText = "Auto"

'Output a text string to indicate the value
setChartAxis = ValueOrCategory & " " & PrimaryOrSecondary & " " _
    & MinOrMax & ": " & valueAsText

End Function

That’s all we need to do to create the UDF, now it’s ready to use.

 

Using the function

The function we have created uses the following syntax:

=setChartAxis(chartName, MinOrMax, ValueOrCategory, PrimaryOrSecondary, Value)
  • chartName = the name of the chart as a text string, or a cell reference to a text string.  If you’re not sure of a chart’s name, click on the chart and look at the NameBox (the box to the left of the formula bar).
  • MinOrMax = a text string of “Min” or “Max” to select whether the function will control the Minimum or Maximum axis value.
  • ValueOrCategory = a text string of “Value” or “Category” to indicate which axis to adjust (will also accept “X” or “Y”)
  • PrimaryOrSecondary = a text string of “Primary” or “Secondary” to indicate which axis to adjust.
  • Value = a number, or a text string.  A number will adjust the axis to that value, “Auto” will reset the axis to Automatic.

For the function the work, the chart must be in the same workbook as the function.

Unlike normal functions, UDF’s do not display the order of the arguments when typed into the formula bar.  Therefore, the best options are:

  • Start typing the UDF then press Ctrl + Shift + A after typing the first bracket.
    Ctrl Shift A to show formula
  • Click on the fx button in the formula bar.  In the Insert Function window select the function under the User Defined list, then click OK.
    select User Defined Function
    The Function Arguments window will show the order and names of the arguments.
    Function Arguments Window v2

The screenshot below shows how the UDF is used:

Display setChartAxis formula in formulabar v2

The formula in Cell G12 is:

=setChartAxis("Sheet1","Chart 2","Min","Value","Primary",E12)

Resetting back to default axis


Advertisement:

If you decide to revert back to Excel’s automatic axis calculation, that is no problem.  Any non-numeric value entered as the Value (or in the cell reference linked to the value) will cause the chart to be reset to automatic.

=setChartAxis("Sheet1","Chart 2","Min","Value","Primary","Auto")

 

Download the example file

Sometimes it is easier to understand by seeing the UDF in action.

Download the file here.

 

Making the function available in all workbooks

If you wish to make the UDF available in all your workbooks:

  • Include the code above within a new workbook
  • Save the workbook as an Excel Add-in (.xlam file type).
  • Install the Add-in you have created by following these instructions.

The function is now available in all your workbooks.  If others open the workbook they will be able to see the function, but it will not work.  If you want other users to use the function save it in each individual file.

15 thoughts on “Set chart axis min and max based on a cell value

    • Excel Off The Grid says:

      Hi Divya,

      The #VALUE! error occurs if there is any error at all, so it could be any number of things.

      The most likely reason is that the chart doesn’t have series with a secondary axis.

  1. Divya says:

    Hi Mark,

    Thanks for your reply.

    It didn’t work when I tried it in the Excel spreadsheet that you have attached as well.

    I may be missing something, I’m not sure what.

      • Mitra says:

        Hi Mark,

        Thank you for this!

        I am experiencing the same issues as Divya. Could you possibly forward the file mentioned above to me as well?

        Thanks.

        • Excel Off The Grid says:

          Hi Mitra,

          I have updated the file in the post to show how to use all axis. So if you download the file (see the link in the post) you should be able to see how it works.

  2. Benno says:

    Hi there,

    this is a super cool function! Many thanks for setting it up and sharing it… Once you see the code it does not even look that complicated (when it is already there 😉 ). I was wondering if the script could be adapted to also change the units of the graph (stepsize). Any idea what name should be used to refer to the attribute of the major units ?

    Many thanks anyhow, this really helps me.

    Kind regards,

    Benno

    • Excel Off The Grid says:

      Hi Benno,

      Yes, easy enough to add the units. Add the following code above the line of code which says: ‘If is text always display “Auto”

      'Set Value of Unit Primary Axis
      If ValueOrCategory = "Unit" And PrimaryOrSecondary = "Primary" Then
      
          With cht.Axes(xlValue, xlPrimary)
              If IsNumeric(Value) = True Then
                  If MinOrMax = "Major" Then .MajorUnit = Value
                  If MinOrMax = "Minor" Then .MinorUnit = Value
              Else
                  If MinOrMax = "Major" Then .MajorUnitIsAuto = True
                  If MinOrMax = "Minor" Then .MinorUnitIsAuto = True
              End If
          End With
      End If
      
      
      'Set Value of Unit Secondary Axis
      If ValueOrCategory = "Unit" And PrimaryOrSecondary = "Secondary" Then
      
          With cht.Axes(xlValue, xlSecondary)
              If IsNumeric(Value) = True Then
                  If MinOrMax = "Major" Then .MajorUnit = Value
                  If MinOrMax = "Minor" Then .MinorUnit = Value
              Else
                  If MinOrMax = "Major" Then .MajorUnitIsAuto = True
                  If MinOrMax = "Minor" Then .MinorUnitIsAuto = True
              End If
          End With
      End If
      

      Then to refer to it in the formula, use “Unit” instead of “Value” and use either “Major” or “Minor” instead of “Min” or “Max”.

      Hopefully that’s enough for you to get it working.

  3. Diego says:

    Hello!

    This is amazing stuff! thank you so much.

    I was wondering if you could also make the X axis based on min and max. In your example you have Jan, Feb etc but what if the months were numbers 1 for Jan, 2 for Feb etc (this is my case) and then you want to plot only from Feb to June.

    Let me know if that is possible. Thanks again!

    • Excel Off The Grid says:

      Hi Diego,

      Yes this is possible.

      (1) Right click on the X Axis and click Format Axis… from the menu.
      (2) From the Axis Options select the Data axis option box
      (3) In the formula set the ValueOrCategory argument to be “Category”.

      If the axis labels are text, Excel will assume the first data point will be 1, the second data point will be 2 and so on. So you can still use month names, rather than month numbers.

      • Diego Canal Saez says:

        Hello. Thank you so much for your quick response. The Y axis works perfectly but it seems like the function does not work on the X axis, I keep getting a #VALUE error.

        My graph has 3 sets of data represented with a line and stacked bars. Could that be the problem?

        • Excel Off The Grid says:

          It definiely works. I’ll send you a file with it working.

          You might have been confused by the typo in my last comment. It needs to be a “Date axis”.

Leave a Reply

Your email address will not be published. Required fields are marked *