Create Questionnaires and Quizzes in Microsoft Excel

Marking questionnaires and quizzes is much easier if you can restrict the answers that people can give. If you create the quiz in Excel it can provide a list of possible answers and refuse any alternatives using a technique called data validation.

Once the quiz has been created, make a list of possible answers for the first question in a column off to one side of the quiz so it does not distract.

Then, move the cursor to the cell that will contain the answer.

Click on the Data menu and then Data Validation.



Click on the Allow dropdown menu and select List.

Click on the small icon on the right of the Source box, select the list and click the icon on the right in the Data Validation window that appears.



Click in the box labelled ‘In-cell dropdown’ so that the tick disappears to make the quiz more challenging by not providing the answers.



You can also prevent people peeking at the answers by putting them on a separate worksheet. Select the sheet, click on the Format menu sheet and then Hide.