5 Ways To Add A Secondary Axis In Excel

When working with data in Excel, it's not uncommon to have multiple data series that you want to display on the same chart. However, these data series might have different units or scales, making it difficult to visualize them on the same axis. This is where adding a secondary axis in Excel comes in handy. In this article, we'll explore five ways to add a secondary axis in Excel, along with practical examples and step-by-step instructions.

Understanding the Need for a Secondary Axis

When you have multiple data series with different scales, using a single axis can make the chart difficult to read. For instance, if you're charting the sales figures and profit margins of a company, the sales figures might be in the thousands, while the profit margins are in percentages. In such cases, having a secondary axis can help you visualize both data series more effectively.

Method 1: Using the Built-in Secondary Axis Option

One of the easiest ways to add a secondary axis in Excel is to use the built-in option. Here's how:

• Select the data series you want to display on the secondary axis.
• Go to the "Chart Design" tab in the ribbon.
• Click on the "Change Chart Type" button.
• In the "Chart Type" dialog box, select the chart type you want to use (e.g., line chart, column chart, etc.).
• Check the box next to "Secondary Axis" and click "OK."

Example: Adding a Secondary Axis to a Line Chart

Suppose you have a line chart showing the sales figures and profit margins of a company over time. To add a secondary axis for the profit margins, follow these steps:

• Select the profit margin data series.
• Go to the "Chart Design" tab and click on the "Change Chart Type" button.
• In the "Chart Type" dialog box, select the "Line" chart type.
• Check the box next to "Secondary Axis" and click "OK."

The chart will now display the sales figures on the primary axis and the profit margins on the secondary axis.

Method 2: Using the "Format Data Point" Option

Another way to add a secondary axis in Excel is to use the "Format Data Point" option. Here's how:

• Select the data series you want to display on the secondary axis.
• Right-click on the data series and select "Format Data Point."
• In the "Format Data Point" dialog box, click on the "Series Options" button.
• Check the box next to "Plot on secondary axis" and click "OK."

Example: Adding a Secondary Axis to a Column Chart

Suppose you have a column chart showing the sales figures and profit margins of a company by region. To add a secondary axis for the profit margins, follow these steps:

• Select the profit margin data series.
• Right-click on the data series and select "Format Data Point."
• In the "Format Data Point" dialog box, click on the "Series Options" button.
• Check the box next to "Plot on secondary axis" and click "OK."

The chart will now display the sales figures on the primary axis and the profit margins on the secondary axis.

Method 3: Using VBA Macros

If you need to add a secondary axis to multiple charts, using VBA macros can save you time and effort. Here's an example of a VBA macro that adds a secondary axis to a chart:

Sub AddSecondaryAxis()
    Dim cht As Chart
    Dim axs As Axis
    
    Set cht = ActiveChart
    Set axs = cht.Axes(xlValue)
    
    axs.HasTitle = True
    axs.Axistitle.Text = "Primary Axis"
    
    Set axs = cht.Axes(xlValue, xlSecondary)
    axs.HasTitle = True
    axs.Axistitle.Text = "Secondary Axis"
End Sub

To use this macro, follow these steps:

• Open the Visual Basic Editor (VBE) by pressing "Alt + F11" or by navigating to Developer > Visual Basic in the ribbon.
• In the VBE, click on "Insert" > "Module" to insert a new module.
• Paste the macro code into the module.
• Click on "Run" > "Run Sub/UserForm" to run the macro.

The macro will add a secondary axis to the active chart.

Method 4: Using a Secondary Axis with Multiple Data Series

If you have multiple data series that you want to display on the same chart, you can use a secondary axis to differentiate between them. Here's an example:

• Select the data series you want to display on the primary axis.
• Go to the "Chart Design" tab and click on the "Select Data" button.
• In the "Select Data Source" dialog box, select the data series you want to display on the secondary axis.
• Check the box next to "Plot on secondary axis" and click "OK."

Example: Adding a Secondary Axis to a Line Chart with Multiple Data Series

Suppose you have a line chart showing the sales figures of a company over time, with multiple data series representing different regions. To add a secondary axis for the sales figures of a specific region, follow these steps:

• Select the data series for the region you want to display on the secondary axis.
• Go to the "Chart Design" tab and click on the "Select Data" button.
• In the "Select Data Source" dialog box, select the data series you want to display on the secondary axis.
• Check the box next to "Plot on secondary axis" and click "OK."

The chart will now display the sales figures for all regions on the primary axis and the sales figures for the selected region on the secondary axis.

Method 5: Using a Secondary Axis with a Combo Chart

If you want to display multiple data series with different chart types (e.g., columns and lines), you can use a combo chart with a secondary axis. Here's an example:

• Select the data series you want to display on the primary axis.
• Go to the "Chart Design" tab and click on the "Change Chart Type" button.
• In the "Chart Type" dialog box, select the "Combo" chart type.
• Check the box next to "Secondary Axis" and click "OK."

Example: Adding a Secondary Axis to a Combo Chart

Suppose you have a combo chart showing the sales figures and profit margins of a company over time, with columns representing the sales figures and a line representing the profit margins. To add a secondary axis for the profit margins, follow these steps:

• Select the profit margin data series.
• Go to the "Chart Design" tab and click on the "Change Chart Type" button.
• In the "Chart Type" dialog box, select the "Combo" chart type.
• Check the box next to "Secondary Axis" and click "OK."

The chart will now display the sales figures on the primary axis and the profit margins on the secondary axis.

Secondary Axis in Excel Image Gallery

Adding a secondary axis in Excel can help you create more effective and informative charts. By using the methods outlined in this article, you can add a secondary axis to your charts and make your data visualization more compelling. Whether you're working with multiple data series or different chart types, a secondary axis can help you tell a more nuanced story with your data.