1 00:00:00,530 --> 00:00:04,980 Another type of chart that exists in Excel is called, the area chart. 2 00:00:04,980 --> 00:00:09,480 Area charts are basically line charts that fill in the area between the line and 3 00:00:09,480 --> 00:00:10,020 the axes. 4 00:00:11,070 --> 00:00:16,510 An area chart with a single data set can pack much more punch than a line chart. 5 00:00:16,510 --> 00:00:18,570 Let's look at the gym membership file and 6 00:00:18,570 --> 00:00:21,860 talk through a couple examples of the area chart. 7 00:00:21,860 --> 00:00:25,540 Then we'll introduce the stacked area chart, and what stacking means for 8 00:00:25,540 --> 00:00:26,460 charts, in general. 9 00:00:27,820 --> 00:00:32,370 Okay, so here we are looking at our line chart of the gym network. 10 00:00:33,600 --> 00:00:35,920 Let's change this line chart to an area chart. 11 00:00:37,010 --> 00:00:41,380 Actually, let's go back to our data series and create a new chart. 12 00:00:42,810 --> 00:00:48,122 So, I'm going to go to Insert and then find the 2-D Area chart. 13 00:00:49,700 --> 00:00:51,500 Okay, and I'm gonna make it a little bit bigger so 14 00:00:51,500 --> 00:00:54,860 it's slightly easier for you all to see. 15 00:00:54,860 --> 00:00:57,020 So, this is an area chart. 16 00:00:58,160 --> 00:01:01,340 It doesn't really do a good job of telling us a story though, right? 17 00:01:02,450 --> 00:01:06,360 Well, before we change that I want to explain what you're looking at. 18 00:01:07,480 --> 00:01:11,670 We can see three colors here, even though there are seven locations. 19 00:01:11,670 --> 00:01:16,201 This is because the area displays of some of the data series 20 00:01:16,201 --> 00:01:18,520 are overlapping the others. 21 00:01:19,650 --> 00:01:23,730 So, we can see the North 2 location here in the front. 22 00:01:24,880 --> 00:01:28,130 Then, the Southeast location covers most of the area of the chart. 23 00:01:28,130 --> 00:01:33,220 And finally, we can see a bit of Burnside in the top left, right here. 24 00:01:34,760 --> 00:01:38,630 You can actually see where this goes going forward but, 25 00:01:38,630 --> 00:01:40,570 it's behind the Southeast location. 26 00:01:41,720 --> 00:01:45,490 The Southeast location is bigger than most of the other locations, so 27 00:01:45,490 --> 00:01:47,160 we don't get to see them. 28 00:01:47,160 --> 00:01:49,810 Why is it displaying the data like this? 29 00:01:49,810 --> 00:01:53,320 Well, it's because of how we've ordered our data series. 30 00:01:54,500 --> 00:02:00,060 So, if we select the Southeast data series, then go to the formula bar, 31 00:02:00,060 --> 00:02:01,200 we see this equation. 32 00:02:02,250 --> 00:02:06,010 The syntax says, that the series we've selected that 33 00:02:07,130 --> 00:02:12,410 the label is on cell A9, so that's the Southeast. 34 00:02:12,410 --> 00:02:17,890 The horizontal X axis categories sheet 1 B3 through M3 35 00:02:17,890 --> 00:02:21,420 are right here, those are the months. 36 00:02:21,420 --> 00:02:26,640 And then for this individual data series, the data points are in cells 37 00:02:26,640 --> 00:02:31,360 B9 through M9, which is shown by this blue box behind the chart. 38 00:02:32,650 --> 00:02:36,839 And then, there is this comma at the end with 6. 39 00:02:38,450 --> 00:02:42,260 Let's change this 6 to a 1, return. 40 00:02:42,260 --> 00:02:45,520 You can see the order of the data series has now changed. 41 00:02:47,150 --> 00:02:50,920 It hasn't changed in our data set but it has changed in the chart. 42 00:02:51,990 --> 00:02:57,265 You can use this trick to reorder your data series on other chart types as well. 43 00:02:57,265 --> 00:02:59,200 We've put Southeast location at the back. 44 00:02:59,200 --> 00:03:01,320 We can see more locations now in front of that. 45 00:03:02,320 --> 00:03:06,820 So clearly, this area chart isn't really telling us a story still. 46 00:03:06,820 --> 00:03:12,180 Let's whittle the chart down to just the two North locations and see what we find. 47 00:03:12,180 --> 00:03:14,780 I can do this a few different ways. 48 00:03:14,780 --> 00:03:17,370 First of all, I could create a new chart, and 49 00:03:17,370 --> 00:03:21,645 just select the two different North locations as data series. 50 00:03:21,645 --> 00:03:25,170 We can also click on this chart and click on a specific data series, and 51 00:03:25,170 --> 00:03:25,840 click Delete. 52 00:03:25,840 --> 00:03:30,600 So I've got the Southeast location selected right now, I want to delete it. 53 00:03:30,600 --> 00:03:32,590 I've removed it from the chart. 54 00:03:32,590 --> 00:03:39,720 Perhaps the best option in this situation is to right-click the chart. 55 00:03:39,720 --> 00:03:40,600 Go to Select Data. 56 00:03:42,380 --> 00:03:45,559 And deselect the locations I don't want to include. 57 00:03:48,891 --> 00:03:53,160 Okay, we're looking at the area charts for both North locations. 58 00:03:54,210 --> 00:03:55,850 The take away still isn't very clear. 59 00:03:55,850 --> 00:04:00,180 What we can see is that the second location ramps up. 60 00:04:01,460 --> 00:04:03,840 The first location declines a bit. 61 00:04:03,840 --> 00:04:05,990 But, is that really what we want to show? 62 00:04:07,010 --> 00:04:09,190 What about if we stack the two series? 63 00:04:10,420 --> 00:04:12,065 So, I'm gonna change the chart type. 64 00:04:14,255 --> 00:04:19,580 I'm gonna go to Area > Stacked Area, there we go. 65 00:04:21,600 --> 00:04:23,340 As the name suggests, 66 00:04:23,340 --> 00:04:27,950 a stacked area chart puts each data series on top of each other. 67 00:04:27,950 --> 00:04:31,540 Showing the incremental increases from each series to get 68 00:04:31,540 --> 00:04:35,250 to a total value of all series in the chart. 69 00:04:35,250 --> 00:04:42,010 This is called, stacking in charts and can be done in both columns and area charts. 70 00:04:42,010 --> 00:04:48,760 So, here we can see that although there may have been some cannibalization. 71 00:04:48,760 --> 00:04:52,920 The addition of the second location has indeed added to the membership for 72 00:04:52,920 --> 00:04:56,050 this gym network in the North region. 73 00:04:56,050 --> 00:05:00,270 We started off with a little less than 3,000 members at the start of the year. 74 00:05:00,270 --> 00:05:04,040 And even though we have seasonal impacts that will reduce membership later on in 75 00:05:04,040 --> 00:05:07,760 the year, we still ended with more than 3,000 members at the end of the year. 76 00:05:08,900 --> 00:05:14,690 How about you create a stacked column chart for the entire network of gyms? 77 00:05:14,690 --> 00:05:16,950 Then a stacked area chart. 78 00:05:16,950 --> 00:05:18,470 In the next video, 79 00:05:18,470 --> 00:05:22,170 I'll show you how to do that along with an additional way to look at the data.