How to Modify Excel Chart Axes with VBA Code

This tutorial explains how to modify and format the axes of chart in Excel using VBA code.

You can download the following workbook to practice.

1. Add X-axis and Y-axis

.HasAxis(xlCategory, xlPrimary) = True adds X axis in a chart.

.HasAxis(xlValue, xlPrimary) = True adds Y axis in a chart.

Let's take the given sample dataset to understand how it works.

Sub AddAxes()
    Dim chartObj As ChartObject
    Set chartObj = ActiveSheet.ChartObjects.Add(Left:=100, Width:=400, Top:=100, Height:=300)
    
    With chartObj.chart
        
        .SetSourceData Source:=Range("A1:C14")
        
        .ChartType = xlLine
        
     .HasAxis(xlCategory, xlPrimary) = True
     .HasAxis(xlValue, xlPrimary) = True
        End With
    
End Sub

Press Run or F5 to run the above code.

2. Add X-axis and Y-axis Title

.Axes(xlCategory).AxisTitle.Text = "President" returns "President" title to X-axis.

.Axes(xlValue).AxisTitle.Text = "Percentage Vote" returns "Percentage Vote" title to the Y-axis

Let's take the given sample dataset to understand how it works.

Sub AddAxesTitle()
    Dim chartObj As ChartObject
    Set chartObj = ActiveSheet.ChartObjects.Add(Left:=100, Width:=400, Top:=100, Height:=300)
    
    With chartObj.Chart
        .SetSourceData Source:=Range("A1:C14")
        .ChartType = xlLine
        
        
        .Axes(xlCategory).HasTitle = True
        .Axes(xlCategory).AxisTitle.Text = "President"
        
       
        .Axes(xlValue).HasTitle = True
        .Axes(xlValue).AxisTitle.Text = "Percentage Vote"
    End With
End Sub

Press Run or F5 to run the above code

3. Change Label Color for X-axis and Y-axis

.Axes(xlCategory).TickLabels.Font.Color = vbBlue returns blue color to the labels at X-axis.

.Axes(xlValue).TickLabels.Font.Color = vbRed returns red color to the labels at Y-axis.

Code for Color

.Font.Color = vbBlue - Blue Color
.Font.Color = vbRed - Red Color
.Font.Color = vbGreen - Green Color

Let's take the given sample dataset to understand how it works.

Sub ChangeAxisLabelColor()
    
    Dim chartObj As ChartObject
    Set chartObj = ActiveSheet.ChartObjects.Add(Left:=100, Width:=400, Top:=100, Height:=300)
    
    With chartObj.chart
        .SetSourceData Source:=Range("A1:C14")
        .ChartType = xlLine
        With .Axes(xlCategory).TickLabels
            .Font.Color = vbBlue
        End With
        With .Axes(xlValue).TickLabels
            .Font.Color = vbRed
        End With
    End With

End Sub

Press Run or F5 to run the above code.

.Axes(xlCategory).TickLabels.Font.Size = 14 set label font size to 14 in X-axis.

.Axes(xlValue).TickLabels.Font.Size = 14 set label font size to 14 in Y-axis.

Let's take the given sample dataset and set the Font size of axes to 14.

Sub ChangeAxisFontSize()
    Dim chartObj As ChartObject
    Set chartObj = ActiveSheet.ChartObjects.Add(Left:=100, Width:=400, Top:=100, Height:=300)
    
    With chartObj.Chart
        .SetSourceData Source:=Range("A1:C14")
        
        .ChartType = xlLine
        
        With .Axes(xlCategory).TickLabels
            .Font.Size = 14
        End With
        
        With .Axes(xlValue).TickLabels
            .Font.Size = 14
            End With
    End With
End Sub

Press Run or F5 to run the above code.

5. Remove X-axis and Y-axis

.Axes(xlCategory).Delete deletes the X-axis.

.Axes(xlValue).Delete deletes the Y-axis.

Let's take the given sample dataset to understand how it works.

Sub RemoveAxes()
    Dim chartObj As ChartObject
    Set chartObj = ActiveSheet.ChartObjects.Add(Left:=100, Width:=400, Top:=100, Height:=300)
    
    With chartObj.Chart
        
        .SetSourceData Source:=Range("A1:C14")
        
       
        .ChartType = xlLine
        
        
        On Error Resume Next
        .Axes(xlCategory).Delete
        
        
        .Axes(xlValue).Delete
        On Error GoTo 0
    End With
End Sub

Press Run or F5 to run the above code.

6. Change Number Formats of Axes

The following code can be used to apply a particular number format.

.NumberFormat = "0%" Format as Percentage (Example: 50%)
.NumberFormat = "0.00" Format with 2 Decimal Places (Example: 12.34)
.NumberFormat = "#,##0.00" Format as Number with Thousand Separator and 2 Decimal Places (Example: 1,234.56)
.NumberFormat = "$#,##0.00" Format as Currency (Example: $1,234.56)

Let's take the same sample dataset and change the values on Y-axis from percentage to decimal (upto two decimal places).

Sub NumberFormat()
    Dim chartObj As ChartObject
    Dim chart As chart
    
    
    Set chartObj = ActiveSheet.ChartObjects.Add(Left:=100, Width:=400, Top:=100, Height:=300)
    
    Set chart = chartObj.chart
    
    chart.SetSourceData Source:=Range("A1:C14")
    
    chart.ChartType = xlLine
     
    With chart.Axes(xlCategory)
        .HasTitle = True
        .AxisTitle.Text = "Category Axis"
    End With
    
   
    With chart.Axes(xlValue)
        .HasTitle = True
        .AxisTitle.Text = "Value Axis"
        .TickLabels.NumberFormat = "0.00"
    End With

Press Run or F5 to run the above code

VBA : Change Number Formats of Axis in Excel

7. Hide Chart Ticks on both axes

.Axes(xlCategory/xlValue).MajorTickMark = xlNone removes the major ticks from the selected axis.

.Axes(xlCategory/xlValue).MinorTickMark = xlNone removes the minor ticks from the selected axis.

Let's take the following Sample data to understand how it works

Sub HideChartTicks()
    Dim ws As Worksheet
    Dim chartObj As ChartObject
    Dim chart As chart

    Set ws = ThisWorkbook.Sheets("Sheet1") 
    Set chartObj = ws.ChartObjects("Chart 1") 
    Set chart = chartObj.chart

    
    With chart.Axes(xlCategory)
        .MajorTickMark = xlNone
        .MinorTickMark = xlNone
    End With

    With chart.Axes(xlValue)
        .MajorTickMark = xlNone
        .MinorTickMark = xlNone
    End With

End Sub

Press Run or F5 to run the above code

About Author:
Yash Gaur

Yash is pursuing an MBA in Finance with a keen interest in analytics. He enjoys working with data and leveraging his research and analytical skills to generate valuable insights.

Let's Get Connected Email LinkedIn