Combo Charts

Charts are covered extensively in our Excel classes in NYC. For those located outside New York, find and compare the top in-person Excel classes or online Excel training.

Exercise 1

Let's take a look at Exercise 1. We're going to create a combo chart using the information that relates to revenue and growth for years 2014 to 20 17.

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 combo charts so I'll click on it, and let's just choose the very first one. There we go. This is our combo chart.

So the point of this 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.

Exercise 2

Let's see if we can improve upon that by going to Exercise 2. I'm going to click on an individual cell within that data, and I'll go to insert. Now I know the icon for 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 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 axis, something we didn't do in the last chart. So I'll click the checkbox for secondary axis. 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 axis.

Formatting a Combo Chart

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 shape styles. What I want to choose is "Gradient Fill—Gold, Accent 4, 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. Now what I'm looking for is "White, Background 1, 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 4." 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. That completes the formatting of our chart.

Recap

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 axis to compare the values against each other favorably and not have them skew one way or the other. We also looked at how you can format your combo chart once it's been created.