Now let's take a look at how we can add more series to an existing chart for comparison charts.
What I mean by comparison charts are cases where we want to compare one category with another.
For example, actual sales to budget or actual to previous year data. We're going to be taking a look
at two different methods.
In this lecture, our method one is to take a look at this in one chart.
We're going to show the two different categories in two different series in the same chart. And method
two is to create two separate charts.
That's something we're going to cover in the next lecture. One chart is going to show the actual sales
and the other chart is going to show the variance to previous year. And we're going to be grouping these
two charts together.
So they look like they're one chart. Let's start off with a simpler method to show these two categories
in one chart.
First off, let's say I just have information on app and actual sales.
I don't have this information yet so I'm just going to plot a chart based on this, highlight the data
set, go to insert, go to the charts part and select this time a clustered bar chart.
I'll just bring it over here and make it a bit thinner which is ultimately going to depend on the size
I have available in my report.
Let's just delete the vertical grid lines, right mouse click, add data labels and let's also reduce the
gap width. Double click the bars to bring up the options or I can use the shortcut key Control+1.
I'm just going to drag this and put it right beside the chart, for gap width reduce this to 40 percent.
.
Also for the labels let's make them a bit smaller.
Just go to the Home tab and let's reduce the font size
and I can remove my horizontal axis.
You might notice that when you create a bar chart, the categories are kind of reversed.
So blend here is on the top of my data.
It ends up being on the bottom of my bar chart. WenCal is on the bottom of my data set, it ends up being
on top.
You might want to reverse this. To do that just activate the series. Click on it, go to the axis
options right here and here you just have to put a checkmark for categories in reverse order.
That switches things around and it puts blend on top.
Okay.
So we created this chart.
Now we take it over to our boss and let's say
He says, Could you add in the previous year data.
So we get the previous year data right here.
Now let me show you three different ways of adding this to an existing chart.
First method, just right mouse click on your chart,
select Data. On the left hand side you can see the series that are plotted.
That's my actual cells.
If I wanted to edit it, I just have to click on it, click on edit and I can see the different elements.
So this is basically the title of my series which is called actual sales and my series values are the
values right here. On the right hand side,
I can see the category labels. Now I don't need to edit this or remove. I want to add a new series so
I'm going to click on Add. For series name,
Select the header here, previous year's sales and for series values,
I have a default in there that I need to remove first.
Just press backspace to remove it or directly highlight the range that you want included.
And that's going to add it to your chart and then click on ok
and ok
and you'd be done.
Now I want to show you another way of doing this.
I'm actually going to select the series and remove it from here and click on Ok. A second way of adding
a series to your chart is to highlight the data and copy it,
click on your chart,
Go to Home, paste, and paste the values in your chart.
Now let me show you a third way of doing this.
I'm going to press Control+z to go back.
Let's now assume the dataset is sitting right beside the other data.
So I'm just going to pull this over here.
If your setup is like this, all you have to do is to activate your chart. So just click on it and then
expand the data set
and that adds the series to the chart
and let's just tweak one thing.
Let's add a little bit of distance between the two bars because now they're stuck together. To do that
We need to change not the gap width but the series overlap.
When the overlap is zero it means they're stuck together.
And if we put something like minus 10, that adds a little bit of a gap between the two series.
I'm just going to expand this to make the values easier to read.
Some information is missing from here. We don't have a chart title so we can add elements very easy by
just by clicking the plus Icon here.
Let's add a chart title to this.
We also don't have a legend.
Let's go back here.
Add a tick mark for the legend.
I don't really like it here.
I prefer the legend to always be under the title.
Just click on it to activate the options here.
Under the main legend options.
Let's switch it to the top.
Okay.
And then the last type of formatting is to change the color of the series as you see fit. But I'm actually
going to do one last thing and that's to sort the data based on actuals.
The app with the highest sales is going to be first, and then the second highest and so on.
All I have to do is to sort my data sets.
I'm just going to right mouse click somewhere in the actual sales column.
Select sort, sort largest to smallest.
That sorts the data based on the actual values
but obviously this is static.
So every time you get a new data, you need to resort these again. If you want them to be sorted automatically
you can do that if you use formulas.
Something we cover in the more advanced charting courses.
Okay.
So that was Method 1, comparing two different categories in one chart.
In the next lecture let's take a look at method two, where we're going to create two charts that are grouped
together as one.