When it comes to working with student data, most tech-savvy educators know how powerful spreadsheets can be. A basic knowledge of formulas and functions makes it possible for a teacher to generate quality information, reports, and charts. But the typical teacher grade book doesn’t allow for much data manipulation. Student scores on particular assignments in a grade book don’t necessarily show teachers whether students have mastered the material.
While grade books are probably here to stay, there is an additional tool that can help teachers gather more information from their students’ grades, including student mastery and testing information. It’s the Conditional Formatting function in Google Sheets.
Basics of Conditional Formatting
With data already entered in the Google Sheet, select “Format” (at the top of the page) and drop down to “Conditional Formatting.” This will open a new box on the right-hand side of the screen.
Next, select the range of cells that you would like to format (D3:D9), and decide what you want to do with these cells. For example, I might use the “Greater than” function to check on student mastery. Then I would select the score for mastery – in this case, “7.” Finally, I pick the formatting that I want to see when this information is there. In this example, the data appears in green to indicate mastery.
I can click “Add another rule” (at the bottom of the box) and add something else that I may want to see. For the same range of numbers, I add the color red for scores that are 6 or below. I also decide to make the red scores bold so that they stand out.
The list of formatting rules appears on the right-hand side of the screen, as long as the Conditional format rules are open. To change the number, color, or data range in one of the rules, just click on the rule to make the change.
Now that you know how to do Conditional Formatting, let’s consider how to use it to help your students.
Helping Your Students
- Student Mastery. Did a student learn the information on an individual level? By using Conditional Formatting to color-code the scores, a teacher can easily see how individual students did on an assignment. If a teacher enters the key assignments over time, a student's individual “row” of scores will clearly show how well that student is doing.
- Guiding Instruction. Did most of the students learn the information? It’s easy to see in the example above that most of the students did not grasp the concept of the assignment. The teacher can use this as formative information to reteach or possibly group students for extra practice. Secondary teachers can use this data to make mastery comparisons from class to class, which would allow for reflections on the instructional or curricular level. For example, “Strategy X produced mastery in 60% of my students, but strategy Y produced 90%.”
- Testing/Screening Data. Do specific students need extra help? Many available testing/screening tools produce a color-coded chart to help teachers make instructional decisions, so why should you bother to import or re-enter data into a Google Sheet? There are a few reasons. First, this may not be the only data point teachers are looking at. Putting all the data together and then conditionally formatting it helps provide a good overview of which students are struggling and need the most support. Second, teachers and teams can develop their own “cut scores,” depending on local norms and expectations. Finally, with data in a sheet like this, it can be sorted, manipulated, charted, and maneuvered to help teachers. Testing systems can be very complex, with multiple reports that are hard to use and understand. But the much simpler Google Sheet gives the teacher full control over using the data.
- Grouping Students. Which groups of students need the same amount of help? If a teacher adds a few different color ranges into the formatting and then sorts the column, the result will be an easy-to-use visual for the teacher or para. The students who score the lowest are one color for a remediation group, and the students who have achieved mastery are a different color group that can move on to the next skill.
Sharing Information with Parents
The color scale option at the top of the Conditional Formatting box offers a good way to share information with parents. The color scale gives you gradient colors for the whole range of numbers. For example, if the data range has 100 as the high score and 20 as the low score, the color scale will make the 100 the darkest shade of one color (green) and the 20 the darkest shade of another color (red). The numbers in the range between these two will be lighter shades of the base color, with the median score being white (no color). This format gives a good visual of where a student falls in a large group, and may be more meaningful than telling parents what percentile their child falls into. Please remember: You should never share other students’ individual data with parents by name. Showing parents the range of the class and where their child falls within that range can be helpful information.
Want to take assessment and results even further? Look into using the assessment features built directly into Mimio classroom software for the MimioVote™ clicker and MimioMobile™ mobile app.
Read More Information on MimioEducator about Google Docs
25 Cool Hacks for Using Google Docs in the Classroom