WEBVTT

00:00:00.280 --> 00:00:07.746
[country themed introduction music]

00:00:07.746 --> 00:00:10.151
(male narrator)
Hello in this screencast we'll take

00:00:10.151 --> 00:00:14.145
a detail look at clustered 
columns type of graph.

00:00:14.599 --> 00:00:19.662
Let's say you have this data set here 
Widgets Inc. and you have a bunch of data

00:00:19.662 --> 00:00:24.642
about sales of the various different
regions and across different years

00:00:25.255 --> 00:00:28.834
and I also have done subtotals 
here by region and by year,

00:00:29.422 --> 00:00:35.927
and let's say we want to create a simple
clustered column graph for this data.

00:00:36.321 --> 00:00:45.107
First you select the data set, insert a
column I just choose a plain 2-D column,

00:00:45.107 --> 00:00:50.732
and that's your simple graph right 
there but you can see that it's got some

00:00:50.732 --> 00:00:54.248
unnecessary data that probably 
does not belong in this graph

00:00:54.248 --> 00:00:59.907
for example it has totals by year 
and it also has totals by region.

00:01:02.054 --> 00:01:03.725
So how do we remove this data?

00:01:03.952 --> 00:01:10.140
Okay so let's first, select this graph
just make sure that it's highlighted.

00:01:10.795 --> 00:01:14.317
When you do then you will see a 
design tab here with all this information

00:01:14.717 --> 00:01:16.574
now click on select data.

00:01:17.288 --> 00:01:21.314
When you click this button you 
will see a pop up box appear here

00:01:21.614 --> 00:01:26.659
and what you can do here is if you
see a column here that doesn't belong

00:01:26.659 --> 00:01:30.839
for example, totals by year, you 
can just select it and click remove.

00:01:31.234 --> 00:01:36.647
Now then totals by year goes away and you
just have the North, South, East, and West

00:01:36.647 --> 00:01:40.447
represented in your graph and you can
see that your graph is slightly changed

00:01:40.447 --> 00:01:41.579
to reflect that.

00:01:42.529 --> 00:01:44.763
Supposing you want to 
remove totals by region.

00:01:44.946 --> 00:01:50.196
What you can do is first switch row and 
column and now you have totals by region

00:01:50.196 --> 00:01:55.697
as one of the rows and you can 
just click remove to take that out

00:01:55.916 --> 00:01:57.942
and now you just have the different years

00:01:59.139 --> 00:02:04.561
and if you want to can get back to your
original configuration so that you have...

00:02:08.421 --> 00:02:13.381
let's say each column be a 
year and within each column

00:02:13.381 --> 00:02:17.553
the height represents the total sales
and within each year you have

00:02:18.471 --> 00:02:21.338
four different sub columns, 
each for a different region.

00:02:22.138 --> 00:02:26.739
And if you want you can interchange this
so that you have instead of 2009-2010.

00:02:26.988 --> 00:02:29.639
You have North, South, 
East, and West appear here

00:02:30.013 --> 00:02:32.342
so this will be North, 
South, East, and West

00:02:32.342 --> 00:02:35.892
and instead of the years appearing here
you can have each of these columns

00:02:35.892 --> 00:02:36.962
be a year.

00:02:37.541 --> 00:02:40.931
To do that again select 
the graph, make that active,

00:02:42.238 --> 00:02:48.582
click on switch row and column so as soon
as you do it your rows and columns

00:02:48.582 --> 00:02:49.798
are interchanged.

00:02:50.981 --> 00:02:56.131
So that's a quick and simple way to create
a simple bar chart with clustered columns.

00:02:57.264 --> 00:03:00.139
What if you wanted to have 
a slightly different style?

00:03:00.139 --> 00:03:04.970
Instead of clustered columns maybe you
want to have all the columns stacked up

00:03:04.970 --> 00:03:06.276
one on top of another.

00:03:06.492 --> 00:03:07.775
It's simple to do that.

00:03:07.952 --> 00:03:12.050
Just click on change chart type 
and then you will be presented

00:03:12.050 --> 00:03:17.267
with all the different options and you can
just select a stacked column, click OK,

00:03:17.443 --> 00:03:20.068
and that will result in this 
kind of a stacked column.

00:03:20.068 --> 00:03:26.257
And now you can actually see 
what's the total sales in each year.

00:03:26.543 --> 00:03:30.503
So you can see that or in this 
case in each zone you can see that

00:03:30.630 --> 00:03:36.384
East has the highest sales and 
within each zone you have the sales

00:03:36.384 --> 00:03:37.944
broken up by the year.

00:03:38.259 --> 00:03:43.209
Or you can switch rows and columns and 
now you have the total sales by each year

00:03:43.209 --> 00:03:47.486
and for each column you have it 
broken down by sales by region.

00:03:48.803 --> 00:03:52.036
So that's how you can 
change the chart type on-the-fly.

00:03:55.005 --> 00:03:59.751
Another neat thing you can 
do is to create Sparklines.

00:03:59.751 --> 00:04:02.746
Ok so let me just 
actually delete this for now.

00:04:02.746 --> 00:04:05.176
And what you can do is create Sparklines

00:04:07.626 --> 00:04:09.406
I will show you what I mean by that.

00:04:09.406 --> 00:04:15.055
So let's just create a new column called 
trends and let's say you want to create

00:04:15.055 --> 00:04:17.365
a trend for each of these columns.

00:04:17.702 --> 00:04:25.392
Okay, so one way to do that is to click 
on the cell H3 here and go on to insert

00:04:28.922 --> 00:04:35.242
and in this Sparklines lines 
box here just click on line

00:04:36.725 --> 00:04:38.542
and it asks for a data range.

00:04:38.851 --> 00:04:44.069
So you can just click on this 
data range right here, click OK,

00:04:44.736 --> 00:04:50.186
and location range would be this one right
here, and you click OK, and you will see

00:04:50.186 --> 00:04:55.683
a line here that represents a 
trend for all these four data points.

00:04:56.984 --> 00:05:01.251
Now if you are obviously you don't 
want to just draw a line, a graph,

00:05:01.251 --> 00:05:05.134
for just this one row you want
to do it for the entire region

00:05:05.134 --> 00:05:06.984
and to do that what you do is,

00:05:09.274 --> 00:05:16.604
let me just edit this here, you can edit
the data and instead of saying data range

00:05:16.604 --> 00:05:21.718
is just C3 through F3 I can make 
the data range be this entire region

00:05:23.181 --> 00:05:27.920
and instead of saying the location is
just this, H3, you can see the location

00:05:27.920 --> 00:05:33.564
is all four cells here and you click OK 
and now you have four different lines.

00:05:33.684 --> 00:05:38.588
Each of these lines represents the
trend of sales in this region right here.

00:05:40.194 --> 00:05:41.977
So that's a Sparkline.

00:05:42.301 --> 00:05:47.313
You can make the Sparkline line a 
little bit more informative by changing

00:05:47.313 --> 00:05:48.646
its format a little bit.

00:05:48.962 --> 00:05:55.136
So you can go to design, just 
select the Sparklines, all of them,

00:05:55.136 --> 00:06:01.550
and then click design and you can say
you want to highlight the high point.

00:06:01.757 --> 00:06:05.776
If you do that whichever is the highest 
point in this graph will be highlighted

00:06:05.776 --> 00:06:10.201
for somebody that's looking at it,
it'll be easy to see in which year

00:06:10.201 --> 00:06:15.292
the highest sales happen and you can
change the style of your Sparklines

00:06:15.292 --> 00:06:19.374
to something more appealing, say 
something like this for example.

00:06:19.977 --> 00:06:30.141
If you want you can also change, you know,
put a color to the markers if you do this

00:06:30.141 --> 00:06:34.254
then you'll have markers for each
of the different points and so on.

00:06:34.877 --> 00:06:37.861
So these are all some neat ways 
in which you can display data.

00:06:38.726 --> 00:06:41.986
So that's all for now I hope you 
enjoyed this thanks for watching.

00:06:44.079 --> 00:06:51.309
[country themes music]