Early in the school year I was contacted by a fifth-grade teacher asking for a way to help her students keep track of their own learning and to set goals based on the results of classroom common assessments. Like many teachers, she struggles with the relevance of a single score that represents what a student “knows” as the result of a common assessment. She wanted a way to take the assessments that students have and keep track of their scores on the individual skills found on a rubric and have them serve as a way to begin the conversation in her conferences with students. She was looking for a way to have her students own their learning and set goals based on their performance.
When we first met, I we didn’t talk about possible solutions. Instead we discussed why this was important for her students and their achievement. I asked more questions than I provided answers, and listened to her really narrow down her goals to make sure what we did would work in her classroom and for her team.
Through this conversation, we determined that Google Spreadsheets could probably do the job, but to be honest, that would be new territory for me. The concept was simple enough: each student would be given a Google Spreadsheet where they would record their common assessment scores for reading and writing throughout the year as they were handed back. This would give them an ongoing record of every score they received on the common assessment rubric with a spot for notes for each assessment where they could reflect on their performance. Each cell in the spreadsheet used conditional formatting that would change based on whether that score was proficient, nearing proficient, or needed improvement. These visual cues helped students see their performance over time and allowed for the teacher to have conversations with them about their reflections and goal setting. Every spreadsheet was shared with the teacher so that both student and teacher could easily see where each student was.
It was a good first step, but it became evident that the information could be used to inform the teacher’s practice based on how students scored on individual assessments. However, the information needed to be brought into one place to see and analyze the overall class performance based on individual rubric criteria. We needed to create a summary sheet so the teacher had quick access to all scores and could see student reflections as well.
One of the great things about Google Spreadsheets is that, because that document lives online, each one has a unique string of numbers and letters in the web address that will never change. This string is the unique identifier of that spreadsheet and, using a formula inside a Google Spreadsheet cell, you can pull the information from one spreadsheet into another spreadsheet. Whenever that first spreadsheet is updated, the information is automatically passed on to the spreadsheet that is importing the information. In this instance, that means that every time a student puts a score in their common assessment spreadsheet, the teacher summary sheet is automatically updated.
The process is pretty straightforward, but it’s important that all steps be followed precisely to make it work.
1. Find the unique identifier in the url of a student spreadsheet (shown highlighted below). Then copy this url for use in the summary spreadsheet.
2. Go to the summary spreadsheet and use the importrange formula to pull your data into the spreadsheet. In the case below, the long number is the unique identifier from the student spreadsheet url. The second part of the formula represents the sheet name and range of the cells that you want to import. Take note that both of these items are in quotation marks. Be sure that you include this in your formula or it won’t work.
=IMPORTRANGE("1NUnZIjZmVIjgUdFaGAL617Z2KFH8E4XwgvdinAdv9Lg","Sheet1!B2:B35")
3. When you first enter this formula in your summary spreadsheet, you will get an #REF error. Click on that cell and click on “Allow access” in the pop-up window that comes up. This basically gives your summary spreadsheet permission to pull information from the student spreadsheet.
From now on, anytime any of the cells in that range on the student spreadsheet are updated, the summary spreadsheet will also be updated.
Focus on the Conversation
It's important to focus on the conversations that come from this information. From the student’s point of view, this allows them to reflect and set goals for their own progress and performance. For the teacher, this can help guide the conversation during student/teacher conferences and help determine what reteaching needs to occur based on class scores. Regardless, the numbers and scores are the data, but that’s not why we created this process.
Over the year these conversations grew and the teachers stopped guiding the conversation. Students took responsibility for their performance and always had something to talk about during conferences, and as a class, they could talk in more meaningful ways about tracking information and setting goals for themselves that could help them in all their endeavors.
If you’d like step-by-step directions or want to see how this is set up, go to https://youtu.be/HEKE0DX2rs4 for a how-to video. It's based on criteria for a kindergarten class, but the set-up process is the same.
