Combination charts can be used to highlight different types of information in a single chart. Using a combination of a column chart and a line chart can give a more specific visual representation of the data and help to make data easier to understand. Combination charts are particularly helpful when the values of the data vary greatly or when you are working with mixed types of data.
To create the combination chart, we will use the example of an athlete who is training for a triathalon. Each week this person spends several hours running, biking and swimming. We can create a chart that shows the number of hours spent on each activity as well as the total number of calories consumed each week.
This is the data as seen in Excel.
We will create a combination chart that shows a column for each training activity and a line for the total calories. To begin, we will select all of the data.
To create the initial column chart, on the Insert tab, in the Charts group, click Column, then click Clustered Column. The result shows us each activity, as well as the total calories, as columns. The activity hours are barely visible because they are much less than the total calories.
To begin changing the Total Calories Consumed data series to a line chart, select the Total Calories Consumed column.
On the Design contextual tab, in the Type group, click Change Chart Type. The Change Chart Type dialog box opens. Click Line with Markers. Click OK to close the dialog box and apply the change.
The chart now shows the Total Calories Consumed data as a line. These totals are much more than the activity totals, and the values on the vertical axis represent this. As a result, the activity totals are at the bottom of the chart with the Total Calories Consumed at the top. This problem can be remedied by creating a second vertical axis that shows the values associated with the activity hours.
To begin creating the secondary axis, select the Total Calories Consumed line.
Move to the Format contextual tab. In the Current Selection group, confirm that we are working with “Series: Total Calories Consumed.” Click Format Selection. The Format Data Series dialog box opens. Under Series Options, click the Secondary Axis radio button. To close the dialog box, click Close. The chart now shows the Total Calories Consumed on the secondary vertical axis and the activity hours on the primary axis.
It would be helpful to have axis titles. Without them, users may be unsure of which axis represents the line and which represents the columns.
To add axis titles, move to the Layout contextual tab. In the Labels group, click Axis Titles. Point to Primary Vertical Axis, click Rotated Title. The title appears on the chart, but currently reads, “Axis Title.” Since the text is selected, we can simply begin typing, “Activity Hours” and press Enter. The primary vertical axis title is now correct.
To add the title for the secondary vertical axis, follow the same steps. When we are finished, the chart looks like this: