The “How” of Data Tracking: Conditional Formatting

I’ve written a lot lately (here and here) about WHY tracking your school or organization’s data is so critical right now.

As schools begin to reopen, I’ve heard from educators of so many different configurations for what school is going to look like: some totally virtual, some in person, some in a hybrid model… 

That’s a lot to keep track of!

It’s hard enough to make sure that no child falls through the cracks when school is operating as usual — now that task seems herculean. 

So how can we be sure that we know where each student stands, even if we don’t see them every day?

How can we know that we’re doing our best to meet their needs (and that we even know what those needs are)? 

That, my friends, is where some simple data tracking strategies come in. 

Here is one trick to help you assess, at a glance, which students or families need a little more support: conditional formatting. 

Conditional formatting allows you to set criteria to automatically color-code or highlight values that you need to take action on.

You’ll see that my (totally fictitious) dataset below is just a typical spreadsheet — nothing stands out at a glance, and it’s hard to see which of these students really needs the most attention. 

We can use conditional formatting to visually highlight some of the students who need some support. We can find it by clicking selecting the text we want to format, and then clicking on the circled icon below.

Once you click on the icon, you’ll see a drop-down menu pop up with all of the options you have for automatically color-coding your data. I think the easiest way to start is by selecting “New Rule.”

In the menu that pops up to manage your rules, you’ll set the criteria that will determine what gets color-coded and how you want to code it. 

​This is where things get fun. 

The simplest option is “Classic.” I always choose “Format cells that contain” and set either a text or numerical value that I want to highlight. Below is how I highlighted the students whose Quarter 3 grades were below a 70. 

And voila! Now you can see, at a glance, the students who were struggling in Quarter 3. No fancy statistical skills needed!

I could also create other rules for my high-performing students or those ones that fell in the middle of the class.

There are TONS of other ways to conditionally format your data — here are two other examples for the same data that address the whole range of grades.

On the left, you’ll see that instead of the Classic mode, I used an Icon Set to do a little traffic light system for the student grades. On the right, I used Data Bars to help me see students’ grades on a scale from the lowest to highest score.