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.
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 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:
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 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)
The post Automatic Ways to Scale Excel Chart Axis (2 Suitable Ways) appeared first on ExcelDemy.
