[country themed introduction music]
(male narrator) Hello in this screencast we'll take
a detail look at clustered columns type of graph.
Let's say you have this data set here Widgets Inc. and you have a bunch of data
about sales of the various different regions and across different years
and I also have done subtotals here by region and by year,
and let's say we want to create a simple clustered column graph for this data.
First you select the data set, insert a column I just choose a plain 2-D column,
and that's your simple graph right there but you can see that it's got some
unnecessary data that probably does not belong in this graph
for example it has totals by year and it also has totals by region.
So how do we remove this data?
Okay so let's first, select this graph just make sure that it's highlighted.
When you do then you will see a design tab here with all this information
now click on select data.
When you click this button you will see a pop up box appear here
and what you can do here is if you see a column here that doesn't belong
for example, totals by year, you can just select it and click remove.
Now then totals by year goes away and you just have the North, South, East, and West
represented in your graph and you can see that your graph is slightly changed
to reflect that.
Supposing you want to remove totals by region.
What you can do is first switch row and column and now you have totals by region
as one of the rows and you can just click remove to take that out
and now you just have the different years
and if you want to can get back to your original configuration so that you have...
let's say each column be a year and within each column
the height represents the total sales and within each year you have
four different sub columns, each for a different region.
And if you want you can interchange this so that you have instead of 2009-2010.
You have North, South, East, and West appear here
so this will be North, South, East, and West
and instead of the years appearing here you can have each of these columns
be a year.
To do that again select the graph, make that active,
click on switch row and column so as soon as you do it your rows and columns
are interchanged.
So that's a quick and simple way to create a simple bar chart with clustered columns.
What if you wanted to have a slightly different style?
Instead of clustered columns maybe you want to have all the columns stacked up
one on top of another.
It's simple to do that.
Just click on change chart type and then you will be presented
with all the different options and you can just select a stacked column, click OK,
and that will result in this kind of a stacked column.
And now you can actually see what's the total sales in each year.
So you can see that or in this case in each zone you can see that
East has the highest sales and within each zone you have the sales
broken up by the year.
Or you can switch rows and columns and now you have the total sales by each year
and for each column you have it broken down by sales by region.
So that's how you can change the chart type on-the-fly.
Another neat thing you can do is to create Sparklines.
Ok so let me just actually delete this for now.
And what you can do is create Sparklines
I will show you what I mean by that.
So let's just create a new column called trends and let's say you want to create
a trend for each of these columns.
Okay, so one way to do that is to click on the cell H3 here and go on to insert
and in this Sparklines lines box here just click on line
and it asks for a data range.
So you can just click on this data range right here, click OK,
and location range would be this one right here, and you click OK, and you will see
a line here that represents a trend for all these four data points.
Now if you are obviously you don't want to just draw a line, a graph,
for just this one row you want to do it for the entire region
and to do that what you do is,
let me just edit this here, you can edit the data and instead of saying data range
is just C3 through F3 I can make the data range be this entire region
and instead of saying the location is just this, H3, you can see the location
is all four cells here and you click OK and now you have four different lines.
Each of these lines represents the trend of sales in this region right here.
So that's a Sparkline.
You can make the Sparkline line a little bit more informative by changing
its format a little bit.
So you can go to design, just select the Sparklines, all of them,
and then click design and you can say you want to highlight the high point.
If you do that whichever is the highest point in this graph will be highlighted
for somebody that's looking at it, it'll be easy to see in which year
the highest sales happen and you can change the style of your Sparklines
to something more appealing, say something like this for example.
If you want you can also change, you know, put a color to the markers if you do this
then you'll have markers for each of the different points and so on.
So these are all some neat ways in which you can display data.
So that's all for now I hope you enjoyed this thanks for watching.
[country themes music]