How can I get my students’ answers to concept inventories into electronic spreadsheets?

posted February 10, 2016 and revised January 12, 2023
by Sam McKagan, PhysPort Director

If you are using multiple-choice concept inventories such as the FCI, BEMA, or CLASS to assess your students’ learning, the PhysPort Data Explorer can help you get instant analysis and visualization of your results. In order to use the Data Explorer, you’ll need to have your students’ responses in some kind of electronic spreadsheet, such as an Excel or .csv file. We’ll take these files in almost any format*, as long as it is an electronic spreadsheet. We’ve learned from faculty that getting their students’ responses into electronic format can be a challenge, so we’ve written this article to provide tips for addressing this challenge.

Scanning Services

Most large universities have a scanning services office that will take your scantron sheets (along with an answer key) and convert them to electronic spreadsheets. If you are lucky enough to have one of these offices, this is the easiest option. It is important to know how to ask these offices to give you your data in the most useful format.

Be sure to ask that your spreadsheets include student responses to each question, and not only whether students answer correctly or incorrectly. Because the wrong answers to concept inventories are designed to address common student thinking, you can learn a lot from which wrong answers your students picked. Also, you need all answer responses to use the PhysPort Data Explorer. At some universities, providing data in this format is not the default, so you have to ask. 

Before deciding to administer a test with more than 5 answer choices per question, or a multiple-response test, check if your scanning services office can process this kind of test. Some assessments, such as the FMCE or BEMA, have more than 5 answer choices per question, and others, such as the WDT or WCI, are multiple-response tests where students are expected to pick more than one answer for each question. The most common kind of scantron sheets include only 5 options, of which students are expected to pick only one. Many scanning services offices can accommodate other kinds of tests, but you may have to ask for a special answer sheet.

Online Administration

One way to get your students’ responses into an electronic spreadsheet automatically is to administer the test online instead of on paper. Most assessment developers do NOT recommend giving students unsupervised access to assessments online, and ask that you give assessments online only in supervised environments. This requirement can be relaxed for advanced undergraduate or graduate students.

For details about which online systems to use and how to use them, see our Guidelines for administering concept inventories online.

Software for converting paper responses to electronic spreadsheets

If you administer your tests on paper and don’t have a scanning services at your institution, there are still several options that make it easier to get your students’ responses into an electronic spreadsheet. Here are some options that other faculty we know use (please contact us if you know of other options that should be included here):

Send your tests to PhysPort: If you have more than 100 student tests, you can mail them to us, and we’ll enter them into the Data Explorer. This is a great option if you have old paper tests that can’t be analyzed with any of the software below. Contact us to learn more about this option.

Use software to read bubble sheets using an image scanner: If you don’t have a scanning services office at your school, but do have an image scanner, there are a variety of software package that will analyze forms you generate.

  • FormScanner: Free open-source software that can read analyze data from forms you generate with their software and scan in on any image scanner. See this article in The Physics Teacher for details about how to use Form Scanner for grading Multiple-Choice Exams.
  • Remark Office OMR: This software does pretty much the same thing as FormScanner, but costs a lot of money. Licenses range from $795 for a single user license to $1500 for multiple users.
  • Apperson’s Datalink Connect: This is a system of special scanners, answer sheets, and software that can quickly create spreadsheets of your students’ responses. This is not a cheap option if you’re doing it on your own: the scanners range in price from $945 to $1795, and you’ll also need to pay for answer sheet. However, if your institution already has a contract with Apperson, this is a good option. 

Use an app on your smartphone to scan bubble sheets: There are several apps available for your smartphone, tablet, or document camera which will scan specially prepared bubble sheets. You download their bubble sheets and print them for your students, then take pictures of the sheets. The software processes the forms and lets you download the spreadsheet. Some of these apps cost money per month; others cost money per scan. The general scale is about $5-20/month (after a free trial).

  • GradeCam: To give tests with more than 10 questions and export to csv, you’ll need an Insight Plus account, which costs $15/month after a 60-day free trial. Gradecam doesn’t work multiple-response tests, but does have an option to print forms with different letters on alternating lines, so it could work for tests with more than 5 answer options.
  • QuickKey: Free, but can only handle up to 30 questions per test with five answers each. Student IDs must have 4 or fewer digits.

*File requirements for the Data Explorer

  • A spreadsheet file: .xls, .xlsx, .csv
  • Include a header row telling us what's in each column
  • Include only individual student responses for each question below the header row
  • At least one column must be student IDs or Names for pre-post matching
  • Optional: Pre, post, or both in the same tab
  • 1 class per tab
  • 1 assessment, or one pre/post pair per tab