Collect and Analyze Student Assessment Data: A Step-by-Step Guide

Over the last year I’ve learned that there’s loads of resources out there about grading, grading better, standards-based grading, analyzing and using data obtained while grading, and even going gradeless. This post isn’t about that, though I’m excited about all the possibilities for assessing student learning and providing meaningful feedback.

This post is about how, before I knew about any of that stuff, I wanted to improve the traditional grading system I was raised on and how you can do the same.

Part of our job as teachers is to continuously assess our students’ growth and learning. For many, especially those using a traditional grading system, that means collecting and analyzing grading data on our students. Then we should act on that data, amending how and what we teach and even taking the time to re-teach when necessary.

But practically how do you do that? Do you run your Scantrons and glance at the reports written in tiny pink numbers? Do you notice that half the class missed #4 and that a not-insignificant number of students used the same odd, totally wrong approach for #7? Is that enough to warrant a class-wide re-teach? How do you keep track of the student population that just needs a little redirection?

From the beginning of my teaching career, I was wholly unsatisfied with using grades to understand where my students were at in their learning. I knew there was so much more information hiding under the simple score I put in my gradebook. To find that information, I did what any good nerd would do: I made a spreadsheet.*

*Also I had just walked out of graduate school, where I spent years collecting and analyzing mountains of permittivity and temperature data in Excel. This stuff just makes me happy.

Consider the following scenario: you give a test, you grade each question, you tally up the points for the four/ten/twenty questions, you write the total at the top of the paper, then move on to the next one.*

*Some other time I’ll write a post about how I personally grade and how to get through a massive stack of papers quickly and easily all while being fair and consistent in your grading. In short, I follow a lot of these guidelines.

Think about the “tally up” step. Do you add up the numbers in your head? Punch them quickly into a calculator? What if, instead, you entered them into a spreadsheet? You could store every single student’s scores, per question, without adding any time to your grading process.

Suddenly you have a vast wealth of data to explore and analyze. If you don’t get excited at the thought of that like I do, or if it seems overwhelming, that’s ok. Let’s just talk about how you acquire this data and set yourself up to look for a few simple patterns.

After all is said and done, your spreadsheet will look like this:

image 06

You’ll be able to…

  • Group students according to their strengths on specific portions of an assignment.
  • Identify any students who need re-teaching on a particular topic.
  • Analyze trends in student performance.
  • Streamline the transfer of grades from paper to your electronic gradebook.

Excited? Let’s go!

Step 1 – Prepare your spreadsheet

Enter the headings for your data table. The first column should be the student name. Use the question numbers as the headings for the remaining columns. Better still, write a brief description of the question. (You’ll need to “Wrap Text” to be able to read the descriptions.) Include a “Total” column as well as a “Percentage” or “Grade” column if you’re not grading out of 100 points.

Copy your student names from your rosters. Keep all sections of the same course in the same spreadsheet. The more data you have in one place, the better. I also recommend including a “Student ID” column next to the “Name” column. I use the section number for the class, then give each student a number based on alphabetical order. (So in Section 6, Abby Adams gets number 601, Ben Brown gets 602, and Zach Zabrowski gets 626. In section 8, Adam Acosta is 801.) This will be super useful for transferring all your data to your grade book later.

In the row above the test questions, enter the maximum points that can be earned for each question.

image 01

I’m using fictional characters in my sample. Try not to judge my TV interests.

Step 2 – Enter student grades

You can enter student grades as you grade each paper, or enter them all at the end. In this setup, the [Tab] key is your friend to move to the next cell on the right. If you have your papers pre-alphabetized, you can whiz through this step by hitting enter when you reach the end of the row, it should take you to the first cell on the next row, assuming you’ve been [Tab]’ing your way across.*

*Or if you’re a super fast data-entry kind of person, you could enter individual student grades in columns then copy and transpose the rows/columns of data. You’ll see in the next steps why it’s better to have each student’s grades go across.

image 02

Step 3 – Convert your data into a table

This is going to be super useful when you’re analyzing grades. Select your entire table, including the headings. In the ribbon, under the “Home” tab, click on “Format as Table.” Pick your favorite. I prefer the simple white option under the “Light” colors.

A box will appear asking where your data is. Make sure the box that says “My table has headers” is checked, then click ok. (My long question descriptions messed up the column width, I adjusted it to make the whole table easier to see.)

image 03

Step 4 – Use formulas to summarize grades

In your “Total” column, use the SUM formula to add up each student’s scores. In the sample, we type =SUM(C3:G3) into the top cell in this column then hit enter. C3:G3 refers to the cells that contain the scores to be added, you should see them highlighted in blue. Since you are already in a table, the same formula should be applied to all other students.*

*If you select cells instead of typing them in, you’ll see they’re labeled according to the table headers.

image 04 a

In your “Grade” column, divide the total points you just added by the total possible points. In this sample, we type =I3/$I$1*100 then hit enter. Note the dollar signs, which fix the total possible points for all students.

image 04 b

We’re collecting data for each student, but we’re also collecting data on each question. Scroll to the bottom of your table, a couple of rows below the last student entry. For each question, calculate the average and median score using the AVERAGE and MEDIAN formulas.

In this sample, we would type =AVERAGE(C3:C20) and =MEDIAN(C3:C20), where the range C3:C20 contains all the student scores for the first question. Select the two cells then use the box on the bottom right corner of the highlighted cells to drag the formula across to the cells under each question. It’s also useful to copy the total possible scores from the top row of your spreadsheet here. (In the sample, I reduced the number of decimal places visible to make the data easier to read.)

image 04 c

This might be enough data for you! Are you surprised by any of the results?

Step 5 – Use color to highlight low grades

Hungry for more? This step helps you identify individuals and groups of students who need attention.

Select all the scores in the “Grades” column. In the ribbon, under the “Home” tab, click on “Conditional Formatting.” Select “Color Scales” and choose the first option, Green-Yellow-Red, on the top left corner. Doing this colors your grades over a spectrum, with green going to the highest grades, red to the lowest, and yellow for everyone in between. There’s other options too, pick your favorite.

image 05 a

Now we want to look at specific questions. At first, I don’t recommend doing this for all the questions at once, since the color can be overwhelming. Start with the one or two questions that most concern you. Select all the scores for that question and as before, assign a color scale. For this purpose, I recommend the Red-White color scale. It highlights the lowest scores in red, with a gradient to white for the highest scores. Since you’re probably not worried about the students who score highly in this question, there’s no need to flood your spreadsheet with more color.

In the book Driven by Data,* Paul Bambrick-Santoyo doesn’t use color and instead recommends that you leave the correct/perfect scores blank so that you don’t even see those values. That he doesn’t use color might have been a product of keeping the data simple or black-and-white printable, but I do like the idea of hiding perfect scores. That’s a post for another day.

*Note: This is an affiliate link, which means I receive a small commission if you purchase the book by reaching it through my site at no extra cost to you. Thanks for your support!

image 05 b

It looks like Section 7 performed exceptionally well on this question… did you teach this skill differently to them or is there something sketchy going on?

Now analyze your data!

You’re all set! The best part about Excel tables is that you can easily sort the table according to different criteria. Click on the down arrow in the column header to open a drop-down menu. You can sort the entire spreadsheet from lowest to highest grade on any given question or in the total.

The colors let you pick out patterns, such as, did everyone that missed question 2 also miss question 4? Once you’ve looked at the specific questions that stood out to you while you were grading, you can add color to the rest of the test.*

*It’s important that you apply the conditional formatting to each question individually, especially if the questions have different point values. Excel decides how to assign each color in the gradient based on how many times a point value appears. You don’t want the scores on #5 to affect how the scores on #6 are highlighted.

Now look at each student. Is there a student who consistently scored low on all the questions? Or is there a strong student who scored surprisingly low on a specific section of the test?

When you’re done, you can resort according to Student ID and now you can easily copy your grades by section into your grade book!

image 06

A more complete story emerges when you consider the individual questions instead of just final averages. Josephine “Joey” Potter and Jack McPhee had similar averages, but they performed very differently on individual questions.* Joey dropped the ball on two of the percent questions while having perfect scores on the rest. You can go back to her test and see if she had a misconception on how percents work or in her setup. Jack, on the other hand, scored the median grade on most questions but totally missed the molecular/empirical formula question. Was he absent the day you taught that topic?

*As I understand it, this kind of thinking eventually leads us to standards-based grading. Neither of the schools where I have taught use SBG, but it’s something I want to seriously look into someday. Here’s an awesome study on how there’s no such thing as “an average student.”

In a future post we can talk about how to act on all this information in your classroom, but you won’t know what to do or if you even need to take action until you collect and analyze some data!

Now that you know how to collect data, and have some initial questions to start your analysis, I’ll let you go at it on your own. You know that students learn best by doing!

Ready to analyze data? How do you collect data on your students’ progress? Let me know in the comments!

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s