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 in Excel. 

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.

With these formatting options, I can see the students that might need some extra support... and it only took a few clicks and a decision about the point at which I'd be concerned about a student. 

This is just one strategy of many that can help you quickly and easily to make sure no students are falling through the cracks. 

Want to learn more? 

• Schedule an Excel workshop (or three!) for your organization - check out the descriptions from my three-part series here. 
• Sign up here for the free Evidence for Engagement mini-course! It will walk you through how to become an evidence-based organization. 
• Pencil this into your calendar: Evidence for Engagement is going live! On September 16 at 3:00 EST, Tamara Hamai and I will be leading a workshop on how to use data to ensure that you're still making an impact, even if your services have gone virtual. More information coming soon!

None of these work for you? Schedule a free 30-minute consultation with me so we can figure out how to best get your data tracking system started!
​