Learn to effectively present diverse data by creating professional combo charts in Excel. Understand how adding a secondary axis enhances clarity and data comparison significantly.
Key Insights
- Create combo charts by selecting relevant data, navigating to the Insert tab, choosing Combo Chart, and combining chart types such as clustered columns and lines to visually represent different datasets effectively.
- Improve the readability and accuracy of combo charts by incorporating a secondary axis, allowing independent comparison of values with vastly different ranges, such as units sold (lowest at 800) versus price per unit (highest at 105).
- Format combo charts by customizing visual elements, including selecting gradient fill colors (gold accent 4), adjusting background color (white background darker 25%), altering chart borders, and applying shape effects (soft round bevel) for a polished presentation.
Combo charts. Combo charts combine two or more charts into a single chart with the option of adding a secondary axis. So let's take a look at exercise one.
We're going to create a combo chart using the information that relates to revenue and growth for years 2014 to 2017. Creating the chart is pretty simple. You'll head over to the insert tab, then you'll go over to the charts group and you're going to look for a combo chart.
Now you may not recognize the icon instantly, but if you hover your mouse over the different icons that are available in the charts group, a little pop-up will come up and tell you the different types of charts you are hovering over. This is the icon for a combo chart, so I'll click on it and let's just choose the very first one. There we go and this is our combo chart.
So the point of this is just is to just take a look at how the information is being displayed. It's not really a good chart because we can't necessarily tell the relationship between the percentage of growth and the revenue and that's because the values are so far apart. So let's see if we can improve upon that by going to exercise two.
I'm going to click on an individual cell within that data. I'll go to insert. Now I know the icon for a combo chart.
I'm going to click on it and choose the very first one. Now there's an improvement here. The price per unit, which takes the place of percentage of growth, is not flat flatlining at the bottom as much, but still we can improve this.
We want to modify the chart as follows. If you want to make changes to the chart, just click change chart type. Now units sold is a clustered column.
I'm confirming that price per unit is a line. That's also the case, but we're being told here to add a secondary access, something we didn't do in the last chart. So I'll click the check box for secondary access.
That makes a big difference. Why? Because the values are now being compared against each other independently. The lowest value for units sold is $800.
The highest value for price per unit is only $105. So you wouldn't be able to compare them fairly against each other because of the disparity in the values. But if you put the values on a separate axis, then you can see that the values compare favorably.
You can make comparisons and the highs and lows are not being calculated based on the highs and lows of either access. So what else is there to do? We'll do some formatting. If I scroll down, we have very specific instructions as it relates to formatting this combo chart.
Let's start with the color of the bars. I'll click on any individual bar and we'll head over to the format tab. That's where we'll get our options for formatting.
First step, let's go to shape styles. And what I want to choose is gradient fill gold accent for no outline. That changes the colors of the bars.
Now I'm going to click in the background of the chart and I want to change the background color. So I'll go to shape fill. And what I'm looking for is white background, darker, 25%.
You'll look for that exact phrasing when you hover your mouse over the color boxes. I'll click that. There we go.
I want to change the color of the chart's border. So I'll go to shape outline and I want to choose gold accent for. And finally, I want to add a shape effect.
So I'll choose shape effects. I'm going to go to bevel and I'm looking for soft round. Again, you'll hover your mouse over the options until you find soft round.
One click. There we go. So that completes the formatting of our chart.
And that also completes this exercise. So in this section, we looked at how you could create a combo chart and how you can use a secondary access to compare the values against each other favorably and not have them skew one way or the other. And we also looked at how you can format your combo chart once it's been created.