Excel Picture Quiz 3

Right, so finally to stage 3, adding the counter and locking the cells. First choose where you are going to put the counter, and use the following formula;

=countif(B5:J17, "correct")

Again changing the italicised bits to fit your quiz;

B5:j17 - the range of cells where the answers are marked
correct - or whatever you have set your quiz to say for correct answers





The final stage is to set the cells you want people to put their answers into as unlocked so we can protect the sheet and stop anyone from checking the formulas to get the answers.

To do this either select all the answer cells using the control button, or one at a time right click and select properties, and uncheck the locked setting under protection











Finally protect the sheet; select 'Protect Sheet' from the Review ribbon, you can set a password if you wish. This will mean you can only select the cells we set as unlocked.

Once you have selected ok your quiz is finished.

I'll upload a copy of my incomplete version here for anyone to play with, please let me know if you have any hints or tips to make this better.

Thanks
Dan

Excel Picture Quiz stage 2

In stage 2 we get the quiz to mark the questions, there are several ways to do this however for this tutorial I am going to use an IF statement. This allows us to have two responses, correct and incorrect, these can be substituted for your own message.

I am going to have the answer in cell B4 and the responce in B5, so the code for B5 is

=IF(B4="Johnny Depp", "Correct", "Wrong")

the italicised parts are the ones you will have to change for each picture.

B4 - the cell reference for the answer you are checking.

"Johnny Depp" - the correct answer.

It is important to note that for the counter I am going to create later to work the 'correct' message must be the same for every picture.

Set the answer cells to change colour using conditional formatting, in Office 2007 this is to the right on the home tab.











Simply set the cell to change colour when the cell is equal to your 'correct' message.

Excel Picture Quiz

So I thought it was about time I put something on here and I thought what better than to start with some instructions for creating a simple and fun picture quiz in Excel.

The instructions presume you have some basic knowledge of Excel to start with, but if there are any steps which you want explaining in more detail feel free to contact me using the quick contact on my website www.zypher.co.uk

I will split this into the following stages / lessons

1. Set out the quiz
2. Add functions and conditional formatting
3. Add a score counter
4. block access to the answers

We start by setting up the layout, set the column widths so you have a thin column followed by a wider one and repeating across the screen, you should start and end with thin columns. The wider columns will hold the images so set these appropriately.

Next set the height of some of the rows to allow them to hold the images, leaving at least 2 rows normal height, this is where the answer and correct or incorrect message will go.

Set the background colour of the cells and add a title to the centre of your top row.

Insert your images into the larger cells and add instructions to the side, I have used a text box though you could just as easily merge some cells for this.