Just for fun - How I created my Excel Christmas Quiz

Before Christmas you may have seen my Excel Christmas Quiz which you can still download here (although the questions might no longer be appropriate for the season, the prize is still valuable until the end of January).

I thought it might be useful in this post to tell you how it was made.

This post will make more sense if you download the spreadsheet first.

The Quiz

The quiz element is pretty straight forward. There are 3 hidden columns alongside the spaces for the answers. One column contains the correct answers (column T), another column (column S) contains a nested IF statement as follows:

(for the first question - row 5)  =IF(R5="","",IF(R5=T5,"Ho Ho Ho","Grinch!"))

which returns a blank cell if no answer is entered, returns "Ho Ho Ho" if the answer entered is equal to the correct answer and "Grinch" if not. Cheesy I know, but it was Christmas.

The third column (column H) contains another IF statement:

(again for the same row) =IF(R5=T5,1,0)

which returns a 1 if the answer is correct or a zero if not.

This third column is summed for the score as follows:

=SUM($H$5:$H$14)&"/10"

This takes the sum of these 1s and 0s and adds the text "/10" after it. See my post on manipulating text in Excel for more detail on this.

The revealing of the Christmas picture


This was done using the background facility in Excel and a series of IF statements combined with offset formulae and conditional formatting.

I first set the background image to be the Christmas picture using Format -> Sheet -> Background. As this tiles the picture in the background and gives no facility to re-size I needed to use some trial and error to get the size I wanted and had to have the image in the top-left to ensure it stayed in the right place.

I then needed to choose a background colour for the rest of the sheet so that the picture was not repeated across the whole sheet - for this I simply formatted the cells with that fill colour (in my case green). It is easier to apply this for the whole sheet and then remove the colour from the cells that hide the picture.

I then created on a separate sheet (hidden) a grid of the same number of cells as the picture, containing random whole numbers between 0 and 9 in each of the cells, representing the 10 questions. This will be used to determine which cells are revealed for each question. I actually used the random number generator to create this grid using the following formula:

=ROUNDDOWN(RAND()*10,0)


RAND() returns a random number between 0 and 1, multiplying this by 10 therefore returns a random number between 0 and 10 (but not necessarily a whole number). The ROUNDDOWN formula rounds down to the number of decimal places specified in the second argument (in this case 0). The result of the whole formula, therefore, is a random whole number between 0 and 9.

Because the RAND formula recalculates on each calculation the spreadsheet does, I needed to fix these numbers as I wanted the cells revealed by a question to stay revealed, not be re-scattered across different cells every time an answer is entered. The simplest way to do this was to copy the grid of numbers and re-paste it over the top of itself using 'paste as values'.

Going back to the main sheet I entered the following formula in the top-left cell of the picture (A1):

=IF(SUM($H$5:$H$14)=10,"",IF(OFFSET($H$5,Sheet4!A1,0)=1,"",1))


The objective of this formula is to return an empty cell if the corresponding question is correct and a 1 if not. I'll explain shortly what we do with this result.


The first IF statement returns an empty cell if the sum of the scores is 10, so all cells are empty if all answers are right. If the total score does not equal 10, the second IF statement checks whether the score of the relevant question is correct (i.e. 1 in column H) and an empty cell if not. The offset function is used to determine which question to look at. $H$5 is the first score (with dollars to fix this reference) but the row number is offset by the number in the corresponding cell in our number grid (on Sheet4). The zero is the number of columns to offset. For example, if the number in the grid is zero, the offset formula will look at cell H5, i.e. the score of question 1, if it is 9 it will look at cell H14 (5+9) and return the score for question 10.

This formula is copied to all cells on the picture, leaving the dollars off the Sheet4 reference so that in cell A2, it looks at cell A2 on Sheet4, etc.

We now have empty cells corresponding to correct questions, which is right as we want an empty cell with no fill as the picture is revealed. For incorrect, or not yet completed, answers we have a 1 in the cell. We use conditional formatting to use this result to hide these cells.

First of all, set the font colour of these cells to the background colour of the rest of the sheet (green in this case). We now have a green "1" in front of the picture for incorrect answers.

We use conditional formatting to choose a green background for these cells if they contain the number 1.

In Excel 2003, select Format -> Conditional Formatting, in Excel 2007/2010 you need to add a new rule. In either case select < Cell value is > < equal to > < 1 > for the three boxes and click the format button to choose the green fill.

Now the picture has a green background until correct answers are entered to gradually reveal the picture.

Revealing the prize


The final step is a much simpler version of revealing the picture, that is only dependent on the total score.

The background of the prize box is set to yellow using the normal cell format and the font colour to whatever you want it to be when it is revealed.

We then use conditional formatting again but enter the following for all of the cells in the prize area:

< Formula is > < =$R$16<>"10/10" >

setting the format to a yellow font colour. This switches the font colour to yellow if the score in cell R16 does not equal 10/10, making the text invisible on the yellow background.

Protecting it all

Finally, we need to protect the sheet so that none of this information can be seen or edited.

Cells are formatted as "Locked" by default, so we need to select the cells that the user needs to be able to use (just the 10 answer boxes), select Format->Cells and on the Protection tab, un-tick the "Locked" box.

We then protect the sheet as follows:

Select Tools->Protection->Protect Sheet

Enter a password so that users cannot switch off the protection and un-tick all boxes in the list except the one that says "Select unlocked cells". Once you have clicked OK, the user cannot even click in any of the cells apart from the 10 answer boxes.

There you have it! Your very own Excel Quiz.


If you enjoyed this post, go to the top left corner of the blog, where you can subscribe for regular updates and your free report.