For the month of March, I’m challenging myself to write a weekly “Listicle” style article for the different communities and audiences that make up the TDAA readership. This week is dedicated to teachers, who’s stamina seems to know no bounds. Essential workers and essentially over-worked, I am indebted to your ambitious vision, no-nonsense meetings, and generosity. TDAA has seen many episodes about work I’ve done to support schools and teachers with spreadsheet systems, like this seating chart, this discipline log, and this other discipline log! Today’s post is all about spreadsheet tips that teachers can use right away. Let’s get to it!

Banana split

There are so many times in life (okay, and in remote teaching/learning) where you need to take data and either split it into two columns or zip it into one column. This especially comes in handy when you need to sort alphabetically… by last name! ~Yikes~ Never fear, Google Sheets has a few simple tools that you can keep in your back pocket for exactly these scenarios. Thanks, Melissa Hero, for posting these great screenshots on your site!

Concatenate/Split - Google Sheets
Here’s an example of smooshing First Name and Last Name into Full Name.
Here’s an example of splitting Full Name into two columns. (Note: this splits every time there is a space, so students with middle names would end up in 3 columns, but then you could always re-smoosh First and Middle back together. Remember, there are different cultural norms in different ethnicities about handling names!)

Once you have your data combined or split appropriately (I can almost guarantee that at some point, you’ll need to go in both directions!) you can do one of my favorite Google Sheets tips. Simply select your data, and then click the little funnel icon on the menu bar. Then, you can easily sort or filter any column in your table.

Video platforms (like Zoom) typically sort names alphabetically by First Name. Meanwhile, gradebooks are historically organized alphabetically by last name. If you need the flexibility of going back and forth, this trick will become your new teacher’s pet.

Things are v-looking up

We covered how to take data and split it and zip it (I sound like my beloved pilates instructor). However, what if your data are split up between two different sources? Teachers face this dilemma ALL THE FREAKING TIME because #edtech platforms don’t “talk” to each other very well. So you might have one system spit out an attendance list and another sheet split out grades. What if you want to know who was absent so they can be marked with “exempt?” You could go one at a time and cross check, but OMG we ALL have better things to do. Especially teachers!

Here’s are some real world scenarios:

  • you forgot to put Student Name on a Google Forms test
  • you forgot to include section name for a test that you repeated in two classes

You could use a VLOOKUP to find the student’s name, or their section, based on their ID number.

So, let’s imagine this scenario. I have test results that have a Student ID number, but no student name. I need to put grades in the SIS platform, which goes by name and not ID. Make the kids retake the test? NOPE!

For this formula, you typically need what I call a “staging table” or a “key.” So, you need a list that has BOTH the Student ID number AND the Student’s Name to use as a reference.

Then, make a formula that looks like this:

=VLOOKUP(select first student ID cell, highlight entire staging table, 2, FALSE).

or in other words, =VLOOKUP(B3, Tab2!A1:Q2, 2, FALSE)

This formula will take the ID from the first tab, search for it in the “key”, find the name next to it, and pop the name back in the first table. Let’s look at a GIF to see this function in action!

Thanks to this website for the GIF above! They have more examples of VLOOKUPs, including a school-themed one and a helpful tutorial at the link above.

Mean grader

Spreadsheet formulas SUUUUPER come in handy for teachers grading tests. There’s a lot that Google Classroom can handle for you now (woo hoo for that!) but I think we’re not out of the woods yet when it comes to spreadsheet-enhanced grading.

Mr. Spreadsheet Whisperer (aka my boo) and public school teacher extraordinaire loves to use Mean and Median functions to find out the shape of test data, and use that information to apply a curve (such that the median grade is 70. Usually, this looks like:

  1. =MEAN(click and drag to highlight cells here)
  2. =MEDIAN(click and drag to highlight cells here)
  3. =[select cell with original grade]+7 (drag down to last row of data… this would be a 7 point curve)

Sometimes his students say he’s a mean grader. If only they knew the truth!

Madlibs

If there were more hours in the day, or perhaps more reasonable expectations on teachers, I know my teacher friends would delight in writing thoughtful comments about each student’s progress and engagement in class. And in may cases, teachers do it anyway – at great personal sacrifice! But sometimes, even the most thoughtful comments can get repetitive. When push comes to shove, wouldn’t it be great to have a “report card comment generator”? Once upon a time, I used some spreadsheet badassery to build just that.

Unfortunately, I don’t have the template close at hand, but I can explain the overall idea. Basically, we took a list of student names (first name only!), their grade, their pronoun, and a menu of sentence fragments. Then, I used the handy dandy “concatenate” function (also known as smoosh, which you saw above) to automate a spreadsheet template. The formulas looked something like this…

=CONCATENATE(“I really enjoyed getting to know ” + [Student name] + ” in class this semester. ” + “[Pronoun] grew both in confidence and mastery of the material. I can tell that ” + [pronoun] + [excels at…]. + ” One area that I want to pay attention to is ” + [growing edge]. + ” If ” + [Student name] + ” continues to make progress with ” + [pronoun] ” + will do great on their final project. Please make sure to remind ” + [Student name] + ” about ” [upcoming project deadline.])

Notice that the formula pattern pays special attention to space bars, since the Concatenate function does not add them for you!

Does a mad-lib style report card comment make you feel icky? I know I have a twinge of that even as I am writing this post. Experience tells me that if we can templatize some of this work, it will give teachers more space to do things like 1:1 social-emotional check-ins that are irreplaceable. Diversity of tactics, right?

Rubric Cube

Managing virtual rubrics is a total pain in the ___________. Okay, okay, we’re done with madlibs. But I couldn’t resist! Even paper rubrics are hard to keep track of, and take extra time to add and enter into gradebook. The new(ish) Google Classroom rubric feature is a step in the right direction, but it’s no replacement for an old-fashioned rubric, imo.

This blog post kinda speaks for itself and I can vouch for using it personally. For you non-teachers who love TDAA, I encourage you to look into it because projects like this show me that I’m *still* only scratching the surface of what’s possible in Google Sheets.

Teachers, I am so humbled by your brilliance and I will keep fighting for a world that treats you with the dignity you deserve. Here’s a tiny contribution toward making your day slightly more magical.

Trying these tips? Have another to share? Leave me a note in the comments!

Leave a Reply