featured, Practice

As educators, we want to use the various tools available to us – from Blackboard, to Google Forms, or maybe even Twitter – to support student learning. By using these tools, we can get data on what our students are doing, and connect that data to our overall gradebook. However, often different tools give us different data formats, or different groups of students engage with the tools differently. Unfortunately, one of the things I sometimes see with educators or other folks who work with student data is that they either:

End up manually copying student submissions e.g. from google forms into blackboard, or
Give up, either just not using the data, or switching to use a different tool (like pen and paper, etc.)

Two really simple tools can address most of these problems though, using Excel, Google Sheets, or another spreadsheet tool:

Vlookup – using this function, for each student in a gradebook you can lookup their StudentID in another list, and find a value against that ID (e.g., find their quiz response, or score)

Pivot tables – using this tool, for each response from a set of students, you can summarise the responses by student (e.g., if they submit lots of times, you can get their ‘average’ response)

I’ve created a dummy dataset (available here) and embedded below – to demonstrate some of these functions…I may append a video description at some point.

DataPlay

I want to illustrate a couple of cases where educators (teachers/lecturers/whatever) might find it useful to use two functions you find in excel, Google Sheets, and other common spreadsheet programs:

1. Vlookup – we can use this to ‘join’ a list of student IDs, to a list of grades they’ve submitted. Generically, it’s used to ‘join’ two lists together by a common key. A bit like pulling a zipper…

2. Pivot tables – we can use this to do things like count the number of submissions a student has made to a quiz, or to get the mean satisfaction score on a question, etc. Pivot tables tabulate and summarise data by group.

These three sheets are just static data, they’re what you’d download from your LMS, quiz platform (e.g. google forms), or other tool

  • On the sheet ‘QuizData’ you’ll see some studentIDs (fake) and their responses on a quiz – we want to know if they did the quiz
  • On the sheet ‘Feedbackdata’ you’ll see IDs from the same cohort, they were asked to submit feedback 3 times, we want to know how many times each student submitted (we use the pivot table sheet for that)
  • On the sheet ‘Searchtext’ you’ll see we asked students to submit a quiz together, and list their student IDs as one of the quiz questions, we want to know which students were in a group that submitted and record that
  • This ClassRoll-gradebook (and the pivot table) are where most of the magic happens – pulling in the data to match each individual student in your cohort
  • The ‘ClassRoll-gradebook’ is our roll, it’s the full list of students (like we’d export from blackboard or some other software) even students who never do any assignments! We want to record the grades against this list.
  • You can see the formulae, and that there are different ways of displaying the result (show their ID if they did it, show a ‘Y’ or some other value, or show the score/result matched against their ID)

If you want a copy of this document, the URL below will create one for you!
https://goo.gl/P9Yvjo

This post was originally published on Simon’s blog and is reposted here with permission. 

Feature image by: Pietro Jeng

Join the discussion