In the previous lecture we took a look at method one where we use one chart to compare two different
categories with one another.
In this lecture we're going to take a look at using two charts. But the two charts are not going to be
showing the full values that we see here.
The first chart is going to show the actual sales
just like we see here. The second chart is going to show the deviation to previous year. And it's going
to show it in absolute values.
That's something we need to calculate first before we insert the chart
but we already have the information and the first one.
So let's just highlight this and insert a bar chart for the actual data. So we can quickly do some basic
formatting to this.
Let's reduce the chart size, make it thinner.
We can remove the axis labels, remove the grid lines, add data labels to these and make them a bit smaller
and also decrease the gap width. To bring up the properties,
I can double click on this element where I can use the shortcut key
Control+1.
Now I'm just going to drag the properties and bring it right beside the chart and reduce the gap width
to 40 percent.
Okay so now the second chart is supposed to show the variance to previous year.
That's something that I'm going to calculate right here.
The formula is very simple.
Actual minus previous year
and we just send the formula down.
Now I want to insert a second chart for this.
So if you have values that are not stuck together, you have to hold down the control key to select the
data separately.
So in this case I want app and I want the variance data but I don't want actual or previous year sales
data to be plotted.
Don't forget hold down the Control key.
Select the data then go to insert and insert the chart that you want. But I just want to show you something
else here.
If we want some of the formatting to remain so for example I change the gap with here.
If I want to keep that I also have the option to copy and paste the chart and change its source data.
I'm just going to click on the chart, Control+C, click on the side and Control+V.
Now I'm going to put the second chart beside the first one and update the source data.
Just right mouse click, go to select data, this time I want to edit the series, click on edit. Change
the series name to D28 and the series values to the variance numbers right here and click on ok.
The other thing I want to remove from this one are the labels here because I already have them
on this side, I don't need them here. If I just press delete, I delete the labels but I also delete my line.
You might be fine with that. In case you want to delete the labels but keep the line
there is a separate option for that. First activate your axis, go to the options right here. Under labels
for label position instead of next to axis, we want none.
So that keeps the line but hides the labels.
Now for the second chart I'm just gonna make it even thinner than the first one and I'm going to add
the data labels to it.
I'm also going to make the data labels smaller.
There is one more adjustment I want to make to this and that's the color of the series.
I would like to have the positive values formatted in a light green color and the negative values in
light red color.
The great thing about column charts and bar charts is that there is an inbuilt option for this. And that
option is in the color options right here.
First activate your series then go to the fill options, select "Invert
if negative". That allows you to select two different colors, one for a positive, one for negative.
The first option we see here is how we want the positive series to be formatted. I'm going to select
a light green color here.
Immediately I get a second option, that's for the negative series.
I'm going to go with a light red and that's pretty much it.
Now to make this look a bit better, make it look like it's one chart.
I'm going to take away that chart border. So activate the chart, go to format and under shape outline,
select no outline. That removes the border.
I'm going to do the same thing for the second chart. To make this even easier to read
I can activate the grid lines. But not the automatic default vertical grid lines.
We don't want those.
Instead we want the primary major horizontal grid lines.
I'm going to do the same thing for the variance side
and also make them less visible.
I'll just go with a very light gray color in both cases.
And to make them look like they are together let's just expand the plot area, so that's
the plot area in there.
This one is the chart area and I can control these separately.
I'm just going to expand the plot area a little bit to make the line look like it's going through.
As a last step, I'll group these together.
Click on the first chart,
Hold down Control, click on the second one.
You can right mouse click in group. Or, go to shape format and group the different objects together as
one object.
Now whenever you're moving this, just make sure you move the grouped object and not the individual objects
that are inside. All of this is also dynamic.
Let's test on WenCaL, actual is less than previous year.
Let's change that to 24,600.
Keep your eye on the variance here.
Keep your eye also on the value here.
Let's press enter, the value change to 400 and the series color changed to green.
Okay.
That's another method you can compare two separate charts that are grouped as one.