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

Application.Calculate

ActiveSheet.PrintOut

Next i

End Sub

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