How to create and use the Area Chart in Excel.
- For updated video clips in structured Excel courses with practical example files, have a look at our MS Excel online training courses . You can even try the Free MS Excel tips and tricks course.
- To see if this video matches your skill level (see the suggested skill score below) do our free MS Excel skills assessment.
- If you are based in South Africa look at the live courses we offer in Johannesburg and Cape Town.
Another useful chart is the Area Chart. What we have got here is some destinations that phone calls were made to- and months. And we want to be able to compare the totals but also the splits between the various destinations.
To set it up, we highlight the area, we go to Insert, and you’ll see we have got an option here for “Area” – we will just click on it –and there are a couple of options – there is the 2D versus the 3D and other options. For now we are going to do the “Stacked Version” so I will just click on it and you’ll see it generates a graph which looks something like this. If you’re not sure whether this is what you are looking for, a good idea is always to switch the rows and columns and just see if it gives a more representative indication. In my opinion this is not great, I’d rather have the months across the axes. So let’s switch back. So now we can see what is happening in January, February, March and which destination is contributing to these totals.
As with all the charts, you can heavily customize this. So let’s maybe go and see if there is anything we want to use here – so maybe let’s change it to that. We can make it look like that – which looks very nice. If we now right click on one of the series – go to Format Data Series- you’ll see that the options that exist are pretty standard – similar to the column charts. You’ll see we do have a secondary axis option here but if you watch and I click you will see it is quite a difficult one to read so we don’t recommend that for an Area Chart. Otherwise each of the options has the same capabilities – you can change them.
If you are using an Area chart, you will notice that if you go to the Tab called Layout – the Lines Tab is opened up so when I click on it you’ll see we can either have None which is no lines or else we can have things called Drop lines. When I click on it, you’ll see Drop Lines just drop a line through the point that refers to the X axis, and it just makes it easier, sometimes, to read the graphs.
Another useful tip, and this is available to all of the graphs. At the moment you’ll see the X axis has January, February, March, April, May, June. You may want something like this – perhaps that relates to – Quarter 1 and April becomes Quarter 2. You’ll see I have set it up so we have Quarter 1 and we assume that those two apply to Quarter 1 as well. If I go to my “Select Data” and I edit the horizontal axis. At the moment, you’ll see it only pulls that line through. If we change it to pull both lines through- let go – you’ll immediately see what happens here – let’s just click out of it. What it does is it changes the X axis and nicely puts it in the middle so we can actually read the graph as Quarter 1 and underlying stuff, and you can do many other levels like this and as mentioned earlier, it doesn’t only apply to Area Charts but all the other charts as well.