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

Get FREE Advanced Excel Exercises with Solutions!

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.

excel chart axis scale automatic


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.


Automatic Ways to Scale Excel Chart Axis: 2 Suitable Ways

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.

Dataset for X and Y axis


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.

Process of generating Bar Chart

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

The Bar Chart


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

Selecting "Format Axis" from Bar Chart

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

change major unit using Axis Options feature to set chart axis scale

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

The value scale of Y-axis changed automatically

  • For a better understanding, see the GIF image below.
  • GIF of changing excel chart axis scale automaticallyWe used texts instead of values in the horizontal category so, we can’t change the x-axis scale. 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.

Format Axis option on X-axis

Note

You can also go to the Format Axis option directly by double-clicking the Right Button on the mouse.

  • First, select the data of a respected axis.
  • Then, double-click the Right Button on the mouse.

Read More: How to Change Axis Scale in Excel


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

selecting 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

Selection of 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

Entering VBA code into the box

 

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

                                                 Run → Run Sub/UserForm

Run the VBA code

  • 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)

Entering user defined function to change chart axis scale

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.


Download Practice Workbook


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.


Related Articles


<< Go Back to Excel Axis Scale | Excel Charts | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Mizbahul Abedin
Mizbahul Abedin

Md Mizbahul Abedin, BSc, Textile Engineering and Management, Bangladesh University of Textiles, has been working with the ExcelDemy project for 11 months. Currently working as an Excel and VBA Content Developer who provides authentic solutions to different Excel-related problems and writes amazing content articles regularly. He has published almost 20 articles in ExcelDemy. He has passions for learning new things about Microsoft Office Suite and Data analysis. Besides, he also likes to travel, photography, international politics, and read... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo