Office hacks: Creating a single-choice test with automatic scoring

In school, work and life, we often encounter electronic test papers. So, for the simplest single-choice electronic test paper, how to make it? In fact, we just need to use the Excel control form in the option button and simple settings, plus two simple formulas, you can easily complete the task of creating a single-choice electronic test paper.

First of all, in a blank Excel worksheet in separate lines to enter a single-choice question; switch to the “Development Tools” tab, click “Insert → Form Controls → Grouping Box”, in the first question below the drawing of a grouping box; click “Insert→Form Controls→Options button”, draw a radio button in the appropriate location in the grouping of question 1, select this radio button, copy, paste 3 copies in the grouping box; select these 4 buttons, top alignment, horizontal distribution; then select the grouping box of question 1 and all 4 options, copy, paste to the following (Figure 1).


Next, hide the “Grouping Box”. Switch to the “Start” tab, click “Edit → Find and Replace → Select Pane”, the list of all the “Group Box” are hidden at the beginning; right-click The first question of the first option, select “Edit Text”, enter the contents of the option, and so on for other options (Figure 2).


Next, create a new worksheet, labeled “Answers”, enter two columns of labels, the first column labeled “Question No.”, in turn, enter “Question 1”, “Question 2″_. “Question 2” ……; the second column labeled “Answers selected”. Right-click an option button in the first group, select “Set control format”, in the pop-up window, the “Control” tab, “Cell Link” at the selection “Answer” worksheet cell B2. After the other groups of settings similar to it, and so on. Thus, each group has four options button, when the first answer is selected, the linked cell will show the result “1”; select the second answer, the linked cell will show the result “2”; and so on, select four answers (Figure 3).


Finally, set the score. As the correct answer to the first question is “C”, that is, when you choose to show “3”, so in the “answer” worksheet, cell C2, enter the function ” =IF(B2=3,20,0)”, the following can be modified by themselves “=IF(B2=correct answer, correct score, otherwise 0)”; then, in cell B8 enter the formula “=SUM(C2:C6) ” on it (Figure 4).


Tip: set the unselected state: when all the steps are done, you should definitely try it first, after trying, to restore it to the “unselected” state. To do this, right-click on the option button that has been selected, select “Set Control Format” and choose “Unselected” in the Control tab.


Leave a Comment