Transcripts for the above video clip:

Excel 2007 Graphs Scatter Charts

A scatter graph allows you to plot various points on a chart based on the X,Y co-ordinates.  So what you’ll see here is we have got a couple of branches. We have got the Revenue Growth Percentage, and we have got the Growth Profit Percentage, so  on a single graph  I want to see when the Revenue Growth is 21% and the GP percent is 12% on the X,Y axis. 

Now setting up for the scatter graph is very important – so you’ll see the first column is always the X axis and the subsequent column or columns  is the Y axis.  You’ll see I have given them some names, but is useful  just to put on top what information you are looking for.  What we can do is we highlight the X and Y axis – go to Insert – you’ll see under Scatter there are a couple of options. Let’s just use the X, Y co-ordinates here – click it – what you’ll see is it generates this graph. Looking at this graph it just gets a bit confusing about which percentage applies to what, so we are just going to label them. I go to Layout – go to axes and I am going to say the horizontal title – title below – then  I know these  belong to the Revenue Growth  – and I know the vertical belongs to the GP percentage. Now we can look at what these are telling us – you can see if you hover over that point  - that this  the series with the Revenue Growth of 30%, which is  represented here, has  a  GP percentage of 11%. So each of these points identify the Revenue Growth and the  GP percentage.  Just looking at this graph you can see that these all look reasonable and there is one here where basically the revenue is negative but the GP percentage is even more negative. It is a nice way of expressing some information.

Just a couple of things to be aware of – I am just going to move this down – now I am going to increase this  to include those two extra cells . If you  want to add a new point, you simply have to put in its X co-ordinate and its Y co-ordinate.  You don’t have to worry about not duplicating – so, for example, if we have another point  we can put in let’s say,  another 31% here, same number  as there – but for this one the GP percentage  is – let’s make it a big number – 20%. When I push Enter just watch what happens here – you’ll see we get another point here. So you can add points, you mustn’t be afraid to duplicate the  points that are on the X axis.

Also be aware that you aren’t limited to only one column for the Y axis.  You can put another one in, so over here let me put Opex percentage, and effectively, that is the second Y axis.  If I include that area in the graph, you’ll see the graph now has another item in the legend called Opex Percentage  which  will be represented by a red square. Let’s go here and put in a number like 15%. Notice my red square has popped up here, 15% on the Y axis and note that it’s using the very first column as its X axis again – so you’ll see the Revenue Growth is sitting there. So using this method  you can plot multiple items on the same scatter graph. Let’s  just to complete it, pretend that they are all  exactly the same and you’ll see that  no matter what, in this case, the Revenue Growth is,  Opex percentage is always the same.

One of the problems with this is that I would actually like the labels to say what branch they come for, but the way the labels work with the X,Y graph, if I say add data labels you’ll see the label is actually considered  the X axis or else it would be the Y or else it could be these labels here,  all of which aren’t really meaningful. I am just going here- you’ll see if I don’t want the Y value, I may want the X value- you’ll see I get the percentage when I want the series name  - you’ll see they all say GP percentage. What I’d like them to do is to say Joburg, Cape Town, Bloemfontein,  etc. There are two ways around this, the first  way is a little bit manual. What we can do is let’s choose this series here, and you’ll see this item here relates to a new item, and that one there relates to Bloemfontein.  I can right click on my series, I am going to right click and add a data label. I am then going to click on the Data labels and click again – so we are now looking  at that one, without doing anything else, I am going to click “equals”  and notice that my equals has appeared up here in the formula bar. I know that   relates  to these lines – Bloemfontein – I go here – and when I click  Enter, note what happens – suddenly the BLM appears for Bloemfontein.  This one here, you can highlight that again  - we know that this relates to JHB, I say “equals” JHB, Enter, and there is JHB. And so we can go on.  In this case, let’s pretend we are only interested in those two  -  I can remove the individual ones  and let’s  remove this Opex Series and you can have a graph like this which not only  gives you the points but  actually gives you the labels as well.

Perhaps the better way is to do the following. It all involves the set up of the data.  I will just go down here. What you’ll see, instead of setting it  up  nicely with the X axis  and the Y axis right next to it, what I have done is created an X axis. But then for each branch, I have labelled it with the branch and then I have put its percentage in.  Cape Town, labelled it, put the percentage in – so you have this jagged look.  Let’s see what  happens  when I convert it into a scatter – go to scatter – click on that one. OK, you can see that is not quite what we want - so we just switch the rows and columns. OK. Now what we have got is suddenly, for each different branch, we have a different marker and it tells us exactly what the Revenue Growth is and the GP percentage growth is.