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?