Automatic Ways to Scale an Excel Chart Axis

This is an overview.

Method 1 – Using the Format Axis Feature to Change the Chart Axis Scale in Excel

Step 1: Creating a Dataset for X and Y axis

Enter Data in B4 and C4.

Step 2: Insert a 2-D Column Chart

Click B4.
Go to the Insert tab >> Click scatter and insert the Bar Chart.

The Bar Chart will be displayed.

Step 3: Changing the Chart Axis Scale Automatically

Select the vertical values of the scatter chart and right click.
Select Format Axis.

In Format Axis, select Axis options.
In Units >> Enter 3000.

The Maximum Bounds will change to 21000 automatically, and the scale of the y-axis changes will change to 3000.

Observe the GIF below.
You can’t change the x-axis scale, because text was used instead of values in the horizontal category.
In Format Axis you should keep the Axis Type and the Vertical Axis Crosses.

Note

You can also go to Format Axis by right-clicking.

Read More: How to Change Axis Scale in Excel

Method 2 – Running an Excel VBA Code to Change the Chart Axis Scale Automatically

Step 1:

Go to the Developer tab.

Developer → Visual Basic

In Microsoft Visual Basic for Applications, insert a module.

Insert → Module

Step 2:

Enter the VBA code.

Function ChartAxisScale(sheetName As String, chartName As String, MinOrMax As String, _
    ValueOrCategory As String, PrimaryOrSecondary As String, Value As Variant)
Dim chart As chart
Dim text As String
'Set the function to control the chart
Set chart = Application.Caller.Parent.Parent.Sheets(sheetName) _
    .ChartObjects(chartName).chart
'Set Primary axis Value
If (ValueOrCategory = "Value" Or ValueOrCategory = "Y") _
    And PrimaryOrSecondary = "Primary" Then
    With chart.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 Primary axis Category
If (ValueOrCategory = "Category" Or ValueOrCategory = "X") _
    And PrimaryOrSecondary = "Primary" Then
    With chart.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 secondary axis value
If (ValueOrCategory = "Value" Or ValueOrCategory = "Y") _
    And PrimaryOrSecondary = "Secondary" Then
    With chart.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 secondary axis category
If (ValueOrCategory = "Category" Or ValueOrCategory = "X") _
    And PrimaryOrSecondary = "Secondary" Then
    With chart.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 IsNumeric(Value) Then text = Value Else text = "Auto"
ChartAxisScale = ValueOrCategory & " " & PrimaryOrSecondary & " " _
    & MinOrMax & ": " & text
End Function
Sub Axis_Scale()
End Sub

Run the VBA:

Run → Run Sub/UserForm

Create a user-defined function.
In your Excel sheet, select B20. Enter the user-defined function.

=ChartAxisScale(“Sheet1″,”Chart 2″,”Max”,”Value”,”Primary”,C19)

Code Breakdown:

This function modifies the properties of the Excel chart axis: sets the minimum or maximum value, or sets it to “Auto”, for the value (Y) or category (X) axis, either on the primary or secondary axis. The function includes 6 parameters: