Area Charts

Simon Sez IT
A free video tutorial from Simon Sez IT
490,000+ Students | Wordwide Use 180 Countries |150+ Courses
4.4 instructor rating • 169 courses • 500,696 students

Learn more from the full course

The Ultimate Microsoft Excel 2010 and 2013 Training Bundle

Can You Use The Internet? If Yes, Then You Can Master Microsoft Excel 2010 and 2013 The Easy Way - On Your Schedule

33:28:35 of on-demand video • Updated February 2021

  • Essential training for Microsoft Excel 2010
  • Essential training for Microsoft Excel 2013
  • Advanced level training for Microsoft Excel 2010
  • Advanced level training for Microsoft Excel 2013
English [Auto] Hello again and walk back to our course on Excel 2013 advanced in this section we're going to start to look at the more advanced and specialized use of charts and graphs in Excel 2013. And in this first section I'm going to look at a couple of examples of types of charts in Excel that aren't really used very often but which are actually very useful and perhaps for which the most important thing is to know what sort of situations you would use them in. So let's get started. Now let me talk first of all about the types of chart that we have in Excel 2013. If you go into the insert chart dialog and select the old charts type as I'm sure you know you have a list of what are effectively chart types down the left here. Now I am going to assume that for the top three or four on that list you know very well the sort of situations in which they might be used. So I'm going to assume as this is an advanced cell course that you're very familiar with column charts and bar charts and the various variations on them stacked columns and so on. I'm also going to assume that you're very familiar with using pie charts and of course one of the most important aspects of using a pie chart is that a pie chart is used to show how something is divided up how sum total is divided up whereas column charts and bar charts may or may not reflect total in fact usually they don't. The other type of chart I'm going to assume that you've used is a line chart. No in fact I will be doing a couple of line charts and a section or two from now because I'm going to talk about regression and scatter charts and so on but I'm going to assume that you're familiar with the basics and with all of those types of chart I'm assuming that you know how to format them how to mark with data series put on the legends and titles and axes and so on. So I'm going to assume that you have all of that knowledge if you don't now is a good time to go away and catch up because if you don't know how to do those things a couple of the things we're going to cover in the next few sections are going to cause you some problems. Now while I'm demonstrating several of these advanced charting topics I'm going to use two or three sets of data and this is what I'm going to use most. It's some sales data on some individual cells people dividing into two teams. Team and team B the data is for the year 2012 and it's arranged by month and then every three months there is a quarterly total for each member of the team. And then obviously get things like team totals and get an overall total for the two teams as well. Now the first chart I'm going to demonstrate to you is an area chart. Now in many ways an area chart is really just a slick way of presenting a line chart but in order to use area charts if you're going to use them in presentations to improve your presentations you're going to need to know one or two very specific skills associated with using area charts as I'm going to show you now. So first of all let me just select the dates for the Carruthers I've hidden the quarterly total columns and the year total column. I'm only interested in the progress over the 12 months of 2012. So for the US like with data like that's go to the Insert tab. And I'm going to click on line charts insert line chart and it's just going to be a 2D line at the moment and I've got my chart for Andy now. I'm really not going to go into any detail about scales axes labels legends etc. I'm assuming that you're familiar with doing all of that. So that's what the line chart looks like for Andy for last year. Now what I'm going to do is to change the chart type and make it into an area. Charles So I'm going to select area what I get is pretty much the same information but many people believe that having the color filled in underneath gives more of an impression of just how much and these cells have varied over time. Once again I'm sure you know how to change the fill color again. I'm not going to go into that kind of formatting of area charts but where area charts from a presentation or points of view very often come into their own is if you want to compare the performance of several people. Now what I'm going to do is to change the data selection and produce an area chart showing all of the members of Team. So select data. It's currently on sheet for the one that I've got selected at the moment and it's a three to four. I'm going to make it a 3 to P 8. I'm going to go in there delete the four. Change to an 8. Click on OK and see what happens. And what I now get is that rather baffling area chart there. Now that is not an unusual type of problem to have for a 2D area chart. But one thing we can do which very often considerably helps is to turn it into a 3-D area shots. So let's change the chart. We're going to stick with an area chart but the available area charts we're going to choose the 3D area option. We'll go with that first one click on OK and we get our area chart now because there's quite a bit of information to squeeze in there. What Excel 2013 has done is to only label two of the points with sales person names on the axis here. So let's just go into quick lay out and let's go for an area chart with a legend instead that's a little bit easier to follow. Now let's make it a bit bigger. Almost all was with Eric Chozen. Most other charts when you make them a bit bigger you get a lot more detail a lot more easy to see what's going on. And already you can start to see how you can get more information out of this area chart the way it is now. However there's something else we can do here which very often helps with aerial shots and that is that we can introduce some transparency. So for instance if we take Andy Carruthers figures the ones at the front they don't obscure all of the figures that are behind them. So let me select Andy Carruthers area here. If I then right click on that and click on format data series I'll see the format data series pane on the right and I'm going to adjust the fill color. Now that's the option that I want there and I want to go into color sometimes difficult to find. That is if you go down through color look at the selected color you can see the one that selected there that sort of the red color there. If you go into more colors from that point one of the things you get is a transparency box and the transparency is currently set at 0 percent. So it's completely opaque. But if I were to let's go for let's just try 30 percent transparency. Now watch what happens in the chart when I said transparency 30 percent. You can see that you can now see right through and these figures to all of the figures behind. Now getting that transparency level right it may take you a certain amount of tweaking. In my experience you need less transparency than you think you do. But I've just changed that from 30 to 40 if I put that box A to 20 Let's try that. So you need to experiment and then you may or may not need to do several of the layers in order to be able to see through to the layers behind. So if you are putting together a presentation where you wanted to clearly show through a couple of the layers further back in the area chart you could maybe even leave them nontransparent and only make one into the front lines transparent but it's very adaptable. And I tend to think of these area charts as something that I'm going to use in a presentation so I'm prepared to spend a little bit of time on some fine adjustments to how they look in order to achieve the comparisons that I want to achieve. But just one other thing to point out about area charts before we move on when you have a situation like this when you have five apparently similar things in this case the sales figures for five members of a team of sales people and you want to compare them. Sometimes you can actually get a pretty good message by comparing them by showing them as parts of a whole. And you can do that in an interesting way with an area chart as well. So if I go back in to change chart type what I'm going to do this time is I'm going to go for that option which is the stacked area chart and then watch what happens with a stacked area and shot Now what that shows me any point in time any month in the year is how much of the sales within that team is contributed by each member of the team. And although it's a little bit of a scary looks like a bit of pop art really the message sometimes when you present it in this way is quite interesting so if you take a particular month say February you can see that the dark red part here and the Carruthers is quite small whereas this sort of orangey kind of color hair color for Michael Devaux is pretty strong. And that also will work. INTUITY as well. So let me go back to change area time. Let's go back to stacked area in today and that can also work into the as well. So that's an area charts for now. I'll see you in the next section.