Make Automatic Class Grade Sheets in Excel From Scantrons

excel screenshot

Every grad student or TA knows that manually transferring marks from a multiple-choice exam result to the class list is one of the most tedious parts of being a TA. Also, doing this job manually leaves a lot of room for errors that can haunt you with hundreds of angry student emails! But fear not! Over my (many) years as a grad student, I’ve learned several excel tricks that will save an incredible amount hassle.

This tutorial will teach you how to make a fancy reports for exams by directly importing scantron reports. The skills you will learn in this tutorial will also help you in your own research and data management. I know they helped me a ton! This tutorial was written using excel 2016 for Mac. If you use windows, it should be more or less identical, but keyboard shortcuts using the “COMMAND” button are replaced with the “CTRL” button.

note

This tutorial is migrated from my old website, written in 2017, and was based on the scantron output from York University from that time period. But it should be easily modified to fit your school’s output.

Click here to download my excel file for reference (before the "saving as a template" step)

At the end of the tutorial you will have a reusable exam report template that can:

  • Quickly import your scantron results into your report.
  • Quickly award bonus marks to excluding those that did not write.
  • Easily combine scantron results from multiple versions of the exam (including makeup and alternate exams).
  • Easily and automatically get grade distributions and averages.
  • Easily print a report for students that hides confidential data.
  • Make a template to easily reuse your hard work over and over again.

You will also learn these excel skills:

  • Tables, pivot tables
  • Excel functions:
    • IF, IFERROR, VLOOKUP, CONCATENATE, RIGHT, LEFT, FREQUENCY array function, AVERAGEIF
  • Formatting, conditional formatting
  • Histograms
  • Excel templates and form protection

Initial setup of exam report sheet.

  1. Create a new excel workbook and name it “ExamReport” or some such.

  2. Give the worksheet a name. Right-click on the worksheet tab at the bottom of the screen and rename it to something useful like “ExamReport”.

  3. Create the following columns by typing the column titles into the first row of your document:

    Student ID
    Last Name
    First Name
    

    screenshot1

  4. Now, select the 3 column titles and the 3 cells below them, and hit command-T on your keyboard. Click the checkbox “This table has headers”. This will convert the normal cells into an Excel Table. This gives you a lot more functionality.

    screenshot

  5. With a table cell selected, click the “Table” tab on the main toolbar. :::important Give the table a new name. I named my “Report”. ::: 6.Make it pretty:

    1. Hide the ugly drop-down arrow buttons: Toggle the “Filter Button” checkbox.
    2. Pick a style. I prefer the “Table Style Light 1” style.
    3. Select the whole header row by clicking on the “1” to the left of the row. Rotate the text so it doesn’t take up a lot of room. With the header row selected, on the “Home” tab: Click on the “Wrap Text” button and click the button with a diagonal “ab” on it and choose “Rotate Text Up”.
    
  6. Finally, I usually make the text justified center and bold. screenshot

Prepare our exam sheet for our exam data

  1. Create non-confidential IDs for when students look up their grades. screenshot

  2. Now for our first bit of magic. We will create a column to help us report our grades to the students while shielding their confidential info like student number and name.

  3. Adding columns to tables is easy. Just click on the next header beside our table (cell D1) and start typing. I use the name “Last Name Initial and Last 5 Digits of Student ID”. Hit return, and voila!

  4. Ok, time for the magic. We want excel to automatically show the first initial of their last name this can be done using the LEFT function in excel.

  5. We can also get the last 5 digits of their student number using the RIGHT function, and combine the two (plus a space in between) using CONCATENATE which joins several bits of text together.

    Our general formula will be:

    =CONCATENATE( {first initial}, {add a space}, {last 5 digits})
    

    The final formula will look like:

    =CONCATENATE(LEFT([@[Last Name]],1),” “, RIGHT([@[Student ID]],5))
    

    Excel would normally just put in a reference to individual cells. But since we’re working with tables, it references the whole column! This is very powerful.

  6. Hit enter, and excel automatically populates the entire column for you!

  7. With a long list, it helps to freeze the headers and student names so you always know where you are in the file. Click on the first data cell in the “last name initial 5 digits” column, go to the “View” tab, and click “Freeze Panes”. Scroll vertically and horizontally to see the effect. screenshot

  8. Create the data columns. Using the method described above, create 3 new empty columns in our table:

    Raw Score
    Version
    Percent
    

Import the exam data

York University’s scantron office outputs a file in the following format: screenshot

  1. Open the scantron office’s file. Really, all we care about is the Student ID and the Raw Score.
  2. Let’s import this data into our exam report sheet. We want to copy the scantron data into our report file as a worksheet within our file. Do this by finding the worksheet’s tab at the bottom of the window. Next click and drag the worksheet tab over into our exam report window right beside our exam report’s worksheet. Close the scantron office’s file once the sheet is imported into our file.
  3. Rename this imported worksheet something useful like “Exam1A”, since this is version A of the exam.
  4. We want to convert the data into a table. Select all the cells of the main data area (including the headers, excluding the class averages) and hit command-T. Give the table a name like “Exam1A”.
  5. Add a new column to the scantron table called “Version”. screenshot
  6. In the first row of the “Version” column, type in A and hit return. Double click the bottom right corner of this cell to fill down into all rows of this table.
  7. That’s it! Our scantron data is ready to import into our report.

Import raw scores into report

We will be using the VLOOKUP function to auto-populate our report with the scantron info

For more info about using VLOOKUP, watch this video.

We will be using a slightly more powerful version, but the same general idea applies.

  1. Click on the first empty row of the “Raw Score” column in our report table. Type in =VLOOKUP(
  2. Now click on the first student’s student number. This will tell excel we’re looking up student numbers in the scantron table. Add a comma.
  3. We want to point excel to the table in which we’re looking it up. Type in the name you gave the scantron table. For me it was “Exam1vAtable”. Excel should autofill this name as you type. Add a comma.
  4. We need to pick the column number where the data is we’re interested in. in our scantron table the raw scores are in the 3rd column, so we type in “3”. Add a comma
  5. Now, we want Excel to only use exact matches between student numbers, so we type in FALSE, to tell it to avoid trying to find a close match. Close the parentheses. And hit return. You should see the raw scores populating the column! Magical!
  6. The final formula: =VLOOKUP([@[Student ID]], Exam1A, 3, FALSE)
  7. We want to do the same thing for the version data, but now we simply refer to column 6. Copy and paste the above formula in, and change the 3 to a 6. Hit enter, and voila! screenshot

Notice how two students give errors because it can’t find their student numbers in the scantron list. See below to fix this.

Dealing with multiple versions

But, you may be asking, “my exam also has a version B!” Well, it’s actually very easy now that we have everything set up.

  1. Import your version B scantron worksheet, create and name the table, etc., and set the version column to B. Remember to add a new row for B in our possible versions table!

  2. Now we need to modify our raw score getting function to first check in version A for a student ID, and if it’s not found, then look in version B, then if not found in either, give the student a score of zero (rather than an error). We’ll use the IFERROR function.

    Our function will look a bit like this:

    =IFERROR( {if student is found in vers A [same formula as above]}, {if not, do something…})
    

    the something is the same formula, but for version B

    =IFERROR( {if student is found in vers A [same formula as above]}, {if not in both, code as zero})
    

    So we’ll have an if IFERROR function inside another IFERROR function for each version of our exam. Side note: This can be simplified using Visual Basic macros, but that’s out of the scope of this tutorial.

    Our formula becomes:

    =IFERROR(VLOOKUP([@[Student ID]], Exam1A, 3, FALSE), IFERROR(VLOOKUP([@[Student ID]], Exam1B, 3, FALSE),0))
    

    Remember to update your version function to match!

    =IFERROR(VLOOKUP([@[Student ID]], Exam1A, 6, FALSE), IFERROR(VLOOKUP([@[Student ID]], Exam1B, 6, FALSE),””))
    
note

for the version, I changed the zero to a “” which leaves the cell empty. You can make this anything you like for example “N/A” or something similar.

If you have more than 2 versions, just nest more IFERROR functions as above. screenshot

Calculate and analyze data

Calculate percentages

Now for the cool part. We’ll calculate the percentage score in a way that allows for easy posting.

First we need to define a few properties of our exam, and allow for adding bonus marks to everyone’s score.

  1. Create a table as shown below and call it “ExamSetup” screenshot
  2. Fill in the appropriate values in the table.
  3. Now to calculate the exam percentage, we want to ensure we’re not giving away bonus points to people who didn’t write the exam. So we’ll use the IF function like this:
    =IF( {if raw score>0}, {then add raw and bonus}, {or else just the raw score}
    
  4. Then divide the result of that by the total marks. So, the formula is:
    =IF([@[Raw Score]]>0,[@[Raw Score]]+ExamSetup[Exam Bonus marks],[@[Raw Score]])/ExamSetup[Exam Total Marks]
    
    screenshot

Make the percentages easy to read

Now we need to make it readable and ensure it rounds properly. On the “Home” tab:

  1. Select the entire “Percent” column in the table.
  2. Click the % button
  3. Click the “Move decimal point” buttons to desired position (look like blue arrows with 0.00 next to it near % button)
  4. Next, we want to color-code our percentages to show students who missed the exam (blue), failed (red), needs improvement (yellow), or is fine (green).
    note

    this is especially useful for Intro Psych, since at York everyone needs a C+ to move forward as a psychology major.

  5. Select the all entries in the “Percent” column. On the “Home” tab, click “Conditional Formatting”, and pick “Manage Rules”. Click the plus button to add a new rule.
  6. Pick the following options: Classic, Cell value, equal to, type in zero, custom format, Color = black, fill tab>background color = lightest possible blue. Hit OK twice to get back to manage rules.
  7. Add a new rule picking the same as above, but this time pick “greater than”, enter 0, and pick different colors.
  8. Add a new rule picking the same as above, but this time pick “greater than or equal to”, enter 0, and pick different colors.
  9. Add a new rule picking the same as above, but this time pick “greater than or equal to”, enter 0.5, and pick different colors, and again for > 0.65.
  10. Make sure the rules are ordered as shown below, then check off “Stop if true” for all. screenshot

Analyze exam results

Now is where all that hard work will pay off bigtime. We want the class average, know how many students wrote each version, and we want a histogram of letter grades on the exam.

Class average

We want class average in its own new table, and we want it to exclude those who didn’t write the exam.

  1. Click the empty cell, and use this formula:
=AVERAGEIF(Exam[Percent], “> 0”)

Counts for each version

To find counts for each version we will use a pivot table.

  1. Click anywhere inside your main “Report” table, on the “Insert” tab, click PivotTable.
  2. Click “existing worksheet” for where to place it, and click on a cell to be the upper left corner. A blank pivot table will appear with a builder popup.
  3. Under “Field Name” find Version, and drag it to the “rows”
  4. Find student ID and drag it to “Values”. It defaults to summing, since it thinks it’s a number. But we want to count how many students are in each version instead. Click the little “i” button beside it, and choose “Count”
  5. You can see the pivot table has updated itself with what you wanted! Amazing! You can now rename the headers to something more useful. screenshot

Histogram

Create a table that tells excel what each letter grade means in terms of percentage. One column should be the letter grade, and the other should be the upper bound of that letter grade. See below (modify for your school’s policy). screenshot

Now we’re going to use a trick to create our histogram easily. Just to the right of the table, we’re going to add the frequency, but excel can’t handle this formula if it’s in a table. We’ll be using normal excel cells for this task.

  1. Select all non-header cells in the column directly to the right of the grade column.
  2. Type =Freq, and let excel autofill the FREQUENCY function for you.
  3. Then, select all “Percent” data cells, then add a comma
  4. Then, select all “Grade” cells. DO NOT HIT RETURN YET.
  5. Now we want excel to do something special and use an array function instead of a normal function. So we hit control+shift+return. Note the curly brackets around the function for each frequency.
  6. Now we have our frequencies! You can name the header now. screenshot
  7. Now let’s create the histogram. Select all cells in “Letter grade” column, hold control, and then select all cells in the “Frequency” column (including headers).
  8. With the above selection go to the “Insert” tab, and click the bar chart button. We have our histogram! screenshot

Create pdf output to post for students

For confidentiality reasons, we want to hide names and student numbers when we create our PDF output. We would also like to hide our analytics. Typically I’ve seen people copy and paste the values into another excel file, only pasting the cell values. The problem with that method is that any changes you make will require completely repeating the process, and you’ll lose any fancy formatting. Another option is to hide the columns you don’t want to show, but this involves continually hiding and un-hiding columns whenever you want to make changes.

Instead, let’s create a perfect way to output our data. By selecting the ouput columns (in our case columns D-G), we can set them as our “printable area”.

  1. Select the columns to post (D-G), go to File > “Set print Area”.
  2. Now select File > “Print”, and the print dialog should appear.
  3. Notice the preview has hidden all other data in our report.
  4. (Only on mac) In the lower left corner of the print dialog click on the PDF button, and save the PDF.
  5. We now have a formatted and confidential output of our exam grades for posting to the class.

Watch the magic occur.

Now that we have the bare bones of our report complete we can take advantage of the power of all that setup.

We can actually manually enter data into our report

  1. Add a row to the bottom of the report table.
  2. Try entering in a new student to the bottom of your report with made-up data. You’ll see that everything updates itself automatically!
  3. Right click on the row to delete it. screenshot

We can take advantage of this functionality to create a reusable exam report template that auto-updates itself as soon as you add data to it!

Making it a re-useable template

  1. Save your completed exam report. Create a copy of the file (we will be destroying your actual data, so make sure you have the actual report backed up!) 2.Rename the new copied file to something like “Exam Report Template”.
  2. Delete:
    • All rows except the first in your main “Report” table.
    • The values in studentID, Last Name, First Name of the first row.
    • The scantron worksheets (Exam1A, Exam1B etc.)
  3. Set:
    • Exam Total Marks to 0.
    • Bonus Marks to 0.
  4. Make sure the formulas in the report table are all still there.
    note

    If you want to make it pretty you can add IFERROR clauses to any functions that show errors.

Your sheet should look something like this! screenshot

Get the sheet formatted as you like it, make it as pretty as you want, add any customizations etc.

  • If you want to prevent edits to the pre-set sections of the sheet you can turn on protection of some cells to make them unable to be modified.
    • Select all cells COMMAND-A, and right click any cell. Under the “Protection” tab, uncheck “Locked”. This will unlock your whole document
    • Now, select the cells you DO want locked, and recheck “Locked”.
    • Enable protection by going to Tools > Protection > “Protect Sheet…”
    • Enter in a password, and then deslect the option “Select locked cells”. This will prevent users of your report from editing any cells that you locked.
  • To create the template, go to “Save as” and choose template as the file type. Voila! A reusable, powerful, and beautiful exam report for all your grading needs.
  • Now you can create new files directly from this template.
note

If you want to insert this template into another Excel Workbook, just drag all the worksheets into the desired workbook.

Closing up

I sincerely hope you found this tutorial helpful! Please let me know if you have any questions or comments.