How to automatically adjust chart axis scaling in Excel

How to automatically adjust chart axis scaling in Excel. Creating a chart in Excel is very simple, but dividing the X and Y axis scaling symmetrically and aesthetically is still a problem for many Excel users. In this article, UniTrain will guide you through two automatic methods to adjust chart axis scaling in Excel: 1. Using the Format Axis feature in Excel 2. Running VBA code in Excel

1. Using the Format Axis feature to change adjust chart axis scaling in Excel

To automatically change the chart axis by using the Format Axis feature in Excel, follow these steps:

Step 1: Create data for the X and Y axes (as shown below).

How to automatically adjust chart axis scaling in Excel

Step 2: Insert a 2-D column chart by selecting cell B4 > Insert > selecting the chart icon and inserting a column chart (as shown below).

How to automatically adjust chart axis scaling in Excel

Then, the column chart will be displayed as shown below:

How to automatically adjust chart axis scaling in Excel

Step 3: Right-click and select Format Axis.

How to automatically adjust chart axis scaling in Excel

Then, select Axis option > Units > change it to 3000.

How to automatically adjust chart axis scaling in Excel

You will see that Maximum Bounds will automatically change from 20000 to 21000, and the unit of the Y-axis will change from 2000 to 3000 as shown below:

How to automatically adjust chart axis scaling in Excel

However, for the X-axis, users cannot change the scaling because they are using text instead of values. That’s why users keep the chart’s horizontal data unchanged. Right-click > select Format Axis > select Automatically select based on data in the Axis Type section and Automatic in the Vertical axis crosses section.

2. Run Excel VBA code to automatically change chart axis scale

Procedure: Step 1: Select Developer > Visual Basic.

When the Microsoft Visual Basic for Applications window appears, select Insert > Module.

Step 2: Insert the code below into the Module.

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

Then, select Run > Run Sub/UserForm.

After running the VBA code, you can create a user-defined function.
Then, go back to the Excel worksheet, select cell B20 and enter: =ChartAxisScale(“Sheet1″,”Chart 2″,”Max”,”Value”,”Primary”,C19)