Hi, my name is Jason Morrell, and in this video I'm going to show you how to produce
a two axis chart, sometimes called a combo chart.
If we look at this chart here, the majority of the data is depicted as a clustered column
and it uses this axis up the side here.
One series though, is depicted as a line and it uses a completely different axis on the
right-hand side there.
They're showing percentages and obviously the percentage here uses a completely different
scale to the primary axis here.
Keep watching and I'm going to show you how to produce this kind of chart.
OK, so here's the data that was used to produce the chart you just saw.
In the first column, we have a number of products listed across the top.
We have the years going from 2014 to 2018.
In column G, it totals up the figures for each product.
Then in column H, it shows you the percentage of each product against the total.
You can produce a combo chart really quickly.
Let me show you this method first.
So I'm going to select all of the headings across to the 2018 column and all the products.
I'm also going to select the data in column H. In other words, I'm excluding the Total
because if we included that, it would throw the scale and all the data out on the chart
because these are such big values.
I then go to the insert tab.
Come to the Recommended Charts, All Charts tab, and down the bottom left you'll see Combo.
There are four Combo options at the top here.
I'm going to stick with the default setting.
Down the bottom here, you'll have one option for each series.
So we're looking at each year going downwards here and against each year it shows the type
of chart that's going to be plotted.
You'll see the first three years are showing as Clustered Column, which is fine, but we
also want 2017 and 2018 to be Clustered Column as well.
So let's just change those over.
The last series though, the percentage, we actually want to depict as a line.
Not only that, but we want to plot this percentage on the secondary axis so it has its own scale
going up the right hand side.
So, primary axis on the left, secondary axis on the right.
And when we click OK, there's our Combo Chart and it's pretty much OK as it is.
Let me show a couple quick things you can do this.
I'm going to move this onto its own sheet, just call this 'Combo'.
And the first thing I do, because the chart basically is OK, is to smooth out the line.
OK, at the moment it just goes jagged, so I'm going to put the arrow head on the line
here and double click it and it should open up directly into the Format Data Series and
it's showing the various series options here.
If it doesn't, for whatever reason, sometimes it comes into other places, all you've got
to do is to choose the drop down arrow here and then choose Series Percentage down here.
Now you'll see the secondary axis is already selected and that's why it shows the axis
up the right hand side there and that's because we chose it when we set up the chart in the
first place.
We come from the Series Options to the Fill and Line Options and the very last option
under the Line Settings is the Smoothed Line.
And as soon as you do that it smooths out all the jagged parts of the line there and
it presents a lot better.
Another thing that's worth adding to this line is the data labels.
Now there's a couple of ways you can do that, but the easiest is to go to the plus sign
here, go to your Data Label options here, and you can choose where you want to put them.
So Inside End, Inside the Base, Outside End, Data Callout.
Now at the moment it's doing the data labels for everything on the chart.
So what I need to do first is to select just the line, so that that has the focus.
And then when we come to the data labels, it's only going to do the data labels for
that particular series.
OK, so let's just choose any one of those and we should be fine.
So now it's a much more meaningful chart because it's got data points along the line.
You'll notice that the majority of the data on the chart stops around here.
So there's a segment at the top there which is not being used.
It's kind of wasted space.
So what you can do is to change the scale of either the columns or the line or both,
you change the scale to stop at, say, thirty five thousand instead of forty thousand.
And you stop it at, say, sixteen percent rather than eighteen percent.
So to do this you just double click on one of the labels on one of the axes and again
on the panel on the right hand side there, it shows the Format Axis options.
If we come across to this option here called Axis Options and then open up Axis Options.
Under Bounds you'll see Minimum and Maximum.
So the lowest and highest value on the axis.
If we just change this down to thirty five thousand, that's that axis done, and you can
see the columns are now kind of utilizing the full height at the chart.
If we do the same for the line, you may or may not want to change that, but if you did,
you'd double click on one of the numbers on the secondary axis.
And again, it shows over here under Bounds, the Minimum and Maximum.
So if we reduce this point one eight down to point one six, that's sixteen percent,
and straightaway the line moves up.
It doesn't really matter too much where you put the line, as long as the information it's
displaying is clear.
So that's the first method of creating a Combo Chart.
Now the second method, this is actually what a lot of people use because they're old-school
is to create a column chart and then adapt it from there.
It's a bit more work, but here's the process.
So if you go back to our original data here, we've still got the original range selected.
This time I'm going to go to the Insert tab, go to the Clustered Column or Column Chart
and then choose Clustered Column.
And again, here's the starting point.
Let me put that on its own chart as well.
So every series in the chart is showing as a clustered column.
If you look at the legend down the bottom, you've got the years going two thousand and
fourteen to two thousand and eighteen.
That's the bit that needs to be a clustered column.
And then we got our last entry there called Percentage, which is green, and you can't
actually see on the chart - there's a space for it on each one, but it's so small in number
that actually kind of flat lines against the axis.
So you can't actually see it here.
What you do to convert that one series to a line is this.
If you click once on the legend, it selects the whole legend.
Click once again on the percentage it highlights just that percentage.
If you right-click on Percentage here, then choose Change The Series Chart Type.
It opens up in the Change Chart Type dialog box which shows all the types of chart down
the left-hand side.
Just notice how it's now come in on Combo, but the option its chosen is this Custom setting,
it's a custom combination.
And if you look down at the settings at the bottom, you can see that Clustered Column
Column is the default option for every single data series on this chart.
So all we need to do here is to change that last one from Clustered Column to Line or
choose one of the other types if you want the markers, that kind of thing.
And again, secondary axis, if you want to switch that on, you'll add the secondary axis
here.
And when you click OK, it makes that change to the chart and the line is actually a completely
separate series that superimposes over the columns that represents those percentages
on the right.
So that's just a quick video showing you how to create a two axis chart or a combo chart,
two different methods.
The quickest way is just to create a combo chart out of the gates, but if you make a
mistake or you start with a column chart and then adapt it, that's pretty easy too.
I hope this video's been useful.
Let me know any questions you have when any comments you have.
Otherwise I'll see you for the next one.