Automatic Ways to Scale Excel Chart Axis (2 Suitable Ways)

One of the most often used Excel features is a chart. It can be used for various things, such as a year-round weather report, monthly sales reports, business reports, stock analysis, etc. In this article, we are going to learn about changing automatic ways to scale Excel chart axis . We will do it in a Bar Chart. Here is the overview of our dataset.

Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.

Introduction to Chart Axis Scale in Excel

Charts typically have two axes that are used to measure and categorize data. In Microsoft Excel, the horizontal (category) axis is known as X-axis, and the vertical(category) axis is known as Y-axis. Data can be plotted along a chart’s depth using the depth axis, which is a third axis seen in the 3-D columns, 3-D cones, or 3-D pyramid charts. Horizontal (Category) axes are absent from pie and doughnut charts as well as radar charts.

Step-by-Step Procedures to Change Chart Axis Scale Automatically in Excel

1. Use Format Axis Feature to Change Chart Axis Scale in Excel

In this method, we will learn how to change chart axis automatically by using the Format Axis feature in Excel. Let’s follow the instructions below to learn!

Step 1: Creating a Dataset for X and Y axis

Let’s choose cells B4 and C4, where we will enter data from a sales report broken down by month for a whole year.

Step 2: Insert 2-D Column Chart

• Again click on cell B4.
• After that, click Insert tab >> you will see the scatter logo, click on that logo and insert the Bar Chart.

• As a result, Bar Chart will appear before you like the following image.

Step 3: Changing Chart Axis Scale Automatically

• Further, select the vertical values of the scatter chart and now press right click of your mouse
• Afterward, select the Format Axis option

• After that, select the Axis option from Format Axis
• Now, go to Units >> Change the unit to 3000.

• Hence, you’ll see the Maximum Bounds will change to 21000 from 20000 automatically and the unit of the Y-axis change to 3000 from 2000 as in the image below.

• For a better understanding, see the GIF image below.
• For the X-axis, we can’t change the scale because, in the horizontal category, we used texts instead of values. That’s why we kept the horizontal data of the chart unchanged. When you will go to the Format Axis you should keep the Axis Type and Vertical Axis Crosses.

Read More: How to Change Y Axis Scale in Excel (with Easy Steps)

2. Run an Excel VBA Code to Change Chart Axis Scale Automatically

Now I’ll show you how to change the chart axis scale in Excel by using a simple VBA code. It’s very helpful for some particular reason. From our dataset, we will change the chart axis in Excel. Let’s follow the instructions below to learn!

Step 1:

• First of all, open a Module, to do that, firstly, from your Developer tab, go to,

                                              Developer → Visual Basic

• After clicking on the Visual Basic ribbon, a window named Microsoft Visual Basic for Applications – will instantly appear in front of you. From that window, we will insert a module for applying our VBA code. To do that, go to,

                                                                    Insert → Module

Step 2:

• Hence, you’ll see an open space after selecting the module. Now paste the below VBA code in the box.

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

• Hence, run the VBA To do that, go to,

                                                 Run → Run Sub/UserForm

• After running the VBA Code, you will be able to create a user-defined function. After that, go back to your excel sheet, and select cell B20. Hence, write down the below user-defined function.

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

Code Explanation:

This function modifies the properties of an Excel chart axis. It 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 is passed 6 parameters:

1. Sheet name: The name of the sheet that contains the chart
2. Chart name: The name of the chart to modify
3. Min Or Max: Whether to set the minimum or maximum value
4. Value Or Category: Whether to modify the value (Y) or category (X) axis
5. Primary Or Secondary: Whether to modify the primary or secondary axis
6. Value: The value to set for the axis

The function returns a string indicating the final state of the axis after the modification.

Read More: How to Change Axis Scale in Excel (with Easy Steps)

Conclusion

The entire process of changing the chart axis scale automatic in Excel is covered in this session. We hope you will effectively use the techniques on your Excel spreadsheets now that you know them all. Please feel free to leave any feedback or inquiries in the box below. Additionally, you can look through ExcelDemy’s other posts on Excel functions.

Related Articles

• How to Set Logarithmic Scale at Horizontal Axis of an Excel Graph
• Change Axis to Log Scale in Excel (3 Easy Ways)
• How to Change X Axis Scale in Excel (2 Useful Methods)
• Break Axis Scale in Excel (3 Suitable Ways)
• How to Break the Axis Scale in Excel (3 Effective Ways)
• Scale Time on X Axis in Excel Chart (3 Quick Methods)