6 Ways to Add a Secondary Axis in Microsoft Excel (2024)

Wondering how to add a secondary axis in Excel? It’s so easy if you follow along with this effortless Excel tutorial.

Microsoft Excel is an excellent data visualization tool because you barely need to do any chart configuration. All you need to do is select the whole dataset and insert an appropriate chart from automatic suggestions created by Excel.

However, chart-making becomes challenging when you need to create a relationship between multiple datasets with different scales of values. You can overcome the bottlenecks and extract actionable insights from the data visualization by adding a secondary axis in Excel.

In today’s article, I’ll delve into the techniques of adding a secondary axis in Excel. I’ll demonstrate step-by-step instructions with practical examples. Also, you’ll learn some secret tips to become a pro. Let’s dive in!

Use Recommended Charts to Add a Secondary Axis

If you’re using Excel 2013 or later, you can use the Recommended Charts command to create a chart with two Y axes. However, you should restructure the input dataset appropriately so Excel can easily understand which two columns should be used as Y axes.

6 Ways to Add a Secondary Axis in Microsoft Excel (1)

For example, the above dataset is the perfect candidate for this exercise. Here, I want to draw a relationship between revenue generation and discounts on products sold. This will allow me to increase the discounts if it helps in sales or reduce those if there’s no correlation between revenue and discounts.

If you’ve already organized your input dataset as shown in the above example, you’re ready to get started.

6 Ways to Add a Secondary Axis in Microsoft Excel (2)

Highlight the dataset and click on the Recommended Charts button inside the Charts block of the Insert tab.

You shall see the Insert Chart dialog. By default, Excel selects the Recommended Charts tab.

6 Ways to Add a Secondary Axis in Microsoft Excel (3)

The second chart type should be the perfect option that automatically converts the data series of lower values as the secondary axes of the chart.

Select that chart and hit the OK button to include the visualization in your worksheet.

6 Ways to Add a Secondary Axis in Microsoft Excel (4)

In the above Excel chart, the X axis is common for the first and second Y axes. The values in the Revenue column are displayed in the column chart and the percentages in the Discount column are plotted as a line chart.

You can add Axis Titles and Data Labels as chart modifications to help your audience easily distinguish two Y axes.

📒 Read More: 7 Ways to Add Chart Axis Labels in Microsoft Excel

Use Combo to Add a Secondary Axis in Excel

The Combo chart allows you to manually assign the Secondary Axis attribute to any of the Y axes to visualize more than one vertical axis in Excel.

6 Ways to Add a Secondary Axis in Microsoft Excel (5)

Select your dataset and add any chart you like from the Insert > Charts command block. It’s better to add either the column or the line chart objects.

It doesn’t matter whether you see the second data series in the chart after adding a basic graph at this stage.

6 Ways to Add a Secondary Axis in Microsoft Excel (6)

Click on the chart you’ve added and go to the Chart Design tab on the Excel ribbon menu.

There, select the Change Chart Type button inside the Type block.

6 Ways to Add a Secondary Axis in Microsoft Excel (7)

This should open the Change Chart Type dialog. Here, navigate to the All Charts tab and select the Combo chart object from the left-side navigation panel.

6 Ways to Add a Secondary Axis in Microsoft Excel (8)

Now, you can click the Clustered Column – Line on Secondary Axis chart template to modify the column chart to a Combo chart.

This option assigns the column containing higher values to the left side vertical axis and the rest to the right side vertical axis.

6 Ways to Add a Secondary Axis in Microsoft Excel (9)

If you wish to select the left and right side vertical axes yourself, choose Clustered Column – Line chart style.

Go to the bottom of the dialog box to make the following selections:

Chart Type: Choose two different chart types by clicking this drop-down for either data series name.
Secondary Axis: Checkmark the checkbox for the data series that you want to make the right-side vertical axis or secondary Y axis.

Adding a Secondary Axis Manually in Excel

If there’s already an Excel chart, you can reconfigure the chart object to add a secondary vertical axis easily.

6 Ways to Add a Secondary Axis in Microsoft Excel (10)

Suppose, you wish to plot the values in columns Revenue and Discount on the primary X axis, the Item column.

Since the Item and Revenue columns are already inside the chart, you simply need to add the Discount column.

6 Ways to Add a Secondary Axis in Microsoft Excel (11)

To achieve this, right-click the chart object and choose the Select Data option from the context menu.

6 Ways to Add a Secondary Axis in Microsoft Excel (12)

You shall now see the Select Data Source dialog box. Click the Add button below the Legend Entries (Series) column.

6 Ways to Add a Secondary Axis in Microsoft Excel (13)

Type a name inside the Series name field of the Edit Series dialog. Click on the up arrow of the Series values field and highlight the values of the Discount column. Ensure you don’t highlight the column header text.

If you accomplish the above steps accurately, you shall see the values of the new column in the chart object.

6 Ways to Add a Secondary Axis in Microsoft Excel (14)

However, due to the huge difference between the values of the Revenue and Discount columns, the Discount data series is barely visible on the chart.

Use Series Options to Add a Secondary Axis in Excel

Another effortless method to add a secondary axis in Excel is the Series Options menu of Format Data Series.

6 Ways to Add a Secondary Axis in Microsoft Excel (16)

Let’s find below how you can appropriately plot the secondary data series (Discount column) and axis in the above chart using Series Options.

The values of the Discount column are already in the chart. But, these aren’t visible due to the scale gap with the data of the Revenue column.

6 Ways to Add a Secondary Axis in Microsoft Excel (17)

To visualize the Discount data series, click any of the visible columns to bring up the Format Data Series on the right.

6 Ways to Add a Secondary Axis in Microsoft Excel (18)

Click the Series Options drop-down and choose the Series “Discount” option from the context menu.

6 Ways to Add a Secondary Axis in Microsoft Excel (19)

Now, click on the Series Options icon just below the drop-down and select the Secondary Axis radial button to visualize both the Revenue and Discount data series on the existing chart object.

6 Ways to Add a Secondary Axis in Microsoft Excel (20)

You should see a stacked column chart with two data series. On the right, you should also see the secondary axis relevant to the values in the Discount column.

If the stacked column chart isn’t suitable for you, reconfigure the chart to Combo by following the steps mentioned earlier in this article.

Adding a Secondary X Axis in Excel

So far, you’ve learned different methods to add a secondary vertical or Y axis in Excel. However, if needed, you can also plot two-column data values on a chart with an upper and lower X-axis data series.

6 Ways to Add a Secondary Axis in Microsoft Excel (21)

Suppose, you’ve created a column (Revenue column) and line chart (Total Items column) using the above dataset against the common horizontal axis Year.

6 Ways to Add a Secondary Axis in Microsoft Excel (22)

Now, you also want to add the Month column data as the upper horizontal axis.

6 Ways to Add a Secondary Axis in Microsoft Excel (23)

To insert the data series from Month, click on any white space of the chart object. Then, click the plus (+) sign in the top right corner of the chart to expand the Chart Elements menu.

6 Ways to Add a Secondary Axis in Microsoft Excel (24)

Hover the cursor over the Axes option on the context menu to bring up an arrow. Click on the arrow to open the overflow context menu.

There, click on the Secondary Horizontal option.

6 Ways to Add a Secondary Axis in Microsoft Excel (25)

Excel shall replicate the primary horizontal axis as the secondary horizontal axis.

6 Ways to Add a Secondary Axis in Microsoft Excel (26)

Now, click on the secondary horizontal axis to highlight the year values. Right-click and choose Select Data from the context menu.

6 Ways to Add a Secondary Axis in Microsoft Excel (27)

You should see the Select Data Source dialog. Click on the Edit button below the Horizontal (Category) Axis Labels column.

6 Ways to Add a Secondary Axis in Microsoft Excel (28)

The Axis Labels dialog shall pop up. Use the arrow icon on this dialog to select the cells below the Month column.

6 Ways to Add a Secondary Axis in Microsoft Excel (29)

Click OK to update the Horizontal Axis Labels data series.

6 Ways to Add a Secondary Axis in Microsoft Excel (30)

Select OK again on the Select Data Source dialog to add Month values as the secondary X axis in Excel.

Use Excel VBA to Add a Secondary Axis in Excel

Excel VBA programming allows you to automate various repetitive and time-consuming tasks in Excel. If you’d like to automatically add a secondary axis with minimal actions taken from your end, you must try this method.

Firstly, you need to create a VBA macro using the following VBA script:

6 Ways to Add a Secondary Axis in Microsoft Excel (31)

Sub AddSecondaryAxis() ' Declare variables Dim ChartName As String Dim DataRange As Range ' Get the chart name from the user ChartName = InputBox("Enter the name of the chart") ' Check if the chart exists in the ActiveSheet If Not ChartExists(ChartName) Then MsgBox "The specified chart does not exist in the active sheet." Exit Sub End If ' Get the data range for the secondary Y axis from the user On Error Resume Next Set DataRange = Application.InputBox("Select the data range for the secondary Y axis", Type:=8) On Error GoTo 0 ' Check if a range was selected If DataRange Is Nothing Then MsgBox "No data range was selected." Exit Sub End If ' Add the secondary Y axis to the chart With ActiveSheet.ChartObjects(ChartName).Chart .SeriesCollection.NewSeries .SeriesCollection(.SeriesCollection.Count).Values = DataRange .SeriesCollection(.SeriesCollection.Count).AxisGroup = 2 .SeriesCollection(.SeriesCollection.Count).ChartType = xlLine ' Add this line End With MsgBox "Secondary Y axis added successfully."End Sub' Function to check if a chart exists in the ActiveSheetFunction ChartExists(ChartName As String) As Boolean Dim cht As ChartObject ChartExists = False For Each cht In ActiveSheet.ChartObjects If cht.Name = ChartName Then ChartExists = True Exit Function End If Next chtEnd Function

You don’t need to customize any part of the above script. I’ve ensured that there aren’t any hard-coded cell or function references in the VBA code.

If you’re about to set up a VBA macro for the first time, refer to this article:

📒 Read More: How To Use The VBA Code You Find Online

6 Ways to Add a Secondary Axis in Microsoft Excel (32)

If you’ve successfully created the VBA macro, you can run it by pressing Alt + F8 keys, selecting the AddSecondaryAxis macro, and hitting the Run button on the Macro dialog.

6 Ways to Add a Secondary Axis in Microsoft Excel (33)

When the macro runs, it shows an input box so you can enter the name of the target chart on the active worksheet.

6 Ways to Add a Secondary Axis in Microsoft Excel (34)

Then, you can use the mouse cursor to select the input dataset.

6 Ways to Add a Secondary Axis in Microsoft Excel (35)

Finally, Microsoft Excel modifies the existing chart to include the secondary Y axis you’ve been looking for.

How to Remove a Secondary Axis in Excel (Bonus)

Removing a secondary axis in Excel is more straightforward than adding it.

6 Ways to Add a Secondary Axis in Microsoft Excel (36)

Go to the target worksheet and find the chart object from which you want to remove an additional Y axis.

Click on the chart data series linked to the secondary axis.

6 Ways to Add a Secondary Axis in Microsoft Excel (37)

Hit the Delete key on the keyboard.

Conclusions

These are the tried and tested methods to add a secondary axis in Microsoft Excel. You can use any technique that matches your Excel expertise level. However, my personal favorite is the Excel VBA-based method.

If the article helped you or you’d like to share another secret method to add a secondary axis, comment below.