Now let's take a look at creating combination charts in Excel.
By combination charts, I mean charts that include different series types in one graph.
Based on this dataset where we have information on the different apps for productivity division,
actual and previous year sales. What we've already done is to insert a clustered column chart or a bar chart.
And let's say we do this, we show this to our boss and our boss takes a look and says: Well, could
you change the series type for previous year to be a line instead of a column?
How would you do this?
Here's something you need to be aware of.
And that's where you right mouse click on this chart.
If you click here in the plot area and you right mouse click, you see the option change chart type.
If I select this, I get to this view.
If I go to line because I want previous year to be aligned, I see both series change to a line.
So changing the chart type changes the type of each single series in that chart.
That's not something I want. I want to change one of them.
The view I actually need is the Combo view I see on the bottom. And the moment I select this I
can see that previous year change to a line because that's the standard for combo view.
I'm going to show you something that's going to take you here directly.
I'm going to click on cancel now. Instead of clicking here and selecting change chart type, you right
mouse click on any of these series.
And if you're in Excel 2016 and above, it doesn't matter which series you select. Because the moment you
right mouse click and you say change series chart type, so notice that we have a different option now.
It's not chain chart type.
It's change series chart type.
We automatically are taken to combo view and Excel doesn't try to decide what we want.
We can actually decide the type of series
we want to have here.
So that's the fastest way of getting there is to select any of these series.
Even if I select actual and I right mouse click here and go to change series chart type, I still come
to this combo view. And now I can decide based on these drop downs what type of series I want to have.
So for previous year, we can go with a line or in this case let's go with option "Line with Markers".
And then click on OK.
We can make the usual adjustments to this. Let's bring up the options box here by pressing Control+1.
For the legend position.
I'm going to put it to top.
I'm just going to fade out the gray color here a bit more. Under the line and fill options
I'm going to go with a lighter gray for those grid lines. And we can also reduce the gap width for our
series here.
Let's just bring it all the way down.
Let's say your boss takes a look at this and says that doesn't look so great.
What if we take away the line and just show the previous year values as markers?
How would you do this?
You have different options here.
You could do it quickly by first activating the series. You just have to click on it then go to format
here, under shape outline, select no outline.
Another option is to bring up the chart options on the side here, go to fill and line options and select
no line from here.
Now while we're in this view, we can also change the marker. If we're not happy with this round marker
we can click on this marker icon here. Under marker option, select another type of built in option.
So when I click this dropdown arrow here, you can see you have a few more options.
You can also bring in your own image or icon from a file.
It has to be small if you want to use it as an icon here or you can just make a selection from an existing
one.
Let's go with this line here and increase the size so it fits in a little bit better.
But notice that every time you increase the size it gets longer
but it also gets thicker.
So that's one way of showing actual and previous year as different series.
Now I'm going to show you a trick here because you can get really creative with the type of markers
you can use.
You're not just restricted to what you saw there.
You can actually create your own or import your own here and copy and paste the marker in your chart.
For example, let's say you have a really picky boss and they tell you
well, I want this longer but not this thick.
Here's what you can do.
Go to insert, go to shapes and let's just draw out this line here.
Let's change the color and change the thickness. A bit thicker
but not as thick as what we currently have.
Let's just make this even a bit thinner. Once you're happy with the type of marker you want to use.
Click on it.
Press Control+C
Basically copy to clipboard, click on your existing markers to activate the entire series and press
Control+V.
That paste that image in your chart. So that basically becomes your new marker.
Note If this is too long and you want to change it, you can change it here so I'm just going to make
it a little bit thinner.
Notice it's not a live image, right?
If I change the way this image looks, I have to press Control+C again, click on the series and
press Control+V. Here obviously you can get creative with the type of markers you want to use.
So let's just do that.
Let's just have some fun and insert a smiley here.
I'm going to change the color to green, just to show you the concept.
Obviously we're not going to be using this in our final report. But to get this in I'm going to copy
this image click on the markers here and press Control+V.
Notice that the marker here in the legend also changed to a Smiley. And if you're wondering whether these
are dynamic or not, they are. Let's change the marker for Dasring. Instead of 5,500
let's go with 10,000.
And that jumped.
OK so that's one way you can get this done. I'm just going to press Control+z to go back to our more serious
report.
Basically that's how you can create combination charts in Excel.
Just remember right mouse click on any series to go to change series chart type and go to combo view.