**UPDATE Sept 23:
- 2023 Scaled score conversions have been added.
- Half term headings have been removed so you can use any paper in any order you wish.
- Space to enter your school’s targets on Summary sheet
As mentioned in my previous blog (Year 6), here is the Excel data sheet, free for your school to use. The sole purpose of this design was to make data input easier for teachers whilst providing all the mock SAT’s data for leaders to analyse over time, as well as a handy overview in prep for the big day!
There are 4 files required (see Downloads at the bottom):
- Overview – SLT’s edition;
- Reading – Teacher edition;
- Maths – Teacher edition;
- and GPS – Teacher edition.
SLT’s edition
Scores tab
This will contain all the scores for all the tests across the year.
In this workbook, you will need to input all the pupils’ names, class name or number, KS1 data and additional contextual information e.g. PP, SEND, and EAL. I would suggest arranging the children in alphabetical order for each class just for ease.
The above information will automatically transfer to the Teacher edition workbooks.
Once the Teacher edition workbooks calculate the scaled scores, you will need to input these directly onto this overview sheet – it will calculate the percentages as soon as the data is inputted.
The writing data and the official SATs’ data will also need to be inputted directly onto this sheet. Once you have all the data, in the combined column, you will need to enter the Y (Yes) or N (No) whether they have achieved combined or not; it will then work out the percentages for you. Note – the GDS combined will need to be worked out manually.
Each cell must have a number inputted to calculate all pupils. For example, if you have 30 children in the class and all 30 children are meant to take the test, then even if they were absent, you MUST give them a score. On this overview sheet, input a 0 and this pupil will be used in the calculation.
If you do not want a pupil to be calculated for your percentages, then leave the cell BLANK.
This is useful if one class has 20 children for example, then leave the other 10 cells blank and it will only calculate the percentages for the 20 children you have data for.
You will notice that the headings are colour-coded. Each colour code represents 1 half term’s worth of tests. For no particular reason, I have designated the following:
- 2017 test to Autumn 1;
- 2018 test to Autumn 2,
- 2019 test to Spring 1;
- and 2022 test to Spring 2.
The yellow headings stand for predicted. This is where you may wish to update your predictions or targets across the year.
Summary tab
This tab does exactly what it says. It automatically provides the percentages for each subject, of each class, for each term, and the overall cohort percentage.
End of Year Final tab
This is for official SATs only. It saved us so much time understanding our results once we inputted our data. You will need to input the scores and Writing data on the Scores sheet and then the overall percentages will be shown on this page.
Teacher edition
Use only Class 1, Class 2, Class 3 and Class 4 tabs
Once the SLT edition form is initially completed, teachers will be able to see the pupils on each of the Reading, Maths and GPS workbooks.
All that is required here is for teachers to input their raw data scores for each part of the Mock SATs e.g. raw score for Arithmetic Paper 1, Reasoning Paper 2 and Reasoning Paper 3, and that’s it! The scaled scores are worked out for you already. These scores now need to be inputted on the SLT overview sheet to be calculated.
Note – for no particular reason, the scaled scores conversions are for 2017 onwards.
For these documents, if you do not want any pupils or cells calculated in the percentages, then leave the cells blank (only the total column should say 0 which will make the scaled score column blank).
I hope this proves useful and saves you and your colleagues some valuable time.
Downloads
Using the link below, download all four files. To download, either click download in the top left corner, or highlight (by clicking in the space and dragging the cursor over the four documents) all four excel documents > right click > download.
In your download folders, it will save to a folder called Onedrive. From here, you will be able to drag this folder where ever you need it and all four documents (still within it) should still correspond when you open them all.
Click “Grant Access” when prompted.
Click “Update” if prompted.
If you find any issues or there is anything I can be of help with, feel free to get in contact.
Make a one-time donation
Make a monthly donation
Make a yearly donation
Choose an amount
Or enter a custom amount
Your contribution is appreciated.
Your contribution is appreciated.
Your contribution is appreciated.
DonateDonate monthlyDonate yearly
Teacher Out Loud 
Great resource but unfortunately the data doesn’t transfer over from the SLT editions onto the teacher editions.
LikeLike
Hi Paul, thanks for your comment. I’ve heard from many others who have downloaded that it works so it may be an update issue.
Try downloading the whole pack again and when you open the documents, it may ask for it to be updated. If it does, click update and hopefully it will link
LikeLike
Hi I’ve tried to download the file but it is saying it is no longer available. Please can you help?
LikeLike
Hello Lucy, I’ve just double checked and it all seems available and downloadable. Click the link under the download section. It’ll take you to one drive where you can select the files. Also try accessing the site via a different Web browser.
LikeLike
Hi, do you have an updated version for the 2023 SATs please?
LikeLike
Hi Mariam,
From the KS2 data release today, you can input the scaled scores directly from the gateway onto the last columns that say SATs Reading etc. It will then calculate it for you.
LikeLike
Thank you, unfortunately it won’t let me do that due to the password protection.
LikeLike
I’ve checked it from my end and I don’t have any restrictions on the final columns of the Y6 overview sheet, as the SATs data should be directly inputted on there, not the individual subject sheets. If you’re still having problems, I’m happy for you to send it to me via twitter or Facebook and I’ll take a look for you
LikeLike
I’ve downloaded the resources ready to use this year (we love it!) However, it keeps saying links don’t work and the SLT version doesn’t communicate with the teacher edition and vice versa. I have tried downloading it again but getting the same problem. Thanking you in advance for your help!
LikeLike
Hi Kate, thanks for getting in touch. I have had a look into this for you and have updated the files. Please try and download it again (all files downloaded together) and let me know how you get on. I have also updated the columns with the 2023 scaled score conversions too 🙂
LikeLike
Hi I have downloaded it but unfortunately can’t change it from protected view as it is asking me for a password. Am I doing something wrong?
LikeLike
Hi Chris, you shouldn’t need to change the view as the sheets function together. You just need to input the data for the conversions and transfers to do it for you.
LikeLike
Hiya thank you for your reply. I was trying to input the data straight onto the SLT tracker. Is that where I’m going wrong?
LikeLike
Yes, you need to input data into the subject data sheets – this will then transfer to the SLT edition.
For more info, take a look at the Year 6 Data Tracker blog.
I hope this helps and proves useful for you.
LikeLike
Is it possible to unprotect the sheets so that the scaled scores can be copied into Insight which we use for recording assessment results?
LikeLike
Hi Matt, are you referring to be able to copy the scaled scores that transfer to the overview sheet?
LikeLike
Hi-yes I would like to be able to copy the scaled scores but can’t because the cells are protected
LikeLike
Hi Matt, I have amended so you are now able to copy the data. You will need to re download the files now.
LikeLike
Hi Matt, I have amended so you should be able to copy now
LikeLike
On class two, the 2017 data transfers to 2018 on the SLT overview page. For reading and duplicates on the maths and GPS
Thanks for this though, I love it!
LikeLike
Hi Nick, Thanks for bring this to my attention. I have now fixed it so please download again and let me know how you get on.
LikeLike
Hello, I have opened the spreadsheet and went to insert pupils names and they are locked? Can you advise me on a way forward for this? Thanks.
LikeLike
Hi Rhi, have you tried adding the names on the SLT overview sheet? It’ll transfer them over to all the other sheets
LikeLike
I love this but …..
I have re-downloaded the most recent version but I have 2 queries.
1. I don’t seem to be able to to get any reading scores to transfer across to the SLT version, it just says REF in every cell. Maths works fine.
2. On the reading teacher sheets I am able to remove the pre-filled zeros which would presumably prevent a row from being in the calculations for a class smaller than 30 (though I can’t check this dues to problem 1 above). However on the maths teacher sheets, although I can remove the zeros for individual papers for rows I do not want, I cannot remove the zeros in the total column and so the unwanted rows still seem to be counted into the summary maths.
LikeLike
Hi Alyson, can you download them again please? I have downloaded them and it is all working fine for me, on three separate devices. Are you downloading them all at the same time and saving them in the same place? Is your Windows up-to-date as well?
Thanks
LikeLike
Yes I downloaded all files to same folder. I will check with IT that Windows version is up to date then download again.
LikeLike
Hello,
I have downloaded your spreadsheets today and am loving the 2023 update. However, on the SLT’s edition, it comes up with “#REF!’ and states that it is locked and we can’t input their scaled scores.
LikeLike
Hi Kate, have you downloaded all documents first and saved them in the same folder? And do you have the latest version if Microsoft? Because it will ask you if you want to Update the sheets when you open each of them. And then you can enter your data on the subject excel sheet – this will then transfer it to the SLT edition.
LikeLike
Hi,
Thank you fr creating this helpful resource! have input the data for our 2 year 6 classes but it seems as though the table is calculating the percentage as if we had 120 students. Is there something obvious I am doing wrong? Thanks
LikeLike
hello, you mentioned the table, which table are you referencing on which document?
LikeLike
Hello, thanks for your response. The issues I am having are with the ‘Year 6 data overview – SLT’s edition’ There are two issues – For all the Maths papers it is showing a #REF! error for Pupils 61 – 90.
Also, the summary tables at the bottom are not calculating EXS for the cohort just the individual classes. Hope this makes sense?
Thanks,
LikeLike
Hi Afua, is this still happening?
LikeLike