Secondary Axis In Excel

What Is Secondary Axis In Excel?

A secondary axis in Excel chart is an additional axis that helps visualize data in another dimension or scale. It is useful for comparing data sets measured in different units in the same chart.

Also, users can use the secondary axis in a Combo chart to represent data of different types in one graph, such as performance versus the conversion rate.

For example, the table below contains a firm’s annual sales and profit margin data.

Secondary Axis in Excel - 1

Meanwhile, if the requirement is to analyze the given data using a chart, then, we can use a Clustered Column chart. Then, using create secondary axis in Excel chart, we can represent the source data visually.

Secondary Axis in Excel - 2

In the above example, we insert the required chart type using the Recommended Charts option in the Insert tab. Furthermore, while the X-axis shows the years, the primary and secondary Y-axes indicate the sales numbers and profit margins.

And plotting secondary axis in Excel Column chart ensures that we display the sales figures and profit margin percentages in the same chart, though they have different units. It thus makes comparing the two parameter types more straightforward.

Key Takeaways
  • The secondary axis in Excel chart is another axis, enabling us to review more than one data series in the same plot. And the data series can be of different scales and data types. Users can add the secondary axis to an Excel chart for comparing data of different data types, such as population vs. death rate and sales vs. profit margins.
  • We can add a secondary X- and Y-axis to an Excel chart directly or manually.
  • We can change the secondary axis by changing the data series it represents or its other features, such as chart type and scale.

How To Add A Secondary Axis In Excel?

We can add a secondary axis to a chart in Excel using two methods, namely:

  1. Simple To Add Secondary Axis
  2. Manually Add A Secondary Axis To The Excel Chart


Excel VBA – All in One Courses Bundle (35+ Hours of Video Tutorials)

If you want to learn Excel and VBA professionally, then ​Excel VBA All in One Courses Bundle​ (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.

Method #1 – Simple To Add Secondary Axis

The steps to directly add a secondary axis to an Excel chart are as follows:

  1. Create the required chart based on the source data containing multiple data series.
  2. Click the chart area to enable the Design tab in the ribbon and select the Change Chart Type option in the Design tab to open the Change Chart Type window.
  3. Click the Recommended Charts tab in the Change Chart Type window and choose the best suitable chart containing the secondary axis.
  4. Finally, update the chart and axes titles to complete the chart for better analysis.

Method #2 – Manually Add A Secondary Axis To The Excel Chart

The steps to manually add a secondary axis to an Excel chart are as follows:

  1. Create the required chart based on the source data containing multiple data series.
  2. Click the chart area to enable the Format tab in the ribbon. And select the data series element we require to represent using the secondary axis from the drop-down list in the Current Selection group in the Format tab. The specific data series gets chosen in the chart.
  3. Press the keys Ctrl + 1 or right-click the chart area and choose Format Data Series in the context menu to access the correspondingpane.
  4. Next, set the Plot Series On option in the Series Options tab in the Format Data Series pane as Secondary Axis for manually inserting the secondary axis in the chart.
  5. Next, we must change the secondary axis data series chart type. And for that, click the chart area to enable the Design tab and select the Change Chart Type option.
  6. The All Charts tab in the Change Chart Type window opens, showing the current Combo chart preview.
  7. Select the required chart type using the secondary axis data series drop-down list.
  8. Finally, clicking OK will show the plot with different chart types representing the primary and secondary axes data series.

Examples

Check out the following methods explained with examples, which will help us create secondary axis in Excel charts.

Method #1 – Simple To Add Secondary Axis In Excel

We shall see the steps to directly create a chart with the secondary y axis in Excel with an example.

The table below shows the students’ scores and attendance percentages.

Secondary Axis in Excel - Method 1

If the requirement is to represent the above data graphically. Then, here is how to insert a Clustered Column chart for visual analysis.

  • Step 1: First, click on a cell within the source data range and then, select Insert Column or Bar Chart 2-D Clustered Column chart type.
Method 1 - Step 1a
Method 1 - Step 1b

Next, click the required chart type results in the following plot.

Method 1 - Step 1c

However, we cannot view the attendance plot, though the legend shows the Attendance (%) data series. The reason is that the attendance data is in percentage, and the numeric values are smaller than the scores’ magnitude. And analyzing the above chart will not give an accurate inference.

Thus, plotting secondary axis in Excel to display the Attendance (%) data series will resolve the issue. And the steps are as follows:

  • Step 2: Next, click the chart area to enable the Design tab in the ribbon. And then, click the Change Chart Type option in the Design tab to open the Change Chart Type window.
Method 1 - Step 2a
Method 1 - Step 2b
  • Step 3: Now, go to the Recommended Charts tab in the Change Chart Type window and then, select the first Clustered Column chart option containing the secondary y axis in Excel plot.
Method 1 - Step 3a

The below plot shows the student names on the X-axis. On the other hand, its primary Y-axis shows the subject scores using the Clustered Column chart. And the secondary Y-axis displays the attendance percentages using the Line chart in excel.

Method 1 - Step 3b
  • Step 4: Click the chart area to enable the Chart Elements option (+ icon) and check the Axis Titles box.
Method 1 - Step 4a

Now that we have the chart and axes titles’ elements in the chart area double-click each element one at a time to update them and achieve the following graph.

Method 1 - Step 4b

Thus, the secondary axis helps us analyze the two entities of different types and scales in the same chart.

Method #2 – Manually Add Secondary Axis To The Chart

We shall see the steps to manually add the secondary axis to an Excel chart with an illustration.

The table below shows the population and the death rate per 1,000 people in five US states.

Secondary Axis in Excel - Method 2

If the requirement is to create a Clustered Column chart for the above data for visual analysis. Then the steps are as follows:

  • Step 1: First, click on a cell within the given data range, and then, select Insert Cluster or Bar Chart 2-D Clustered Column chart.
Method 2 - Step 1a
Secondary Axis in Excel - Method 2 - Step 1b

However, we do not see the death rate data series in the chart, though the legend shows it. The reason is that the data series’ type and scale differ from the Population (2022) data series’ type and scale.

The solution is adding the secondary Y-axis in the chosen chart to ensure we can view the two data series for visual analysis.

  • Step 2: Next, click the chart area to enable the Format tab in the ribbon.
Method 2 - Step 2a

Next, set the chart element in the Current Selection group as the death rate data series.

Method 2 - Step 2b

We will see the selected data series highlighted or chosen in the chart area.

Secondary Axis in Excel - Method 2 - Step 2c
  • Step 3: Now, right-click in the chart area, choose Format Data Series in the context menu or use the shortcut Ctrl + 1 to access the Format Data Series pane.
 Method 2 - Step 3a

Next, in the Format Data Series pane, set the Plot Series On setting in the Series Options tab as Secondary Axis.

Method 2 - Step 3b

The resulting chart appears like a Stacked Column chart, which may lead to incorrect data analysis. Thus, we shall change the secondary axis data series chart type using the following steps.

  • Step 4: Next, with the secondary axis data series selected, click on DesignChange Chart Type to open the Change Chart Type window.
Method 2 - Step 4a

Next, the All Charts tab opens in the Change Chart Type window, showing the Combo chart. Then, set the death rate data series chart type as a Line chart. And the corresponding Secondary Axis box must remain checked.

Method 2 - Step 4b
Method 2 - Step 4c

Then, click OK will result in the following chart.

Secondary Axis in Excel - Method 2 - Step 4d
  • Step 5: Now, click the chart area to enable the Chart Elements icon, and then, check the Axis Titles option in the Chart Elements window.
Secondary Axis in Excel - Method 2 - Step 5a

Finally, double-click the chart title and axis titles’ elements one at a time in the chart area to update them and obtain the below plot.

Secondary Axis in Excel - Method 2 - Step 5b

Thus, we can analyze the population figures and the death rate percentages in the five states using one graph.

How To Change The Secondary Axis In Excel?

Let us see the steps to change the secondary axis to an Excel chart with an example.

The table below contains the expenses, sales, and units sold details at different branches of a firm.

How to Change Secondary Axis in Excel

Next, based on the given data, we create a Line chart with the Units Sold data series in the secondary Y-axis, as shown below.

How to Change Secondary Axis in Excel - 1

If the requirement is to change the secondary axis data series. Then, the steps are as follows:

  • Step 1: First, click the chart area to enable the Design tab in the ribbon.

Next, in the Design tab, select the Change Chart Type option to open the Change Chart Type window.

How to Change Secondary Axis in Excel - Step 1a

The All Charts tab in the Change Chart Type window shows the current Combo chart.

How to Change Secondary Axis in Excel - Step 1b
  • Step 2: Next, uncheck the Units Sold data series’ Secondary Axis box.
How to Change Secondary Axis in Excel - Step 2a

Then, check the Expenses ($) and Sales ($) data series’ Secondary Axis boxes, one by one, as shown below.

How to Change Secondary Axis in Excel - Step 2b

Step 3: Now, using the Units Sold data series drop-down chart list, select the 2-D Clustered Column chart.

How to Change Secondary Axis in Excel - Step 3a
How to Change Secondary Axis in Excel - Step 3b

The reason to change the Units Sold data series chart type is to ensure we can visually analyze the data better.

And clicking OK will result in the below chart.

How to Change Secondary Axis in Excel - Step 3c

However, the secondary Y-axis title is missing, and we must interchange the Y-axis titles.

  • Step 4: Now, click the chart area to enable the Chart Elements option. Next, hover over the Axis Titles option. The action will show the right arrow. Then, click the right arrow and check the Secondary Vertical box, as we require the secondary Y-axis title element in the chart area.
How to Change Secondary Axis in Excel - Step 4a

Finally, double-click the primary and secondary Y-axes title elements in the chart area, one by one, to update them and achieve the below chart.

How to Change Secondary Axis in Excel - Step 4b

Thus, the final chart shows the Units Sold data as a Clustered Column chart in the primary Y-axis. And the remaining two data series have the chart type set as Line, and the secondary Y-axis represents them.

On the other hand, if we retain the data series that the secondary axis originally represented and change specific aspects of the axis, such as scale units and chart type. Then, such steps also count as changing the secondary axis.

How To Remove The Secondary Axis In Excel?

Let us see the steps to remove the secondary axis from an Excel chart with an example.

The below image shows a table containing values of different powers of the variable X and a 2-D Clustered Bar chart based on the given data.

And while the Y-axis represents the X data series, the primary X-axis shows the X4 data series and the secondary X-axis at the top of the plot shows the X2 data series.

How to Remove Secondary Axis in Excel

Here is how to remove the secondary axis in Excel Bar chart.

  • Step 1: First, right-click the secondary X-axis and then, click the Delete option in the context menu.

Otherwise, we can click or select the secondary X-axis and then, press the Backspace or Delete key on the keyboard.

How to Remove Secondary Axis in Excel - Step 1a

Now, the secondary axis in Excel Bar chart gets removed.

How to Remove Secondary Axis in Excel - Step 1b
  • Step 2: Next, right-click the secondary X-axis title X2 and then, click Delete in the context menu.
How to Remove Secondary Axis in Excel - Step 2a

It may appear that the plot does not show the X2 data series. But the legend shows that the plot contains the two data series. The reason is that the X2 data series values are smaller than the X4 data series values.

Furthermore, we can confirm by clicking the chart area to enable the Format tab in the ribbon and selecting the chart element Series “X2” in the Current Selection group.

How to Remove Secondary Axis in Excel - Step 2b

The above action will show the chosen data series in the chart area, as depicted below.

How to Remove Secondary Axis in Excel - Step 2c

And hence, the primary X-axis represents both the X2 and X4 data series.

Important Things To Note

  • Ensure the source data contains more than one data series to add a secondary axis in Excel chart.
  • Ensure that the primary and secondary axes’ data series have different chart types for more comprehensive and improved data analysis.
  • If the secondary X-axis option is unavailable, enable the secondary Y-axis to represent the data series. And then, select Design Add Chart Element AxesSecondary Horizontal to add the secondary X- axis. And then delete the secondary Y- axis.

Frequently Asked Questions (FAQs)

1. Why use a secondary axis in Excel?

We use a secondary axis in Excel for the following reasons:
The secondary axis enables us to consider additional outcomes from one chart and cover varied research requirements.
The secondary axis helps plot data points that vary from each other on a larger scale.
The secondary axis enables us to compare data series of varying types, helping in improving the data analysis.
The secondary axis helps achieve efficiency as we can use one chart to analyze the data of varying types instead of using multiple charts.

2. How to add secondary X axis in Excel?

We can add secondary X axis in Excel using the following steps. Let us see them with an illustration.

The table below contains three data series, Y, X- Primary, and X- Secondary, with the X- Primary and X- Secondary data series dependent on the Y data series.

Excel Secondary Axis - FAQ 2

Now, the requirement is to plot a Scatter chart with a secondary axis based on the above data. Then, the steps are as follows:

• Step 1: First, click on a cell in the source data range and then, select Insert Scatter (X, Y) or Bubble ChartScatter with Smooth Lines and Markers chart.

FAQ 2 - Step 1a

Then, click on the Scatter chart will give the below plot.

FAQ 2 - Step 1b

However, as the X- Primary and X- Secondary data series depend on the Y data series, we must swap the current X- and Y- axis.

• Step 2: Next, click the chart area to enable the Design tab in the ribbon and then, choose the Select Data option in the Design tab.

FAQ 2 - Step 2a

Next, the Select Data Source window opens. Now, we must select the X- Primary series and then, click Edit under the Legend Entries field.

FAQ 2 - Step 2b

Next, the Edit Series window will open.

Now, we must update the Series X values as the X- Primary data series range and the Series Y values as the Y data series range. Then, click OK.

FAQ 2 - Step 2c

Next, repeat the process for the X- Secondary data series. But the Series X values will be the X- Secondary data series range.

FAQ 2 - Step 2d

FAQ 2 - Step 2e

FAQ 2 - Step 2f

Then, click OK to close the Select Data Source window.

FAQ 2 - Step 2g

• Step 3: Next, click the chart area to enable the Format tab in the ribbon. Then, select the Series “X- Secondary” element in the Current Selection group.

FAQ 2 - Step 3a

The chosen data series gets highlighted in the chart area.

Next, press the keys Ctrl + 1 to open the corresponding Format Data Series pane and then, set the Plot Series On in the Series Options tab as Secondary Axis.

FAQ 2 - Step 3b

• Step 4: Now, click the chart area to enable the Design tab in the ribbon. And then, select Add Chart Element Axes Secondary Horizontal to insert the secondary X-axis.

FAQ 2 - Step 4a

FAQ 2 - Step 4b

• Step 5: Next, right-click the secondary Y-axis and then, select Delete in the context menu.

FAQ 2 - Step 5

• Step 6: Then, click the chart area to enable the Chart Elements option (‘+’ icon) and check the Axis Titles box.

FAQ 2 - Step 6a

Finally, double-click the chart and axes titles’ elements in the chart area, one by one, to update them and obtain the following chart containing the secondary horizontal or X- axis.

FAQ 2 - Step 6b

3. Why can’t I add a secondary axis in Excel?

We can’t add a secondary axis in Excel if we plot a chart based on the source data containing only one data series.
The secondary axis option will be available only if our chart contains more than one data series.

Download Template

This article must be helpful to understand the Secondary Axis In Excel, with its formula and examples. You can download the template here to use it instantly.

This has been a guide to Secondary Axis In Excel. Here we learn how to add secondary x-axis and y-axis, change & remove it along with examples & downloadable excel template. You can learn more from the following articles –