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.
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.
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.
Table of contents
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:
- Simple To Add Secondary Axis
- 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:
- Create the required chart based on the source data containing multiple data series.
- 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.
- Click the Recommended Charts tab in the Change Chart Type window and choose the best suitable chart containing the secondary axis.
- 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:
- Create the required chart based on the source data containing multiple data series.
- 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.
- Press the keys Ctrl + 1 or right-click the chart area and choose Format Data Series in the context menu to access the correspondingpane.
- 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.
- 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.
- The All Charts tab in the Change Chart Type window opens, showing the current Combo chart preview.
- Select the required chart type using the secondary axis data series drop-down list.
- 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.
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.
Next, click the required chart type results in the following plot.
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.
- 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.
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.
- Step 4: Click the chart area to enable the Chart Elements option (+ icon) and check the Axis Titles box.
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.
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.
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.
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.
Next, set the chart element in the Current Selection group as the death rate data series.
We will see the selected data series highlighted or chosen in the chart area.
- 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.
Next, in the Format Data Series pane, set the Plot Series On setting in the Series Options tab as Secondary Axis.
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 Design → Change Chart Type to open the Change Chart Type window.
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.
Then, click OK will result in the following chart.
- 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.
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.
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.
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.
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.
The All Charts tab in the Change Chart Type window shows the current Combo chart.
- Step 2: Next, uncheck the Units Sold data series’ Secondary Axis box.
Then, check the Expenses ($) and Sales ($) data series’ Secondary Axis boxes, one by one, as shown below.
Step 3: Now, using the Units Sold data series drop-down chart list, select the 2-D Clustered Column chart.
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.
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.
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.
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.
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.
Now, the secondary axis in Excel Bar chart gets removed.
- Step 2: Next, right-click the secondary X-axis title X2 and then, click Delete in the context menu.
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.
The above action will show the chosen data series in the chart area, as depicted below.
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 → Axes → Secondary Horizontal to add the secondary X- axis. And then delete the secondary Y- axis.
Frequently Asked Questions (FAQs)
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.
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.
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 Chart → Scatter with Smooth Lines and Markers chart.
Then, click on the Scatter chart will give the below plot.
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.
Next, the Select Data Source window opens. Now, we must select the X- Primary series and then, click Edit under the Legend Entries field.
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.
Next, repeat the process for the X- Secondary data series. But the Series X values will be the X- Secondary data series range.
Then, click OK to close the Select Data Source window.
• 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.
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.
• 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.
• Step 5: Next, right-click the secondary Y-axis and then, select Delete in the context menu.
• Step 6: Then, click the chart area to enable the Chart Elements option (‘+’ icon) and check the Axis Titles box.
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.
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.
Recommended Articles
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 –