How to Use the Excel Spreadsheets to Analyse Your English Sats Results

How to Use the Excel Spreadsheets to Analyse Your English Sats Results

How to use the Excel spreadsheets to analyse your English SATs results.

Why analyse the SATs results?:

Three main reasons

a)To inform your class teaching for next year – are there particular areas you need to give a clearer emphasis to?

b)To inform the next class teacher about targets for individual children or groups of children.

c)To help inform the co-ordinator or Staff development officer whether there is a training issue for staff.

How to enter the Reading and Spelling results:

  1. Mark your Optional SATs papers using the marking guidelines given.
  2. Total the scores from all the papers.
  3. Order the pupil’s papers in ascending numerical order.
  4. Open the Excel Spreadsheet programme and choose the appropriate file (e.g. Y5 Optional English SATs)
  5. Enter the pupil’s names in the top row of each sheet (Reading, Writing and Spelling) in ascending order from left to right.
  6. Now enter the marks awarded for each pupil for each question as follows:-

The numerical score gained e.g. 1, 2 or 3

A O for an incorrect answer

A cross for a not attempted answer (these will also give you valuable information – are there patterns showing non-attempts, why is this? Lack of self-confidence? Lack of knowledge?)

  1. Now click on File and Save as and give it a name e.g. Y5 English Reading 2000 cohort. This will mean that you still have the original blank copy as well as a completed copy of your worksheets.

How to enter the Writing results:

  1. When marking the writing papers it is suggested you either use the analysissheets at the back of the each Optional SAT teacher’s books or use the National Curriculum Level descriptor Assessment grids available on in the Professional Issues and policies section / Policies / Assessment / Assessment grids.
  2. If using the analysis sheets it is suggested that you use a code to indicate whether each child shows evidence of each of the elements listed (e.g. N = not shown; B = beginning to show evidence and Y = evidence shown). This will mean that when you analyse the results of the class you will get an indication of areas where there are areas of strength and weakness as outlined below.
  3. If using the National Curriculum Level descriptor Assessment grids then use the alternative Writing recording sheet which uses the headings from the Assessment / Moderation grids. Enter the “level” given for each section to again give an indication of strengths and weaknesses when the results are analysed.
  4. Put the papers in the same order as for the Reading results and enter the marks on the grid.
  5. Now click on File and Save as and give it a name e.g. Y5 English Writing 2000 cohort. This will mean that you still have the original blank copy as well as a completed copy of your worksheets.

Using the results:

Once you have all the results entered you can now sort the information to enable you to look at particular sections of the class.

  1. Highlight the whole spreadsheet by clicking on the small grey box in the top left hand corner

This one!

  1. Now click on Data
  2. Click on Sort
  3. And tell to sort by Column B (for Reading and Writing) and Column C for type of Spelling or by Column D for how the children performed against the different word level objectives and then click on OK
  4. The computer will now sort the question types into alphabetical order. This means that by looking across the rows you can now look at specific topics and ascertain whether are any types of questions which the whole class struggled with e.g. textual evidence (this would then give the current class teacher an area to focus on during the next year). The question numbers are also given so that you can refer back to the question paper to see whether the wording is familiar/ unfamiliar to the children.

By looking at the columns either singly for individual children or in blocks for groups of children you can also look for specific areas where children struggled in order to provide group or individual targets for next year. (E.g. your more able children generally didn’t perform well on the inference of character questions, so this could be a group target for next term’s guided reading sessions).

If each year group completes the SATs analysis grids then the co-ordinator can consider whether there are common areas of difficulty or poor performance across the school, which could indicate a staff training need (e.g. the use of authorial voice)

  1. To return the sorted worksheet back to its starting point then click on the undo arrow at the top of the screen (next to the brush icon!).
  2. You can also sort the Fiction and non-fiction questions separately to compare how the children have performed on each of these sections. To do this – Point your arrow to the grey boxes at the left hand side and keeping your finger on the left button drag the mouse so that all the Fiction questions are highlighted.

Click here to highlight the line and then drag the mouse

Then lift finger, point arrow at Data and then Sort as above and the computer will just sort the fiction questions. Repeat for the non-fiction questions.

  1. Again click on the undo arrow to return back to the beginning.
  2. If you wish to keep this sorted set of information then click on File and Save as and then give the file a new name e.g. 2000 cohort Y5 reading - sorted

Health Warning:

Remember that this information should only be used as part of your whole information gathering and transfer process and not become all important!

Anita Yearsley May 2000