Reports: Part 1 - using a Google sheet (or other spreadsheet!)

 Using a Google Sheet (or spreadsheet) to Write Reports

It's that time of year when workload increases with report writing. Just this week I have had to write 180 two sentence reports (we also had Ofsted in this week and a year 7 parents evening...). Although I would love to write highly individualised and detailed reports it is just not possible if I want to also run my extra-curricular groups, keep on top of marking and planning and see my family at all. Therefore cheats have to be found! 

I have created two systems over the last few years, one using spreadsheets which I will go through here, and another using google forms, which I will write up at a later date. 

Set up

So the first step is to write a standard report that makes sense. For example I want to report back on the effort they put into lessons and a comment on their homework. I then want to give them a target that will help improve their attainment. An example would be:

In year 9 Harry has demonstrated excellent attitude to learning in History. He has consistently completed and submitted homework promptly. In order to make further progress they need to ensure they careful read and meet the assessment criteria.

Everything in bold here is interchangeable. I can change 'excellent' to other words such as good, or poor. 

Once I've got my sentence set up I can then input it into a spreadsheet, breaking it up between parts that will stay the same, and parts I want to be able to change like this:

My report has been split up into columns with spaces left for the interchangeable words

Adding options using drop down boxes

I then need to decide on my adjectives for each space. I like to put these on a different tab to keep the page uncluttered, but that is personal preference, you don't have to use a different tab. You can see my options in this screen shot here:

I want to be able to add these easily in my reports and therefore I add drop down boxes so I can just select the right word. This looks like it should be difficult, but is actually incredibly easy to create:

1. Go to the column you want to put the options in

2. Click on 'insert'

3. Select drop down box

4. Under the 'criteria' option select drop down from a range

Now you need to tell it what to look up. Go to where you have saved your list of words (for me this is on the other tab), then click on the box under 'drop-down' in your data validation section at the side:

Once you have your cursor there, select the text you want to be the options by clicking on the top box, pressing down your shift key and then down to the bottom. Another box will appear in the middle of the screen logging which cells you have selected. 

Back in your report you will now have a drop down box that allows you to select the options for your class. Repeat this for all areas where you want multiple options. 

Separating surname/first name

Once you have the top row set up it's time to put the names in. If your markbook has both first and last names in the same cell, don't worry. Copy and paste them somewhere in your reports spreadsheet:

1. Select the data option at the top

2. Select 'split text to columns' 

3. Either let it work out automatically, or tell it where you want to split, i.e. where there is a space, or a comma etc. 

Now you have a list of names you can paste into your name column of your report. 

Copying cells

To copy each of the comments for all of your class just click on the box you want to copy and then hover your mouse over the bottom right hand corner - a little cross will appear. Left mouse click and drag it down - it will automatically populate the following cells with the same data. 

A word of warning though - if you have a number in that column it will increase the number each time. For those columns I do just manually copy and paste - it's a little time consuming but I find that quicker than going through and editing the numbers back to what I want them to be. 

Creating a report with no awkward spaces

So now I can write my class reports by selecting the appropriate adjectives/genders for each of my class. If I were to just copy all the boxes and paste them into my report spreadsheet provided by my school I would have a problem though - odd spacing! This is where a formula really comes in useful - CONCATENATE

As you can see at the end I have a full report with no awkward spacing, that I can then just input into my exported spreadsheet from sims. 

Here is the formula I used to create my completed report - when I just want it to go to the next box I put in quote marks. When I want a space there, I put a space between the quote marks. 

=CONCATENATE(A2,"",B2," ",C2,"",D2," ",E2,"",F2," ",G2,"",H2," ",I2," ",J2)

Overview

So this is how I write on mass KS3 report. I hope it is useful and clear enough. Below is a video of me completing/explaining the process I use, so if you prefer to see it step by step do watch it. If you have questions, or suggestions on how to improve this method please do add them in the comments below. I am a firm believer that if we all work together we can reduce each others' workloads significantly. I certainly don't pretend to have all the answers, or to be an IT expert! These are just methods I've developed over my teaching career.

If you want some information on how to deal with tracking data click HERE for my tips and cheats.

Most of all, keep sane and enjoy that extra time you've just grasped back!

Dice