Quizzing and random question selector

UPDATE 8/10/17 A 10-question version is available here:



Reposting here so there is a permanent page for my quizzing Excel file and any updates over the year.

I would be interested in your feedback on how it works out in the classroom.

Good luck all for the new academic year.




This spreadsheet helps to organise questions, and generate quizes.

The intention is for each lesson, a quiz of six questions is used: three new questions based on the last lesson’s learning, and three random questions from all previous lessons.

For each new lesson, type in the next three numbers in column E, e.g. 013, 014 and 015 for Lesson 3. Then move to the ‘Questions’ tab and type in your questions and answers in the appropriate row.

You can pre-populate your questions in the ‘Questions’ tab – as long as you haven’t referenced future questions in your Planner (column E), they won’t be selected in the random question generator.

To select the three random questions, press F9, which will generate three random question numbers  (yellow box) (up to the most recently used question in your planner). Type, or copy, these into the remaining  three rows for the lesson. (Note if you copy /paste from K2-4, you’ll need to  use Paste Special/Values).

Note – the random question selection includes an aspect of weighting. All questions are weighted such that the more often and more recently a question has been used, the less likely it is to be selected in the random question generator. This should help improve the coverage of all the questions over the year.

The ‘LessonQs’ and ‘LessonQ&A’ tabs will auto-update with the relevant questions and answers (i.e. the last six in the Column E). This can then be displayed to the class.

Separately, a completely random six question quiz can be made with the ‘6RandomQs’ and ‘6RandomQ&A’ tabs – just press F9.

Separately, you can generate a quiz based on six selected questions by filling in  the ‘Six  selected question’ cells (orange) and the ‘6SelectedQs’ and ‘6SelectedQ&A’ tabs will auto-update .

(Note for any Excel-aficionado – the coding is a massive bodge-job – please don’t judge too harshly! I will tidy it up in a later version if this proves useful.)


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s