Result – Self Marking Spellings in Google Docs.

Whilst messing around today I suddenly thought to myself

“What if you could use the forms in Google docs to set up a spelling test that would mark itself?”

WHY?

  • To make life much easier for the teacher and save time marking repeated spelling logs.
  • To show any patterns in spelling mistakes.
  • To give the teacher more time to teach.
  • To allow children at home / absent to take the tests.
  • To possibly engage with parents to supervise tests at home and let the child take the test when they are ready and not use school time?

There seemed to be plenty of reasons why I should spend a little time trying it out and to be honest it wasn’t as hard as I thought it would be.   The setting up of the formulae was a little tricky and involved a bit of trial and error, but I got there in the end.

HOW:

  1. Open a new spreadsheet and save it.
  2. Open three sheets at the bottom.
  3. Click on the Share tab and choose fill out a form
  4. Write ‘Name’ as the first question then Question 1 etc.   This will put the children’s answers into the first page of the spreadsheet with their names.
  5. Rename this this ‘Answers Given’ at the bottom.
  6. Create two more sheets.
  7. Rename the sheets ‘Results’ and ‘Test Answers’ 
  8. NOW THE TRICKY BIT! REMEMBER YOU MIGHT HAVE TO CHANGE THE CELL REFERNCES
  9. On the ‘test answers’ sheet write the spelling answers one per cell HORIZONTALLY across.   Then select and drag to fill down the sheet for 30 children or more if needed.
  10. On the ‘Results’ sheet in the first column use a fourmula like =’Answers Given’!C1 to copy the NAMES column from your ‘Answers Given’ sheet.  If the name shows up drag the cell down to copy the formula.
  11. In the next column put in a formula like this =IF(‘Answers Given’!D2=’Test Answers’!A1, “1”, “0”)   This just means if the answer in ‘Answer given’ matches the answer in ‘test answers’ then score it 1 if not 0!
  12. Copy across for all the answers.
  13. Finally in the last column of the ‘Results’ sheet put Total and put in a formula like =COUNTIF(B2:K2,”1″) This just says add up the previous row if there is a 1 scored
  14. SELF MARKING SPELLINGS DONE!

I know that this looks horrific to do, but trust me I’m not very good at these things so if I can do it everybody can.   I’ve trialled it with my class and it really does work!

LESSONS LEARNT

  • Google docs can be used in creative ways to help reduce workload.
  • Doing this for spellings will allow the teacher to spend much more time on teaching not marking.
  • You could also use the embe
  • The possibilities of sending out linked exercises etc with the email to support the learning is an exciting possibility.

I’m also looking into other areas where this sort of collated marking could be useful and will post as soom as I have thought it further through.   If you want to see it in action then leave your email and I’ll invite you to have a look.

 

13 thoughts on “Result – Self Marking Spellings in Google Docs.

  1. Hello Steve, I have finally found your blogging space through your tweet. Thanks for sharing this one. I really like working with google docs and this just adds another dimension to its powerful ability. Do you ever work with tombarrett as he does a lot of work with google docs as well?
    Another great feature that I have used for collaboration and ease is that of the google forms.

  2. Nice one. I Haven’t played with google docs too much but I used to do this in Excel and I guess the two are getting pretty similar these days.

    One variation for younger kids is to use the Wingdings font for the marking column and display a smiley face if they get it right and sad face if wrong ( I think J and K) then do a countif on the J’s to see how many correct 😉

  3. Pingback: Result - Self Marking Spellings in Google Docs. | Gearfire.com

  4. Pingback: 10 Google Forms for the Classroom | ICT in my Classroom

  5. I have just found your using gdocs to grade spelling tests.

    I love it.

    I have been playing around and have added some things you may think is interesting.

    I have it put a 4 for a correct answer to go along with my point system. I also have it put a 1 for Bonus words to go along with my system.

    If a 4 or 1 does not show up then I have the sheet show the word the way the student typed it.
    I also have a formula to count how many times a word was spelled correctly.

    Just some ideas…
    See here…
    http://spreadsheets.google.com/pub?key=p5M3ruvf9-maSSwwQsUWoaw

  6. Thanks Kevin, some great additions. The thing I love about the internet is the way that people can always improve on you idea in ways that you couldn’t think of yourself!

  7. Pingback: A New Dawn in Salford | Teaching with Technology

  8. I have taken this to a whole new level.

    http://spreadsheets.google.com/pub?key=p5M3ruvf9-mZ1RNpwELyeNA&gid=14

    I have a list that is easy to input and that “goes to” the key.

    I now have the students give a “secret” word and they use that to immediatly see their results.

    I have each word each word evaluated and graphed with a tug O War.

    I have color coded so I can immediately see A’s and over 100’s!

    It is GREAT! The best thing I have done in my room in a long time!!!!

    Thanks!

  9. A great idea and one I’ve started to try. Any idea if this would work with individualised spelling tests? I want students to create their own lists and an audio file of each word. From there, they can click on the audio file for each word.

    Cheers,

    Mark

  10. Pingback: 10 Google Forms for the Classroom « edte.ch

  11. There is another way you can do this without any formulas for those who are scared off by them.

    1. highlight a column by clicking on the letter
    2. Hold down the Ctrl button on the keyboard and press the F key.
    3. Click on the “replace” tab.
    4. Type the correct answer in the “find” textbox
    5. Type a 1 in the “replace” textbox.
    6. Click on the “replace all” button.
    7. There is no need to replace incorrect answers with a 0 because they will not be counted.
    8. Continue steps 1-7 for each column. (If the questions were multiple choice, you can do several columns at once. Select the first column and then hold the Ctrl button down as you click on all columns with the same correct answer. You can then find and replace all correct answers in those columns with a 1 at one time.)

  12. Please email me about the use of Google Forms for spelling. I created one and it worked great, but what do I do for the second test? Do I delete the old rows?

Leave a Reply

Your email address will not be published. Required fields are marked *