How to Format Y-Axis Numbers in Excel VBA Charts?

Sharing is caring!

Are you trying to format the y-axis number labels in your Excel chart using VBA? This article will show you exactly how to customize the number format of the y-axis in your charts with some simple VBA code. Whether you want to display the numbers as currency, percentages, or with a specific number of decimal places, you can achieve the desired formatting programmatically.

Understanding Chart Axis Number Formatting in Excel

Before we dive into the VBA code, let’s first understand how number formatting works for chart axes in Excel:

• By default, Excel automatically chooses a number format for the y-axis labels based on the data being plotted
• You can manually change the number format by right-clicking the y-axis labels, selecting “Format Axis”, and specifying the desired number format
• Number formatting options for the y-axis include General, Number, Currency, Accounting, Date, Time, Percentage, Fraction, Scientific, and Text
• You can specify the number of decimal places, whether to use a thousands separator, the currency symbol to use, and other formatting details

While it’s straightforward to set the y-axis number format manually through the Excel interface, you may want to control the formatting dynamically with VBA. This is especially useful if you are generating charts programmatically or want to update the formatting of multiple charts at once.

The VBA Code to Format Y-Axis Numbers

Here’s the core VBA code you’ll need to format the y-axis numbers in your Excel chart:

'Format y-axis numbers
ActiveChart.Axes(xlValue).TickLabels.NumberFormat = "your_number_format"

To use this code:

1. Replace "your_number_format" with the actual number format you want to apply, enclosed in double quotes. See the next section for examples of common number formats.
2. Place this line of code in your VBA macro or procedure, after the code that creates or activates the chart you want to format.
3. Run the code and the y-axis number labels in the active chart will update with the specified formatting.

That’s really all there is to it! With this one line of VBA, you can programmatically set the number format for the y-axis of your Excel chart.

Let’s break down the code a bit:

• ActiveChart refers to the currently selected chart. If you have multiple charts, make sure the one you want to format is active (selected) before running this code.
• Axes(xlValue) specifies that we want to format the value axis, which is typically the y-axis. For the category axis (usually the x-axis) you would use Axes(xlCategory) instead.
• .TickLabels.NumberFormat is the property that controls the number formatting of the axis labels. This is where we specify the format code.
• = is the assignment operator that sets the number format equal to the code we provide in quotes on the right side.

Once you understand the basic syntax, you can adapt this code to format any axis in any chart in Excel using VBA. Just make sure to use a valid number format code and to activate the correct chart before running the code.

Examples of Number Formats

Here are some examples of frequently used number formats you can specify in the VBA code:

Feel free to use any of these number formats or create your own custom format based on your specific needs. The same format codes used for cell number formatting in Excel can be used here.

A few tips for working with number format codes:

• Use # for optional digits, 0 for required digits
• Separate thousands with , and decimal places with .
• Enclose literal characters like $ or % in double quotes
• Escape special characters like - or / with a backslash: \\
• For more advanced formats, separate positive and negative with ;

Formatting Other Chart Axis Elements

In addition to formatting the y-axis number labels, you can use VBA to format other parts of the chart axes:

X-Axis Number Formatting

To format the number labels on the x-axis (category axis), use:

ActiveChart.Axes(xlCategory).TickLabels.NumberFormat = "your_number_format"

This is useful if your x-axis displays numbers, dates, or times instead of text categories.

Axis Title Text

To change the text of the y-axis title, use:

ActiveChart.Axes(xlValue).AxisTitle.Text = "your_axis_title"

And for the x-axis title:

ActiveChart.Axes(xlCategory).AxisTitle.Text = "your_axis_title"

Axis Title Formatting

To format the font of the axis title, use something like:

With ActiveChart.Axes(xlValue).AxisTitle.Font
    .Name = "Arial"
    .Size = 12
    .Bold = True
    .Color = RGB(0, 0, 255)
End With

You can specify the font name, size, whether it is bold or italic, text color, and other font properties. Use xlCategory instead of xlValue to format the x-axis title.

Axis Minimum and Maximum

To set the minimum and maximum values displayed on the y-axis:

ActiveChart.Axes(xlValue).MinimumScale = your_min_value
ActiveChart.Axes(xlValue).MaximumScale = your_max_value

Replace your_min_value and your_max_value with the numbers you want to use for the axis start and end points. This allows you to control the scale and zoom level of the chart.

Other axis properties you can format with VBA include:

• Gridlines (major and minor)
• Tick marks (major and minor)
• Axis line color and weight
• Log or linear scale
• Alignment and orientation of labels
• Display units
• And more…

With VBA, you have an incredible amount of control over the look and layout of your chart axes. Refer to the macro recorder or the Excel VBA reference to explore all the possibilities.

Putting it All Together

Here’s an example that shows formatting multiple aspects of the chart y-axis:

Sub FormatChartYAxis()

    'Assumes the active chart is the one to be formatted
    With ActiveChart.Axes(xlValue)
        .TickLabels.NumberFormat = "$#,##0.00"
        .AxisTitle.Text = "Revenue (USD)"
        .AxisTitle.Font.Name = "Arial"
        .AxisTitle.Font.Size = 12
        .AxisTitle.Font.Color = RGB(0, 0, 255)
        .MinimumScale = 0
        .MaximumScale = 100000
    End With

End Sub

This VBA procedure formats the active chart with:

• Y-axis numbers displayed as currency with 2 decimal places and a thousands separator
• Y-axis title set to “Revenue (USD)”
• Y-axis title font set to 12 pt blue Arial
• Y-axis scale set to a minimum of 0 and maximum of 100,000

Adapt this code to your needs by changing the number format, axis title, font, color, scale range, etc. You can call this procedure from another macro, assign it to a button, or run it automatically when certain events occur.

Here are some tips for incorporating chart axis formatting into your VBA code:

• Use variables to store the number format and other parameters so you can easily change them
• Check if a chart is active before formatting, or use a specific chart name or index number
• Optionally allow the user to input the desired formatting with an InputBox
• Loop through all the charts in a worksheet or workbook to apply formatting to each one
• Store default formatting options in a config sheet and load them into your VBA code
• Create a custom function to return the appropriate number format based on the data

There are many ways to approach chart axis formatting in VBA, so experiment to find what works best for your situation.

Summary

Formatting chart y-axis numbers in Excel using VBA is a straightforward process:

1. Use the .TickLabels.NumberFormat property to set the number formatting
2. Specify number format codes like "#,##0.00", "0%", "$#,##0.00" etc.
3. Combine with other VBA statements to format axis title, font, scale range, and more
4. Place the formatting code after the chart creation/activation code in a VBA procedure

With a bit of VBA, you can dynamically control the look of y-axis labels to display numbers exactly as you want. This is a great way to enhance the readability and visual appeal of your Excel charts.

Proper number formatting is essential for creating professional-looking charts that effectively communicate the insights in your data. By automating the process with VBA, you can save time, ensure consistency, and create impressive interactive dashboards.

FAQs

Vaishvi Desai

Vaishvi Desai is the founder of Excelsamurai and a passionate Excel enthusiast with years of experience in data analysis and spreadsheet management. With a mission to help others harness the power of Excel, Vaishvi shares her expertise through concise, easy-to-follow tutorials on shortcuts, formulas, Pivot Tables, and VBA.