Sometimes while dealing with hierarchical data we need to combine two or more various chart types into a single chart for better visualization and analysis. This type of chart having multiple data sets is known as “Combination charts”.

In this article, we are going to see how to make combination charts from a set of two different charts in Excel using the example shown below.

Example: Consider a famous coaching institute that deals with both free content in their YouTube channel and also have their own paid online courses. There are broadly two categories of students in this institute :

1. The students who enrolled in the coaching but are learning from YouTube free video content.
2. The students who enrolled as well as bought paid online courses.

So, the institute asked their Sales Department to make a statistical chart about how many paid courses from a pool of courses which the institute deals with were sold from the year 2014 to the last year 2020 and also show the percentage of students who have enrolled in these paid courses.

Table :

Here, the first data is “Number of Paid courses sold” and the second one is “Percentage of Students enrolled”. Now our aim is to plot these two data in the same chart with different y-axis.

Implementation :

Follow the below steps to implement the same:

Step 1: Insert the data in the cells. After insertion, select the rows and columns by dragging the cursor.

Step 2: Now click on Insert Tab from the top of the Excel window and then select Insert Line or Area Chart. From the pop-down menu select the first “2-D Line”.

From the above chart we can observe that the second data line is almost invisible because of scaling. The present y-axis line is having much higher values and the percentage line will be having values lesser than 1 i.e. in decimal values. Hence, we need a secondary axis in order to plot the two lines in the same chart. In Excel, it is also known as clustering of two charts.

The steps to add a secondary axis are as follows :

1. Open the Chart Type dialog box

Select the Chart -> Design -> Change Chart Type

Another way is :

Select the Chart -> Right Click on it -> Change Chart Type

2. The Chart Type dialog box opens.  Now go to the “Combo” option and check the “Secondary Axis” box for the “Percentage of Students Enrolled” column. This will add the secondary axis in the original chart and will separate the two charts. This will result in better visualization for analysis purposes. 

The combination chart with two data sets is now ready. The secondary axis is for the “Percentage of Students Enrolled” column in the data set as discussed above.

 Now various formatting can be carried out in this secondary axis using the Format Axis window on the right corner of Excel.

Select the secondary Axis -> Right Click -> Format Axis -> Format Axis Dialog Box

Changing the Bounds of Secondary Axis

You can further format the above chart by making it more interactive by changing the “Chart Styles”, adding suitable “Axis Titles”, “Chart Title”, “Data Labels”, changing the “Chart Type” etc. It can be done using the “+” button in the top right corner of the Excel chart.

Finally, after all the modification, the chart with multiple data sets looks like :

We can infer from the above chart that in the year 2019, the percentage of students who enrolled in the online paid courses are relatively less but in 2020 more students have enrolled in paid courses than free content on YouTube.