Self-assessment and colour coded spreadsheets

An easy peasy guide to using student self-assessment checklists and colour coded spreadsheets to inform revision

Step 1: Create a checklist of success criteria for your specification (or just for one topic)

Step 2: Put it in excel and align the text as below so it rotates 90° (so you can see more of the success criteria across the screen)

Step 3: You can merge the cells above several success criteria to give it a topic title

Step 4: You need to conditionally format the cells so that they will change colour when you enter a number

Type '1' and select custom format

Select the 'fill' tab and choose green, then go to the 'font' tab and choose green as well

Then repeat step 4 typing '2' and selecting orange/amber and typing '3' and selecting red. When you type 1 in the cell it should go green, typing 2 turns it orange and typing 3 turns it red. Copy the empty cell across any others that you want this conditional formatting to apply to.

Step 5: Give students a printed (or electronic) copy of the checklist and ask them to rate themselves 1 (I completely understand), 2 (I need some help), 3 (I don't understand this)

Step 6: Transfer the student 1, 2, 3 ratings into the main spreadsheet

Step 7: Total all the numbers (red arrow) - a higher number (black arrow) means more students are struggling and so this would be a useful area to revise

I use a similar style of spreadsheet to keep track of whether student have completed homeworks, handed in books, etc.

It can also be used to track students as they complete work (I've used it to motivate BTec students to complete parts of assignments - when they start the work or get their first bit of feedback you can change it from red to amber and then change it to green as they complete it).