All versions of Excel offer a function called "IF" which lets the program automatically determine the result for one cell on the spreadsheet based on the contents of another. These formulas are easy to write and can be powerful. You can use the IF function in Excel to score tests, for example. When doing this, all you need to know is the answer to each test question and the number of points you wish to assign to each question, in the case that some questions are worth more than others.
Select an empty column in the spreadsheet to the right of the exam questions and answers submitted by your students. This will be the column you use to build the answer key with which Excel will check the students' answers.
Identify the cell reference for the student's answer to the first question. For example, if the first question is in the second row, row B, and the answer is in the second column, the cell reference is "B2."
Type an equal sign into the first cell in this column for the first question or row on your spreadsheet. This begins the formula. Then complete it as follows:
In this case, B2 is the cell reference for the student's answer, and "answer" is the actual answer that is correct for the test question. If the student answered correctly, this example provides a point value of 1. If not, they get 0 points for the question.
Type a similar formula in the same column as the first formula for every question on the test.
Click in the cell under your last formula for the last question, and then click the "Sum" button on the Excel toolbar. This will create a total points value the student earned.
Click in any cell on the spreadsheet in which you wish to display the test score. Press the equal sign, then click on the cell that contains the Sum formula. Then, press the divide button on your keyboard ("/") and type the total number of questions in the test. Press "Enter."
Right-click on the result and choose "Format Cells..." Select the "Number" tab, then "Percentage," and click "OK." The result is the percentage score the student earned on the test.
Tips & Warnings
- Using Excel for scoring tests can make life easier on you, but also helps students build computer skills. The students must interact with Excel to input and answer the questions, which offers an extra benefit to their education.
- Once you complete one test spreadsheet with your desired formulas, you can copy the entire column of formulas and paste them into other test spreadsheets, so you do not have to retype them again.
- Don't include these formulas in the actual test spreadsheet that your students see, as the formulas themselves contain the answers.
- Photo Credit Stockbyte/Stockbyte/Getty Images