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.
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:
- Sheet name: The name of the sheet that contains the chart
- Chart name: The name of the chart to modify
- Min Or Max: Whether to set the minimum or maximum value
- Value Or Category: Whether to modify the value (Y) or category (X) axis
- Primary Or Secondary: Whether to modify the primary or secondary axis
- Value: The value to set for the axis
The function returns a string with the final state of the axis.
Download Practice Workbook
Related Articles
- How to Set Logarithmic Scale at Horizontal Axis of an Excel Graph
- How to Change Axis to Log Scale in Excel
- How to Break Axis Scale in Excel
- How to Set Intervals on Excel Charts
<< Go Back to Excel Axis Scale | Excel Charts | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
Mizbahul, Excellent article. Thank you. I have a worksheet with three curves which are reference lines (average, min and max of the anticipated data set) and them up to four seperate data sets (animal length and weight for what it’s worth). I was trying to set the auto-scaling to use the min and max from ONE of the seperate fish data sets. Excel want’s to use the reference lines but that data set is always going to be the largest anticipated value so the charts won’t scale to the actual animal data. I can manually set the scales, but do you know if there is a way to select the actual dataset used for the autoscaling? I can’t use Macros because the users (this is a free tool) are farmers and not necessarily excel-oriented users. Thanks!
Hello Jim Allen,
To address the issue of auto-scaling with reference lines (like min, max, or average) while using specific datasets, you can create a dynamic range formula for the data axis.
For example, use MIN and MAX functions with references to the desired dataset, ensuring the axis adjusts accordingly.
This avoids Excel defaulting to reference lines. By setting the axis scale manually or with a formula tied to the dataset, the chart will scale based on the actual data values. This solution doesn’t require macros, making it accessible to non-Excel users.
Regards
ExcelDemy