[Rbreb13]

A Self Test Spreadsheet in Excel

Here's a simple way to create a self-test spreadsheet. Enter all the questions in column A, leaving the first row for column headers. Enter the corresponding correct answers in column B. The cells in column C are the users' input area. Select only the input area, right-click, and choose Format Cells. On the Protection tab, remove the check next to Locked and click on OK. Right-click the cell header of column B and choose Hide from the pop-up menu. Add appropriate column headers in row 1.

In cell D2, enter the formula =IF(C2=B2,"YES",""). Copy that formula down column D for all of the question rows. Below the last question row—still in column D—enter the formula =COUNTIF(D2:D##,"YES"), where ## is the number of the last question row. Finally, choose Tools | Protection | Protect sheet from the menu, uncheck all the boxes except Select unlocked cells, and click on OK. Enter a password when prompted, then save the sheet—and you're done!


You can now give copies of this spreadsheet to others to use as a self-test. As soon as a user enters an answer that matches the hidden correct answer, the adjacent column will display YES. Uppercase and lowercase distinctions don't matter, but a user must enter the precise words or numbers that you defined as correct.