[Script Info]
Title: 
[Events]
Format: Layer, Start, End, Style, Name, MarginL, MarginR, MarginV, Effect, Text
Dialogue: 0,0:00:00.28,0:00:07.75,Default,,0000,0000,0000,,[country themed introduction music]
Dialogue: 0,0:00:07.75,0:00:10.15,Default,,0000,0000,0000,,(male narrator)\NHello in this screencast we'll take
Dialogue: 0,0:00:10.15,0:00:14.14,Default,,0000,0000,0000,,a detail look at clustered \Ncolumns type of graph.
Dialogue: 0,0:00:14.60,0:00:19.66,Default,,0000,0000,0000,,Let's say you have this data set here \NWidgets Inc. and you have a bunch of data
Dialogue: 0,0:00:19.66,0:00:24.64,Default,,0000,0000,0000,,about sales of the various different\Nregions and across different years
Dialogue: 0,0:00:25.26,0:00:28.83,Default,,0000,0000,0000,,and I also have done subtotals \Nhere by region and by year,
Dialogue: 0,0:00:29.42,0:00:35.93,Default,,0000,0000,0000,,and let's say we want to create a simple\Nclustered column graph for this data.
Dialogue: 0,0:00:36.32,0:00:45.11,Default,,0000,0000,0000,,First you select the data set, insert a\Ncolumn I just choose a plain 2-D column,
Dialogue: 0,0:00:45.11,0:00:50.73,Default,,0000,0000,0000,,and that's your simple graph right \Nthere but you can see that it's got some
Dialogue: 0,0:00:50.73,0:00:54.25,Default,,0000,0000,0000,,unnecessary data that probably \Ndoes not belong in this graph
Dialogue: 0,0:00:54.25,0:00:59.91,Default,,0000,0000,0000,,for example it has totals by year \Nand it also has totals by region.
Dialogue: 0,0:01:02.05,0:01:03.72,Default,,0000,0000,0000,,So how do we remove this data?
Dialogue: 0,0:01:03.95,0:01:10.14,Default,,0000,0000,0000,,Okay so let's first, select this graph\Njust make sure that it's highlighted.
Dialogue: 0,0:01:10.80,0:01:14.32,Default,,0000,0000,0000,,When you do then you will see a \Ndesign tab here with all this information
Dialogue: 0,0:01:14.72,0:01:16.57,Default,,0000,0000,0000,,now click on select data.
Dialogue: 0,0:01:17.29,0:01:21.31,Default,,0000,0000,0000,,When you click this button you \Nwill see a pop up box appear here
Dialogue: 0,0:01:21.61,0:01:26.66,Default,,0000,0000,0000,,and what you can do here is if you\Nsee a column here that doesn't belong
Dialogue: 0,0:01:26.66,0:01:30.84,Default,,0000,0000,0000,,for example, totals by year, you \Ncan just select it and click remove.
Dialogue: 0,0:01:31.23,0:01:36.65,Default,,0000,0000,0000,,Now then totals by year goes away and you\Njust have the North, South, East, and West
Dialogue: 0,0:01:36.65,0:01:40.45,Default,,0000,0000,0000,,represented in your graph and you can\Nsee that your graph is slightly changed
Dialogue: 0,0:01:40.45,0:01:41.58,Default,,0000,0000,0000,,to reflect that.
Dialogue: 0,0:01:42.53,0:01:44.76,Default,,0000,0000,0000,,Supposing you want to \Nremove totals by region.
Dialogue: 0,0:01:44.95,0:01:50.20,Default,,0000,0000,0000,,What you can do is first switch row and \Ncolumn and now you have totals by region
Dialogue: 0,0:01:50.20,0:01:55.70,Default,,0000,0000,0000,,as one of the rows and you can \Njust click remove to take that out
Dialogue: 0,0:01:55.92,0:01:57.94,Default,,0000,0000,0000,,and now you just have the different years
Dialogue: 0,0:01:59.14,0:02:04.56,Default,,0000,0000,0000,,and if you want to can get back to your\Noriginal configuration so that you have...
Dialogue: 0,0:02:08.42,0:02:13.38,Default,,0000,0000,0000,,let's say each column be a \Nyear and within each column
Dialogue: 0,0:02:13.38,0:02:17.55,Default,,0000,0000,0000,,the height represents the total sales\Nand within each year you have
Dialogue: 0,0:02:18.47,0:02:21.34,Default,,0000,0000,0000,,four different sub columns, \Neach for a different region.
Dialogue: 0,0:02:22.14,0:02:26.74,Default,,0000,0000,0000,,And if you want you can interchange this\Nso that you have instead of 2009-2010.
Dialogue: 0,0:02:26.99,0:02:29.64,Default,,0000,0000,0000,,You have North, South, \NEast, and West appear here
Dialogue: 0,0:02:30.01,0:02:32.34,Default,,0000,0000,0000,,so this will be North, \NSouth, East, and West
Dialogue: 0,0:02:32.34,0:02:35.89,Default,,0000,0000,0000,,and instead of the years appearing here\Nyou can have each of these columns
Dialogue: 0,0:02:35.89,0:02:36.96,Default,,0000,0000,0000,,be a year.
Dialogue: 0,0:02:37.54,0:02:40.93,Default,,0000,0000,0000,,To do that again select \Nthe graph, make that active,
Dialogue: 0,0:02:42.24,0:02:48.58,Default,,0000,0000,0000,,click on switch row and column so as soon\Nas you do it your rows and columns
Dialogue: 0,0:02:48.58,0:02:49.80,Default,,0000,0000,0000,,are interchanged.
Dialogue: 0,0:02:50.98,0:02:56.13,Default,,0000,0000,0000,,So that's a quick and simple way to create\Na simple bar chart with clustered columns.
Dialogue: 0,0:02:57.26,0:03:00.14,Default,,0000,0000,0000,,What if you wanted to have \Na slightly different style?
Dialogue: 0,0:03:00.14,0:03:04.97,Default,,0000,0000,0000,,Instead of clustered columns maybe you\Nwant to have all the columns stacked up
Dialogue: 0,0:03:04.97,0:03:06.28,Default,,0000,0000,0000,,one on top of another.
Dialogue: 0,0:03:06.49,0:03:07.78,Default,,0000,0000,0000,,It's simple to do that.
Dialogue: 0,0:03:07.95,0:03:12.05,Default,,0000,0000,0000,,Just click on change chart type \Nand then you will be presented
Dialogue: 0,0:03:12.05,0:03:17.27,Default,,0000,0000,0000,,with all the different options and you can\Njust select a stacked column, click OK,
Dialogue: 0,0:03:17.44,0:03:20.07,Default,,0000,0000,0000,,and that will result in this \Nkind of a stacked column.
Dialogue: 0,0:03:20.07,0:03:26.26,Default,,0000,0000,0000,,And now you can actually see \Nwhat's the total sales in each year.
Dialogue: 0,0:03:26.54,0:03:30.50,Default,,0000,0000,0000,,So you can see that or in this \Ncase in each zone you can see that
Dialogue: 0,0:03:30.63,0:03:36.38,Default,,0000,0000,0000,,East has the highest sales and \Nwithin each zone you have the sales
Dialogue: 0,0:03:36.38,0:03:37.94,Default,,0000,0000,0000,,broken up by the year.
Dialogue: 0,0:03:38.26,0:03:43.21,Default,,0000,0000,0000,,Or you can switch rows and columns and \Nnow you have the total sales by each year
Dialogue: 0,0:03:43.21,0:03:47.49,Default,,0000,0000,0000,,and for each column you have it \Nbroken down by sales by region.
Dialogue: 0,0:03:48.80,0:03:52.04,Default,,0000,0000,0000,,So that's how you can \Nchange the chart type on-the-fly.
Dialogue: 0,0:03:55.00,0:03:59.75,Default,,0000,0000,0000,,Another neat thing you can \Ndo is to create Sparklines.
Dialogue: 0,0:03:59.75,0:04:02.75,Default,,0000,0000,0000,,Ok so let me just \Nactually delete this for now.
Dialogue: 0,0:04:02.75,0:04:05.18,Default,,0000,0000,0000,,And what you can do is create Sparklines
Dialogue: 0,0:04:07.63,0:04:09.41,Default,,0000,0000,0000,,I will show you what I mean by that.
Dialogue: 0,0:04:09.41,0:04:15.06,Default,,0000,0000,0000,,So let's just create a new column called \Ntrends and let's say you want to create
Dialogue: 0,0:04:15.06,0:04:17.36,Default,,0000,0000,0000,,a trend for each of these columns.
Dialogue: 0,0:04:17.70,0:04:25.39,Default,,0000,0000,0000,,Okay, so one way to do that is to click \Non the cell H3 here and go on to insert
Dialogue: 0,0:04:28.92,0:04:35.24,Default,,0000,0000,0000,,and in this Sparklines lines \Nbox here just click on line
Dialogue: 0,0:04:36.72,0:04:38.54,Default,,0000,0000,0000,,and it asks for a data range.
Dialogue: 0,0:04:38.85,0:04:44.07,Default,,0000,0000,0000,,So you can just click on this \Ndata range right here, click OK,
Dialogue: 0,0:04:44.74,0:04:50.19,Default,,0000,0000,0000,,and location range would be this one right\Nhere, and you click OK, and you will see
Dialogue: 0,0:04:50.19,0:04:55.68,Default,,0000,0000,0000,,a line here that represents a \Ntrend for all these four data points.
Dialogue: 0,0:04:56.98,0:05:01.25,Default,,0000,0000,0000,,Now if you are obviously you don't \Nwant to just draw a line, a graph,
Dialogue: 0,0:05:01.25,0:05:05.13,Default,,0000,0000,0000,,for just this one row you want\Nto do it for the entire region
Dialogue: 0,0:05:05.13,0:05:06.98,Default,,0000,0000,0000,,and to do that what you do is,
Dialogue: 0,0:05:09.27,0:05:16.60,Default,,0000,0000,0000,,let me just edit this here, you can edit\Nthe data and instead of saying data range
Dialogue: 0,0:05:16.60,0:05:21.72,Default,,0000,0000,0000,,is just C3 through F3 I can make \Nthe data range be this entire region
Dialogue: 0,0:05:23.18,0:05:27.92,Default,,0000,0000,0000,,and instead of saying the location is\Njust this, H3, you can see the location
Dialogue: 0,0:05:27.92,0:05:33.56,Default,,0000,0000,0000,,is all four cells here and you click OK \Nand now you have four different lines.
Dialogue: 0,0:05:33.68,0:05:38.59,Default,,0000,0000,0000,,Each of these lines represents the\Ntrend of sales in this region right here.
Dialogue: 0,0:05:40.19,0:05:41.98,Default,,0000,0000,0000,,So that's a Sparkline.
Dialogue: 0,0:05:42.30,0:05:47.31,Default,,0000,0000,0000,,You can make the Sparkline line a \Nlittle bit more informative by changing
Dialogue: 0,0:05:47.31,0:05:48.65,Default,,0000,0000,0000,,its format a little bit.
Dialogue: 0,0:05:48.96,0:05:55.14,Default,,0000,0000,0000,,So you can go to design, just \Nselect the Sparklines, all of them,
Dialogue: 0,0:05:55.14,0:06:01.55,Default,,0000,0000,0000,,and then click design and you can say\Nyou want to highlight the high point.
Dialogue: 0,0:06:01.76,0:06:05.78,Default,,0000,0000,0000,,If you do that whichever is the highest \Npoint in this graph will be highlighted
Dialogue: 0,0:06:05.78,0:06:10.20,Default,,0000,0000,0000,,for somebody that's looking at it,\Nit'll be easy to see in which year
Dialogue: 0,0:06:10.20,0:06:15.29,Default,,0000,0000,0000,,the highest sales happen and you can\Nchange the style of your Sparklines
Dialogue: 0,0:06:15.29,0:06:19.37,Default,,0000,0000,0000,,to something more appealing, say \Nsomething like this for example.
Dialogue: 0,0:06:19.98,0:06:30.14,Default,,0000,0000,0000,,If you want you can also change, you know,\Nput a color to the markers if you do this
Dialogue: 0,0:06:30.14,0:06:34.25,Default,,0000,0000,0000,,then you'll have markers for each\Nof the different points and so on.
Dialogue: 0,0:06:34.88,0:06:37.86,Default,,0000,0000,0000,,So these are all some neat ways \Nin which you can display data.
Dialogue: 0,0:06:38.73,0:06:41.99,Default,,0000,0000,0000,,So that's all for now I hope you \Nenjoyed this thanks for watching.
Dialogue: 0,0:06:44.08,0:06:51.31,Default,,0000,0000,0000,,[country themes music]