Jump to content

A Self Test Spreadsheet in Excel


Recommended Posts

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.

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.


  • Create New...