Analysing data in excel

Here’s a way to help your senior leadership team find out about some of the interesting things you can do in Excel. I used Office 2007 for this, but I’m sure you can achieve the same in Office 2003. I find these kind of training examples are sometimes tedious, unless there’s a real purpose. But this example has got a real purpose that your senior managers will like – understanding the relationship between spend and achievement. If you can get them to do this, then I bet you’ll see charts like this popping up at lots of meetings!

Last week, I read a breakdown of local authority education budgets per pupil. (From a parliamentary answer on TheyWorkForYou). It’s a list of all of the Direct School Grants, per pupil, for all of England’s local authorities. (Because you’ll ask: London authorities account for the top 16, with Tower Hamlets top. Leicestershire are bottom)

And I wondered what the link was between spend and league tables. And 5 minutes in Excel gave me the answer:


The chart above compares each individual local authority’s Contextual Value Added score (CVA) to their spend per pupil.
The trend line shows that overall, the higher the budget, the greater the CVA score.
But, there are a couple of odd outliers in this chart, but you’ll need to follow the instructions below to see who they are!

Although I did it at a national level, I bet your head or deputy head would want to do it for schools in your own local authority!

Comparing School Budgets to Value Added: How to do it

Here’s my step-by-step guide to how I created the chart above.

  1. Get the spend data: Paste the table from the link above, into Excel (just highlight the local authority rows in the table on the web page, COPY and PASTE into Excel. Then delete the 2007-8 column. 
  2. Now add the League Tables from the BBC website: Highlight the local authority rows on the web page, COPY and then PASTE>SPECIAL>AS TEXT into Excel
    Halfway down this table you’ll need to delete the row that has the England average figures.
  3. Make a single table: Sort both tables so that the Local Authority names match up, then delete a few columns to get them into one single table. (I just kept EM for exam results, CVA for Contextual Value Added, and left one column of local authority names).
  4. image Now you’re ready to create the chart: With nothing highlighted in Excel, choose the INSERT tab on the ribbon, then CHART>SCATTER. Then you need to tell it what data to look for. On the DESIGN tab, choose SELECT DATA. In the popup, select “Add”, and you’ll see the menu on the right. Any old name will do. For Series X values, choose the budget column (without the heading) and for the Series Y Values, choose the CVA or Exam Results column. And then click OK.
    When doing “Series Y Values”, you may need to delete the “={1}” so that the format looks like the box above
  5. Now add the Trend Line: On the DESIGN tab of the ribbon, choose Layout 3, which has a trend line in it.
  6. Err, that’s it folks.

Here’s a challenge: Who can be first to add a comment to say which way the trend line goes when you compare “BUDGET” and “5 A*-C”? (And only then will I share the SkyDrive link to my version to download)

And, as a bonus, who can improve these instructions so that you can hover over a dot on the chart, and see the name of the local authority?

Comments (4)

  1. neiladam says:

    Hi Ray. Bit worried about your analysis here. Trying to remember my stats, but if you change the Options for the trend line and add an R-squared value you get 0.2424. Basically, you’ve plotted a nice line, but it’s fit is dodgy. A perfect fit would be 1.0. "There’s lies, da…" Neil

  2. Ray Fleming says:

    Hi Neil,

    I have to admit my knowledge of statistics stopped at what I did in A-Level Economics and Geography, so you’re already well ahead of me.

    I just looked at the chart, and thought "yes, it looks like CVA goes up when more money is spent", and so clicked on the chart with the trend line to see if the line went in that direction.

    However, with your deeper knowledge (sorry, the ‘R-squared’ but went straight over my head) does that mean that there isn’t a positive trend. (And, on the GCSE chart where the line seems to fall, is that also not correct?)

    Here’s my original worksheet:

    And, back the original point, did most people in school SLT’s know their way around Excel well enough to produce simple charts like this quickly? (I used this example because I thought it would attract SLT to the idea of comparing data in a chart – and because I wanted to know if there was a link)


  3. neiladam says:

    Hi Ray

    There is a definite positive trend on the first chart, but the correlation is weak, so you cannot create strong conclusions on that. (And correlation is not, of course, same as causation.) As for second chart, negative correlation is so weak (R^2=0.0384) that I’d be careful about using it at all.

    I’m not a statistician either – little further than you actually (degree Geog and A-level maths), and too long ago.

    These tools are powerful and fairly simple, but we also need people to use them with a degree of understanding of what they represent, what confidence can be placed on what they appear to reveal and other factors involved in interpretation. Schools should be able to use them – and secondaries at least should have mathematicians (or others) quite capable of interpreting.


  4. sprince says:

    Hi Ray,

    From those BBC statistics it’s reassuring to see that there is a definite relationship between attendance and performance… otherwise I think it’d be time to pack up and go home!

    The figures relating to cost/pupil are difficult to separate out from factors such as social deprivation and cost of living in each different authority so I would be surprised to see anything significant come from them. BSF will also muddy the figures as authorities running under BSF contracts will be spending vastly greater sums than those which aren’t, with very little chance of it leading to proportionately improved results (as much of the increased spend is going to commercial companies rather than to schools themselves)

Skip to main content