Excel Add Secondary Axis (Table of Contents)
Introduction to Excel Secondary Axis
Secondary Axis is an advanced visualization technique used in analytics. We know about basic charts which have the implementation of a single axis. But what if we need two axes i.e adding a second data set on the existing chart. In that case, we’ll need a secondary axis. Basically, it is the first step in advanced analytics. When we have two data sets to show in a single chart, then we can add a secondary axis. This can be done in many tools and MS Excel as well. Excel has some basic and advanced graphical techniques which allow analysts to generate better insights from the graph. Adding a secondary axis is one of those advanced techniques in analytics.
Example of Secondary Axis in Excel
Below is an example that shows how the secondary axis works in Excel. For that, we need a tabular data.
This data has two datasets I,e Sales and Target Achieved %. When a secondary axis is added, the graph will look like this as shown below.
Sales amounts are shown in blue bars whereas Targets are shown in the orange line represented in the vertical axis as shown above. Both the data sets are on Y-axis but the second data set is added as a secondary axis to the same chart.
Explanation:
While creating a secondary axis, the result is both axes vertically shown on the same graph. This is the most advanced technique used in analytics to show two data sets in the same graph. If both the data sets are important to show, then we can use a secondary axis for the same. This type of technique is often used in the financial model where we need a clear picture of the data.
Representation of the two data sets in a single chart usually helps in better visualization from a reader as well as an analyst’s point of view. Below are the steps to add a secondary axis on an existing chart.
4.9 (2,356 ratings)
View Course
Steps to Add Secondary Axis in Excel
We’ll use the above table to demonstrate the addition of a secondary axis in the chart.
The data has “Month” in the first column, “Sales” and “Sales percentage” in the second and third column respectively. The secondary axis is based on a column chart. There are some steps to add a secondary axis.
- The first step is to select directly the graph type from Insert > Combo and select the “Clustered column-Line on secondary axis” as shown below.
Now the chart can be seen with two axes as per the below image.
- The second method is using a column chart. To show the sales in a graphical form, we need to plot a column chart first.
This chart is not clear as it is not showing the sales percentage clearly. So to show it in a better way, we can add another vertical axis. But this can be done by changing the chart type by going to Design > Change chart type as shown below.
A dialog box will open like this where we have to go to “All charts” as shown below.
After the window opens, we need to select the first clustered column chart.
Now, the result is a secondary axis added to the chart.
Target achieved in percentage can be clearly seen now in a second vertical axis in orange color. March has the lowest sales percentage whereas May has the highest percentage as shown in the chart.
- The second step is a manual method. Initially, the column chart needs to be created first.
Next, we have to select the chart and click on the Format Tab as shown below.
The from the drop-down menu in “Current selection”, we have to select “Series Target Achieved %”.
The goal fixed can be identified in column bars as shown below.
Now, we’ll press Ctrl+1 to open format data series options.
Now, we’ll have to select the “Secondary Axis” from the options and the chart will look like this as shown below. We are basically switching over from the Primary Y-Axis to Secondary Y-Axis here.
Now the chart shows Target achieved % in a column bar with a secondary axis. But it is a bit complicated to read enough. So, we can change the chart type by going to Design > Change chart type.
From the options, we have to go to “Combo” and change the “Target achieved %” to “Line” instead of “Clustered”.
Now the result is a chart with dual-axis as shown below.
In this way, we can add a secondary axis to an existing chart. The above steps are a bit different but the output remains the same in all the steps. It helps in analyzing multiple data sets in an easier way. Apart from Excel, the Secondary axis can be added in Google sheets, Power BI, Tableau, etc. But plotting the same in Excel is comparatively easy than other visualization tools. Comparing the “Sales” is easier in the column chart but with this technique, we can compare profit margins from different months.
Things to Remember
- The secondary axis requires a different chart from the primary axis chart.
- This chart type is a clustered combo chart.
- Secondary Axis values can be viewed from the right side vertical axis.
- Primary Axis values can be viewed from the left side vertical axis.
- Both the data sets are needed to have two-axis in a single chart.
- The chart is based on a column bar chart.
- Two data sets mean two columns from a single table.
Recommended Articles
This has been a guide to Add a Secondary Axis in Excel. Here we discuss How to Add a Secondary Axis in Excel along with practical examples and downloadable excel template. You can also go through our other suggested articles –
