Area and Stacked Area Charts5:23 with Michael Watson
An overview of area and stacked area charts in Excel.
Another type of chart that exists in Excel is called, the area chart. 0:00 Area charts are basically line charts that fill in the area between the line and 0:04 the axes. 0:09 An area chart with a single data set can pack much more punch than a line chart. 0:11 Let's look at the gym membership file and 0:16 talk through a couple examples of the area chart. 0:18 Then we'll introduce the stacked area chart, and what stacking means for 0:21 charts, in general. 0:25 Okay, so here we are looking at our line chart of the gym network. 0:27 Let's change this line chart to an area chart. 0:33 Actually, let's go back to our data series and create a new chart. 0:37 So, I'm going to go to Insert and then find the 2-D Area chart. 0:42 Okay, and I'm gonna make it a little bit bigger so 0:49 it's slightly easier for you all to see. 0:51 So, this is an area chart. 0:54 It doesn't really do a good job of telling us a story though, right? 0:58 Well, before we change that I want to explain what you're looking at. 1:02 We can see three colors here, even though there are seven locations. 1:07 This is because the area displays of some of the data series 1:11 are overlapping the others. 1:16 So, we can see the North 2 location here in the front. 1:19 Then, the Southeast location covers most of the area of the chart. 1:24 And finally, we can see a bit of Burnside in the top left, right here. 1:28 You can actually see where this goes going forward but, 1:34 it's behind the Southeast location. 1:38 The Southeast location is bigger than most of the other locations, so 1:41 we don't get to see them. 1:45 Why is it displaying the data like this? 1:47 Well, it's because of how we've ordered our data series. 1:49 So, if we select the Southeast data series, then go to the formula bar, 1:54 we see this equation. 2:00 The syntax says, that the series we've selected that 2:02 the label is on cell A9, so that's the Southeast. 2:07 The horizontal X axis categories sheet 1 B3 through M3 2:12 are right here, those are the months. 2:17 And then for this individual data series, the data points are in cells 2:21 B9 through M9, which is shown by this blue box behind the chart. 2:26 And then, there is this comma at the end with 6. 2:32 Let's change this 6 to a 1, return. 2:38 You can see the order of the data series has now changed. 2:42 It hasn't changed in our data set but it has changed in the chart. 2:47 You can use this trick to reorder your data series on other chart types as well. 2:51 We've put Southeast location at the back. 2:57 We can see more locations now in front of that. 2:59 So clearly, this area chart isn't really telling us a story still. 3:02 Let's whittle the chart down to just the two North locations and see what we find. 3:06 I can do this a few different ways. 3:12 First of all, I could create a new chart, and 3:14 just select the two different North locations as data series. 3:17 We can also click on this chart and click on a specific data series, and 3:21 click Delete. 3:25 So I've got the Southeast location selected right now, I want to delete it. 3:25 I've removed it from the chart. 3:30 Perhaps the best option in this situation is to right-click the chart. 3:32 Go to Select Data. 3:39 And deselect the locations I don't want to include. 3:42 Okay, we're looking at the area charts for both North locations. 3:48 The take away still isn't very clear. 3:54 What we can see is that the second location ramps up. 3:55 The first location declines a bit. 4:01 But, is that really what we want to show? 4:03 What about if we stack the two series? 4:07 So, I'm gonna change the chart type. 4:10 I'm gonna go to Area > Stacked Area, there we go. 4:14 As the name suggests, 4:21 a stacked area chart puts each data series on top of each other. 4:23 Showing the incremental increases from each series to get 4:27 to a total value of all series in the chart. 4:31 This is called, stacking in charts and can be done in both columns and area charts. 4:35 So, here we can see that although there may have been some cannibalization. 4:42 The addition of the second location has indeed added to the membership for 4:48 this gym network in the North region. 4:52 We started off with a little less than 3,000 members at the start of the year. 4:56 And even though we have seasonal impacts that will reduce membership later on in 5:00 the year, we still ended with more than 3,000 members at the end of the year. 5:04 How about you create a stacked column chart for the entire network of gyms? 5:08 Then a stacked area chart. 5:14 In the next video, 5:16 I'll show you how to do that along with an additional way to look at the data. 5:18
You need to sign up for Treehouse in order to download course files.Sign up