0:00:00.280,0:00:07.746
[country themed introduction music]

0:00:07.746,0:00:10.151
(male narrator)[br]Hello in this screencast we'll take

0:00:10.151,0:00:14.145
a detail look at clustered [br]columns type of graph.

0:00:14.599,0:00:19.662
Let's say you have this data set here [br]Widgets Inc. and you have a bunch of data

0:00:19.662,0:00:24.642
about sales of the various different[br]regions and across different years

0:00:25.255,0:00:28.834
and I also have done subtotals [br]here by region and by year,

0:00:29.422,0:00:35.927
and let's say we want to create a simple[br]clustered column graph for this data.

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

0:00:45.107,0:00:50.732
and that's your simple graph right [br]there but you can see that it's got some

0:00:50.732,0:00:54.248
unnecessary data that probably [br]does not belong in this graph

0:00:54.248,0:00:59.907
for example it has totals by year [br]and it also has totals by region.

0:01:02.054,0:01:03.725
So how do we remove this data?

0:01:03.952,0:01:10.140
Okay so let's first, select this graph[br]just make sure that it's highlighted.

0:01:10.795,0:01:14.317
When you do then you will see a [br]design tab here with all this information

0:01:14.717,0:01:16.574
now click on select data.

0:01:17.288,0:01:21.314
When you click this button you [br]will see a pop up box appear here

0:01:21.614,0:01:26.659
and what you can do here is if you[br]see a column here that doesn't belong

0:01:26.659,0:01:30.839
for example, totals by year, you [br]can just select it and click remove.

0:01:31.234,0:01:36.647
Now then totals by year goes away and you[br]just have the North, South, East, and West

0:01:36.647,0:01:40.447
represented in your graph and you can[br]see that your graph is slightly changed

0:01:40.447,0:01:41.579
to reflect that.

0:01:42.529,0:01:44.763
Supposing you want to [br]remove totals by region.

0:01:44.946,0:01:50.196
What you can do is first switch row and [br]column and now you have totals by region

0:01:50.196,0:01:55.697
as one of the rows and you can [br]just click remove to take that out

0:01:55.916,0:01:57.942
and now you just have the different years

0:01:59.139,0:02:04.561
and if you want to can get back to your[br]original configuration so that you have...

0:02:08.421,0:02:13.381
let's say each column be a [br]year and within each column

0:02:13.381,0:02:17.553
the height represents the total sales[br]and within each year you have

0:02:18.471,0:02:21.338
four different sub columns, [br]each for a different region.

0:02:22.138,0:02:26.739
And if you want you can interchange this[br]so that you have instead of 2009-2010.

0:02:26.988,0:02:29.639
You have North, South, [br]East, and West appear here

0:02:30.013,0:02:32.342
so this will be North, [br]South, East, and West

0:02:32.342,0:02:35.892
and instead of the years appearing here[br]you can have each of these columns

0:02:35.892,0:02:36.962
be a year.

0:02:37.541,0:02:40.931
To do that again select [br]the graph, make that active,

0:02:42.238,0:02:48.582
click on switch row and column so as soon[br]as you do it your rows and columns

0:02:48.582,0:02:49.798
are interchanged.

0:02:50.981,0:02:56.131
So that's a quick and simple way to create[br]a simple bar chart with clustered columns.

0:02:57.264,0:03:00.139
What if you wanted to have [br]a slightly different style?

0:03:00.139,0:03:04.970
Instead of clustered columns maybe you[br]want to have all the columns stacked up

0:03:04.970,0:03:06.276
one on top of another.

0:03:06.492,0:03:07.775
It's simple to do that.

0:03:07.952,0:03:12.050
Just click on change chart type [br]and then you will be presented

0:03:12.050,0:03:17.267
with all the different options and you can[br]just select a stacked column, click OK,

0:03:17.443,0:03:20.068
and that will result in this [br]kind of a stacked column.

0:03:20.068,0:03:26.257
And now you can actually see [br]what's the total sales in each year.

0:03:26.543,0:03:30.503
So you can see that or in this [br]case in each zone you can see that

0:03:30.630,0:03:36.384
East has the highest sales and [br]within each zone you have the sales

0:03:36.384,0:03:37.944
broken up by the year.

0:03:38.259,0:03:43.209
Or you can switch rows and columns and [br]now you have the total sales by each year

0:03:43.209,0:03:47.486
and for each column you have it [br]broken down by sales by region.

0:03:48.803,0:03:52.036
So that's how you can [br]change the chart type on-the-fly.

0:03:55.005,0:03:59.751
Another neat thing you can [br]do is to create Sparklines.

0:03:59.751,0:04:02.746
Ok so let me just [br]actually delete this for now.

0:04:02.746,0:04:05.176
And what you can do is create Sparklines

0:04:07.626,0:04:09.406
I will show you what I mean by that.

0:04:09.406,0:04:15.055
So let's just create a new column called [br]trends and let's say you want to create

0:04:15.055,0:04:17.365
a trend for each of these columns.

0:04:17.702,0:04:25.392
Okay, so one way to do that is to click [br]on the cell H3 here and go on to insert

0:04:28.922,0:04:35.242
and in this Sparklines lines [br]box here just click on line

0:04:36.725,0:04:38.542
and it asks for a data range.

0:04:38.851,0:04:44.069
So you can just click on this [br]data range right here, click OK,

0:04:44.736,0:04:50.186
and location range would be this one right[br]here, and you click OK, and you will see

0:04:50.186,0:04:55.683
a line here that represents a [br]trend for all these four data points.

0:04:56.984,0:05:01.251
Now if you are obviously you don't [br]want to just draw a line, a graph,

0:05:01.251,0:05:05.134
for just this one row you want[br]to do it for the entire region

0:05:05.134,0:05:06.984
and to do that what you do is,

0:05:09.274,0:05:16.604
let me just edit this here, you can edit[br]the data and instead of saying data range

0:05:16.604,0:05:21.718
is just C3 through F3 I can make [br]the data range be this entire region

0:05:23.181,0:05:27.920
and instead of saying the location is[br]just this, H3, you can see the location

0:05:27.920,0:05:33.564
is all four cells here and you click OK [br]and now you have four different lines.

0:05:33.684,0:05:38.588
Each of these lines represents the[br]trend of sales in this region right here.

0:05:40.194,0:05:41.977
So that's a Sparkline.

0:05:42.301,0:05:47.313
You can make the Sparkline line a [br]little bit more informative by changing

0:05:47.313,0:05:48.646
its format a little bit.

0:05:48.962,0:05:55.136
So you can go to design, just [br]select the Sparklines, all of them,

0:05:55.136,0:06:01.550
and then click design and you can say[br]you want to highlight the high point.

0:06:01.757,0:06:05.776
If you do that whichever is the highest [br]point in this graph will be highlighted

0:06:05.776,0:06:10.201
for somebody that's looking at it,[br]it'll be easy to see in which year

0:06:10.201,0:06:15.292
the highest sales happen and you can[br]change the style of your Sparklines

0:06:15.292,0:06:19.374
to something more appealing, say [br]something like this for example.

0:06:19.977,0:06:30.141
If you want you can also change, you know,[br]put a color to the markers if you do this

0:06:30.141,0:06:34.254
then you'll have markers for each[br]of the different points and so on.

0:06:34.877,0:06:37.861
So these are all some neat ways [br]in which you can display data.

0:06:38.726,0:06:41.986
So that's all for now I hope you [br]enjoyed this thanks for watching.

0:06:44.079,0:06:51.309
[country themes music]