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.



Want to Learn VBA & Macros?

If you want to automate Excel and save time, my recommended resouces for learning VBA Macros are: