Retrieval Roulette – Quiz and Game Generator

Random Quiz 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:

  1. Add questions and answers to the list on the ‘Questions’ tab.
  2. 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
  3. Click the tab name (e.g. Battleships 6×6) for the questions to be randomly populated into the template grid
  4. 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.
  5. 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:

  1. Load the spreadsheet and go to the VBA editor (press alt-F11)
  2. Insert a new Module (menu Insert, select Module)
  3. 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



Next i

End Sub

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

6 Responses to Retrieval Roulette – Quiz and Game Generator

  1. Jayne Linighan says:

    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.

    • Mr Allsop says:

      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.

  2. Cathal Molloy says:

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

    • Mr Allsop says:

      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.

      • Cathal Molloy says:

        Thanks for you reply. That is excellent.

  3. Carolyn Evans says:

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

Please leave a comment below

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.