USD Scantron Exam Summary Facility

The Excel Workbook Scantron Summary.xlsm will import student scores from the Scantron Scanner and provide a variety of useful statistics and tools to help instructors. Changes can be made in the key after scanning is completed, multiple answers can be marked correct, and student answers can be altered if there are errors on the scantron sheets. Scores are updated automatically after changes are made. Scores can be cut and pasted into another workbook, thereby avoiding the time and potential errors of entering them manually.

This is a Microsoft Excel 2007 spreadsheet and will only run on Office 2007. Commands to run the program are present on the Ribbon under the Scantron Summary tab. New worksheets can be created to hold scores for as many exams as needed. The workbook can also be renamed if desired.

To run this spreadsheet, macros must be enabled in Excel. If you get a Security Warning upon opening the workbook, you will need to open the Options window and select "Enable this content." You may have to open Excel Options and change your security settings to allow macros.

Download Scantron Summary Workbook
This workbook can be downloaded to any location you desire.

Download a Sample Exam File
This sample data file can be used to test-run the workbook.
Right click the link above and choose "Save Target As..."

This facility is available because USD's new Test Scanner provides for electronic output. The output is a text file containing student ID numbers, answers, and scores. Elaine Johnson (Slagle Hall 18B, 677-5267) runs the scanner and can provide the output file via e-mail or floppy disk. Be sure to ask her for this when taking the scantrons to be scanned.

Remember that the new scanner requires that students enter their Student ID numbers on the scantron forms. When you prepare the exam key, fill in the ID number on the key with all zeros. This is necessary for the key to be included in the output file.

The Excel workbook has three buttons on the Scantron Summary tab. Press the Import Data button to select the data file and import it into the active worksheet. The student ID numbers, answers, and raw scores will fill the sheet. Then press the Analyze Data to run an analysis of the exam. Scores, summary statistics, and a table of answers will be generated. Check the table of answers (in blue) for evidence of errors in the key. Any answer that is more popular than the correct answer will appear in red--signaling a possible error in the key. Also check for multiple or missing answers in the student data. Any desired corrections can be made on the spreadsheet, and the scores will be immediately updated. If you decide to offer credit for more than one answer, simply enter the letters of all acceptable answers in the key. You can press the Analyze Data button again to completely refresh the output.

There is no feature for directly entering the student scores to another grade book other than Excel's block and copy/paste commands. If you block and copy the colums of student ID numbers and scores to paste into another workbook, be sure to use right-click the mouse and use the Paste Special and Values options so that the scores are pasted rather than the formulas.

The Info/Help button provides a summary of the program's features and a key to the summary statistics.

© 2007-2009 Timothy H. Heaton, Department of Earth Sciences, University of South Dakota