1
00:00:00,280 --> 00:00:07,746
[country themed introduction music]

2
00:00:07,746 --> 00:00:10,151
(male narrator)
Hello in this screencast we'll take

3
00:00:10,151 --> 00:00:14,145
a detail look at clustered 
columns type of graph.

4
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

5
00:00:19,662 --> 00:00:24,642
about sales of the various different
regions and across different years

6
00:00:25,255 --> 00:00:28,834
and I also have done subtotals 
here by region and by year,

7
00:00:29,422 --> 00:00:35,927
and let's say we want to create a simple
clustered column graph for this data.

8
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,

9
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

10
00:00:50,732 --> 00:00:54,248
unnecessary data that probably 
does not belong in this graph

11
00:00:54,248 --> 00:00:59,907
for example it has totals by year 
and it also has totals by region.

12
00:01:02,054 --> 00:01:03,725
So how do we remove this data?

13
00:01:03,952 --> 00:01:10,140
Okay so let's first, select this graph
just make sure that it's highlighted.

14
00:01:10,795 --> 00:01:14,317
When you do then you will see a 
design tab here with all this information

15
00:01:14,717 --> 00:01:16,574
now click on select data.

16
00:01:17,288 --> 00:01:21,314
When you click this button you 
will see a pop up box appear here

17
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

18
00:01:26,659 --> 00:01:30,839
for example, totals by year, you 
can just select it and click remove.

19
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

20
00:01:36,647 --> 00:01:40,447
represented in your graph and you can
see that your graph is slightly changed

21
00:01:40,447 --> 00:01:41,579
to reflect that.

22
00:01:42,529 --> 00:01:44,763
Supposing you want to 
remove totals by region.

23
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

24
00:01:50,196 --> 00:01:55,697
as one of the rows and you can 
just click remove to take that out

25
00:01:55,916 --> 00:01:57,942
and now you just have the different years

26
00:01:59,139 --> 00:02:04,561
and if you want to can get back to your
original configuration so that you have...

27
00:02:08,421 --> 00:02:13,381
let's say each column be a 
year and within each column

28
00:02:13,381 --> 00:02:17,553
the height represents the total sales
and within each year you have

29
00:02:18,471 --> 00:02:21,338
four different sub columns, 
each for a different region.

30
00:02:22,138 --> 00:02:26,739
And if you want you can interchange this
so that you have instead of 2009-2010.

31
00:02:26,988 --> 00:02:29,639
You have North, South, 
East, and West appear here

32
00:02:30,013 --> 00:02:32,342
so this will be North, 
South, East, and West

33
00:02:32,342 --> 00:02:35,892
and instead of the years appearing here
you can have each of these columns

34
00:02:35,892 --> 00:02:36,962
be a year.

35
00:02:37,541 --> 00:02:40,931
To do that again select 
the graph, make that active,

36
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

37
00:02:48,582 --> 00:02:49,798
are interchanged.

38
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.

39
00:02:57,264 --> 00:03:00,139
What if you wanted to have 
a slightly different style?

40
00:03:00,139 --> 00:03:04,970
Instead of clustered columns maybe you
want to have all the columns stacked up

41
00:03:04,970 --> 00:03:06,276
one on top of another.

42
00:03:06,492 --> 00:03:07,775
It's simple to do that.

43
00:03:07,952 --> 00:03:12,050
Just click on change chart type 
and then you will be presented

44
00:03:12,050 --> 00:03:17,267
with all the different options and you can
just select a stacked column, click OK,

45
00:03:17,443 --> 00:03:20,068
and that will result in this 
kind of a stacked column.

46
00:03:20,068 --> 00:03:26,257
And now you can actually see 
what's the total sales in each year.

47
00:03:26,543 --> 00:03:30,503
So you can see that or in this 
case in each zone you can see that

48
00:03:30,630 --> 00:03:36,384
East has the highest sales and 
within each zone you have the sales

49
00:03:36,384 --> 00:03:37,944
broken up by the year.

50
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

51
00:03:43,209 --> 00:03:47,486
and for each column you have it 
broken down by sales by region.

52
00:03:48,803 --> 00:03:52,036
So that's how you can 
change the chart type on-the-fly.

53
00:03:55,005 --> 00:03:59,751
Another neat thing you can 
do is to create Sparklines.

54
00:03:59,751 --> 00:04:02,746
Ok so let me just 
actually delete this for now.

55
00:04:02,746 --> 00:04:05,176
And what you can do is create Sparklines

56
00:04:07,626 --> 00:04:09,406
I will show you what I mean by that.

57
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

58
00:04:15,055 --> 00:04:17,365
a trend for each of these columns.

59
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

60
00:04:28,922 --> 00:04:35,242
and in this Sparklines lines 
box here just click on line

61
00:04:36,725 --> 00:04:38,542
and it asks for a data range.

62
00:04:38,851 --> 00:04:44,069
So you can just click on this 
data range right here, click OK,

63
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

64
00:04:50,186 --> 00:04:55,683
a line here that represents a 
trend for all these four data points.

65
00:04:56,984 --> 00:05:01,251
Now if you are obviously you don't 
want to just draw a line, a graph,

66
00:05:01,251 --> 00:05:05,134
for just this one row you want
to do it for the entire region

67
00:05:05,134 --> 00:05:06,984
and to do that what you do is,

68
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

69
00:05:16,604 --> 00:05:21,718
is just C3 through F3 I can make 
the data range be this entire region

70
00:05:23,181 --> 00:05:27,920
and instead of saying the location is
just this, H3, you can see the location

71
00:05:27,920 --> 00:05:33,564
is all four cells here and you click OK 
and now you have four different lines.

72
00:05:33,684 --> 00:05:38,588
Each of these lines represents the
trend of sales in this region right here.

73
00:05:40,194 --> 00:05:41,977
So that's a Sparkline.

74
00:05:42,301 --> 00:05:47,313
You can make the Sparkline line a 
little bit more informative by changing

75
00:05:47,313 --> 00:05:48,646
its format a little bit.

76
00:05:48,962 --> 00:05:55,136
So you can go to design, just 
select the Sparklines, all of them,

77
00:05:55,136 --> 00:06:01,550
and then click design and you can say
you want to highlight the high point.

78
00:06:01,757 --> 00:06:05,776
If you do that whichever is the highest 
point in this graph will be highlighted

79
00:06:05,776 --> 00:06:10,201
for somebody that's looking at it,
it'll be easy to see in which year

80
00:06:10,201 --> 00:06:15,292
the highest sales happen and you can
change the style of your Sparklines

81
00:06:15,292 --> 00:06:19,374
to something more appealing, say 
something like this for example.

82
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

83
00:06:30,141 --> 00:06:34,254
then you'll have markers for each
of the different points and so on.

84
00:06:34,877 --> 00:06:37,861
So these are all some neat ways 
in which you can display data.

85
00:06:38,726 --> 00:06:41,986
So that's all for now I hope you 
enjoyed this thanks for watching.

86
00:06:44,079 --> 00:06:51,309
[country themes music]