Retrieval Roulette – Quiz and Game Generator

Last year Adam Boxer shared a simple but highly effective spreadsheet to randomly generate short quizzes (which include questions from the current topic being studied as well as questions from previous topics) from a list of pre-set questions. The link to his original ‘Retrieval Roulette’ post is here, and is well worth a read.

With his permission I’ve taken his original spreadsheet and added templates that create boards for games of Battleships, Blockbusters and Connect Four.

You can download a copy of the Quiz Creator here. The ‘Questions’ tab is currently filled with questions for the IGCSE course I teach (interwar politics, Cold War and a Germany 1918-1945 depth study) but you can replace them with your own questions in the order in which you cover them through the course.

To create quizzes:

Add questions and answers to the list on the ‘Questions’ tab.
Set up your question range:
- Enter the question number you have covered up to in cell F1
- Enter the first question number of your current topic in cell F2
- Enter the last question number of your current topic in cell F3
Click the tab name (e.g. Battleships 6×6) for the questions to be randomly populated into the template grid
Press F9 on any tab to refresh with a new random set of questions. There are occasional repeats within the same grid – I’m currently trying to work out a way to stop this, but my students actually quite like it.
When you go to print the template you will get two A4 sheets – one with the questions and one with the answers. The printed template will also feature a random Quiz ID so that you can match the questions and answers in case you produce numerous quizzes.

Using a macro to create and print multiple random quizzes

You can use a macro to automatically create and print a certain number of random quizzes. It only takes a couple of minutes to set up and allows you to produce unlimited random quizzes quickly and easily:

Load the spreadsheet and go to the VBA editor (press alt-F11)
Insert a new Module (menu Insert, select Module)
Copy and paste the code below (substitute the text-like variable called number_of_desired_copies with the actual number of copies you want to produce):

Sub print_random()

For i = 1 To number_of_desired_copies

Application.Calculate

ActiveSheet.PrintOut

Next i

End Sub

Now just press the ‘play’ button to run the macro and hey presto 🙂

Comments

13 responses to “Retrieval Roulette – Quiz and Game Generator”

Alison Ackroyd

Thank you – I have adapted for T level Science. It is utterly brilliant. Do you have any media links I can credit you to when I share?

Alison @MidKent College

Reply
Michael

Hello, This is fantastic. Thank you very much for sharing. I am going to use it in economics. I was wondering if you could tell me how you run the blockbuster and connect 4 games?

Reply
Laurence Seltzer

Wow this is great! Thank you so much for sharing this. I am entering my info now. I teach remotely and I am not sure how I can share these on my LMS (Schoology) so my students can play in pairs. Any advice?

Reply
1. Mr Allsop
  
  You will need to export them to PDF and upload those, assuming the students are able to annotate PDFs on Schoology (it’s a platform I don’t know so can’t help with that I’m afraid).
  
  Reply
Julia Morris

Thank you so much this is such an amazing resource. I’ve adapted it for my German learners now and am spreading it far and wide on facebook and twitter, every teacher should now about this! I’ve also found a way of inserting it into a Genial.ly presentation so students can play it on their own devices without needing excel: https://view.genial.ly/5f849b209d6d260d614bf9b8/interactive-content-revision-roulette
Here is a version that combines it with a digital boardgame: https://view.genial.ly/5f8591e04977856eccca60b9/game-retrival-roulette-board-game-2

Reply
Uther Pendragon

Hi. Fantastic resource, thank you. Just so I understand, if I download an insert some of my own questions these will be placed into the various games on the spreadsheet? I’m not to savvy at this sort of thing! Please could you help me? Thanks!

Reply
Joel Thorpe

This is fantastic! Thank you!

Reply
Jayne Linighan

Had some CPD today and this was introduced. A complete time saver as am forever writing questions for the start of our lessons.

Thank you very much.

Reply
1. Mr Allsop
  
  Fantastic! I’m glad you’ve found it useful. Please do share back any completed question sets or pass them on to Adam Boxer – he’s got a collection of them on his website.
  
  Reply
Cathal Molloy

I like the idea of the battleships. How do you get them to use it?

Reply
1. Mr Allsop
  
  Two players take a different grid each. They position their boats on the big grid (with the questions) – I suggest one boat measuring each of 1, 2, 3, and 4 squares. Then they start playing. Player 1 chooses a grid square. Player 2 reads the question. If Player 1 answers correctly, Player 2 reveals whether it is a hit or a miss (this is then recorded by Player 1 on their miniature grid). If incorrect, Player 2 doesn’t tell them whether it’s a hit or a miss and the square can be played again. Then play switches to Player 2. They continue to take turns until one player has hit all parts of their opponent’s ships.
  
  Reply
  1. Cathal Molloy
    
    Thanks for you reply. That is excellent.
    
    Reply
Carolyn Evans

Thanks, this looks great, I’m going to give it a go!

Reply