How to make a test in Excel

Tests are good because students quickly demonstrate their level of training in a particular field of knowledge. Teachers have, like dozens of years ago, to spend time on manual processing of results. To automate the process and relieve the teachers using the program Excel.

Instruction

Prepare three questions and answers to on a small amount of data to learn the preparation of the test. Having dealt with a simple example, similarly you will be able to use a spreadsheet Excel for the development of complicated options.

In the Excel worksheet, merge multiple cells, which would be the first question of the test. Fill this area with some color that turned out beautifully.

Type into the cell prepared the text of the first question. Then right-click on the cell and on the shortcut menu, select "Format cells". On the Alignment tab, specify how to put the text horizontally and vertically. For example, in vertical direction you can select "center" so the text is not pressed against the top or bottom edges of the cell. Check the "wrap text" and click "OK".

Combine several cells in order to obtain the space for the answer to the first question of the test. This place is also fill with any color to make it look harmonious.

Copy the mouse prepared the cells for the first question and answer. Paste twice on the Excel sheet to place under the second and third questions and answers. Type in the right places lyrics the remaining issues. Design of finished.

Select the cell where you want the answer to the first question of the test. In the top horizontal menu of the program select "Data" and "Verifying...". On the tab "Settings" dialog box, specify the data type "List". Place a checkmark next to "Ignore blank cells" and "a List of valid values". In the "Source" list all possible answers to the first question, separating them with a semicolon. The student will have to choose the one correct answer.

Similarly, in the cells create the lists with the answers to the second and third test questions.

On a separate sheet, use the logical function "IF" to automatically calculate the results. Set up function arguments for this in the "Logfilesize" specify the cell from which it got the answer. In the "Znacheniya", specify in quotes, the word "Right." In the "Znacheniyakh", specify in quotes, the word "Error". Do this separately for the three answers to the test questions.

To count the total number of correct answers, use the logical function "COUNTIF". Configure the arguments to this function. To do this in the field "Range" select all the cells with the answers to the questions. In the "Criteria" you specify in quotes the word "Right."

Save the results and check how well the program counts the number of correct answers.

Is the advice useful?