Combination Charts in Excel
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. These are known as “Combination charts” in Excel.
In this article, we are going to see how to make combination charts from a set of two different charts in Excel using an 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 :
- The students who enrolled but are learning from YouTube free video content.
- The students who enrolled in paid online video lectures.
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 sold from the year 2014 to the last year 2020 and also show the percentage of students who have enrolled in the online paid courses only.
Table :
| Course Enrollment Stats | ||
|---|---|---|
| Year | Number of Paid Courses sold | Percentage of students enrolled |
| 2014 | 10 | 30% |
| 2015 | 15 | 25% |
| 2016 | 20 | 30% |
| 2017 | 20 | 50% |
| 2018 | 25 | 45% |
| 2019 | 15 | 20% |
| 2020 | 30 | 70% |
Implementation :
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 Column or Bar Chart. From the pop-down menu select the first “2-D Column”.
Inserting Bar Chart
Bar Chart
Step 3: To add combination chart :
Select the Chart -> Design -> Change Chart Type.
Another way is to right-click on the chart and select “Chart Type”.
Step 4: The Chart Type window 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.
Adding Combo
Combo chart with secondary axis
The combination charts are ready. The secondary axis is for the “Percentage of Students Enrolled”. The modifications can be performed in this secondary axis using the Format Axis window on the right corner of Excel.
Step 5: You can format the above combo chart by adding various “Chart Styles”, suitable “Axis Titles” in all three axes. Refer to the image shown below :
Updating and adding the axes title
Now, we can also change the type of the second chart. For that right-click on the chart and open the “Chart Type” window. In our case, we have selected Stacked Line with markers for the Percentage of Students enrolled line chart.
Changing the chart type
Final combination charts
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.
Please Login to comment...