Welcome to part 3 in our series on Excel charts!
So far, we’ve looked at a couple ways to display data visually. Column charts are ideal for comparing different categories of data or showing changes over time. Pie charts work best if you need to show how individual amounts contribute to a total.
In this article, we’ll explore combo charts in Microsoft Excel.
A combo chart in Excel lets you display different categories of data in the same chart. Combo charts often display one set of data using columns while a second data set is plotted using a line. Rather than displaying data on two separate charts, they’re combined into a single combo chart.
Look at the data displayed in the screenshot below.
The first six rows of the worksheet show a summary of income from baked goods and drinks broken out by fiscal quarter. Rows eight through ten display a summary of customers by quarter.
Income and the number of customers are two different categories of data. Income represents the amount of money people spent while customer count summarizes the number of transactions.
Even though the categories are different it would be useful to see them plotted on the same chart.
Creating a combo chart is easy in the current version of Microsoft Excel. Let’s look at an example.
Creating a Combo Chart (Step by Step)
Step #1: Select the data you’d like to represent in your chart. In this example, I selected three ranges. First, I selected cells A2:E2 then, while holding CTRL on my keyboard, I selected A6:E6 and A10:E10.
Step #2: Click the Insert Column Chart button from the Insert > Charts group on the ribbon and select one of the available options. I clicked the second option: Clustered Column – Line on Secondary Axis.
Step #3: Move or resize the chart, if necessary. I moved my chart down to row 12.
Let’s take a closer look at this combo chart. Remember how I chose Clustered Column – Line on Secondary Axis? What does that mean exactly?
This combo chart is primarily a clustered column chart that represents the organizations total sales, broken down by fiscal quarter. The number of customers the organization had each quarter is superimposed onto the chart and represented by an orange line.
The axis on the left represents total sales for each quarter (in dollars) while the secondary axis on the right, represents the number of customers. The scale of each axis is adjusted so the chart is easily viewable.
Customizing a Combo Chart
I’m fairly happy with the appearance of the chart but I would likely make two changes. For this chart, I would:
- Add a title.
- Change the label in cell A10 to make it easier to interpret.
Here are the results.
If you wanted to further refine the appearance of the combo chart, you could utilize the Add Chart Element or Quick Layout Gallery controls. Both are found on the ribbon in the Chart Layouts group on the Chart Design tab.
Important: You always need to select the chart before making changes. All of the controls related to updating or formatting the chart are “contextual”. They only appear when the chart – or a specific chart element – is selected.
The Quick Layout Gallery
The Quick Layout gallery lets you modify several chart attributes at the same time.
In the screenshot below, I selected the chart then applied the settings in Layout 1. Layout 1 retains the title but moves the legend to the right of the chart.
Layout 5 (below) retains all of the features from the previous step but adds a primary axis title to the chart, along with a data table.
In my example, I added US Dollars to the primary vertical axis title. The data table enhances the presentation of the chart by showing specific dollar amounts and customer counts under the labels for each fiscal quarter.
Add Chart Element
You can add, remove, or modify individual properties using the Add Chart Element control.
For example, I can add Primary Minor Horizontal gridlines to the chart to enable a more precise interpretation of each column in the chart (columns represent income).
Chart Styles let you update the appearance of your chart by changing multiple formatting attributes at once. I recommend that you experiment with the various settings available in the Chart Styles gallery.
The Format Tab
Buttons on the Ribbon’s Format tab let you change one formatting attribute at a time.
In the screenshot below, I applied a yellow colour to the chart area and added a shadow effect to each data series to provide some visual interest.
Combo charts are a great way to visually represent different categories of data together that would otherwise need to be displayed on two charts. They allow you to analyze relationships between numbers that might otherwise be unrelated.