Visualizing Student Support Data with Google Sheets

In the beginning

I joined the [formerly-called] SEN team last year. The team was using anecdotal records for their data collection. The issue was that it was kind of a black box -immediately I wondered how I would know if the strategies I was using with students in my care were helping them reach their goals.

Last year, I helped the team reimagine some ways to collect data on student behavior through the use of rubrics, checklists, and goal trackers. We also had teachers write anecdotal notes about students while observing their behavior.

This year, the school created a new position, inclusion support coordinator. The first thing she did when she came in was to ask how we could collect data from teachers more easily. I was elated to hear this and suggested we use a Google Form that could be easily bookmarked to the browser, so that's what we did. She pushed teachers to use it when they suspected academic or behavioral issues with students. We also did away with the former ways of collecting data to try to save teachers' time.

The form responses before visualization.

With the data collected from the Form, we started by filtering the responses into the different programs; PYP, MYP, and DP. This was good, but after a month, we could see that it was hard to read. I also started asking my coordinator and principals what kind of data they look at when viewing it, and I started thinking about how to take their feedback, what I knew about what I would want to see as a homeroom teacher, and I started thinking of how. I could make this all visual. 

In my head, we would see the current weeks' totals along with the top five students. I also had this idea of a student lookup in my head.

The idea I had brewing in my head was a Google Form that could be easily bookmarked. In my head, we would collate the responses, and these would become the updated form question responses.

I had this other idea that we would have a lookup of student with the goals and the support plan. 

My demo of a student lookup 

Over winter break, as I spent the days at my in-laws house, I got to work making a dashboard. 

For this dashboard, I wanted a few things, data visualization, simple, more data, and better visualizations - right now, there's a lot of text.

Going forward, this exercise should show that data needs to be consolidated and accessible. 

Here's what it looks like.

Then I broke it. I committed the cardinal sin of editing the response sheet. After editing the response sheet, it no longer connected with the dashboard I made. 

The good thing is that I have a better idea of what I want to do next year.

Using AI for Formula Heavy Lifting

This post was inspired by an article in Life Hacker.

At the beginning of this school year, I had an interest in spreadsheets. I was also considered one of the more knowledgeable people in my school about them. 

I was Googling how to do many of the more complicated formulas. I felt like I was moving too slowly and so I tried using AI to help me with those formulas I couldn't figure out.

I remember when the AI boom hit. After I "discovered" ChatGPT, I found a site that would help you with Excel formulas. That site started charging to use it, so I tried using AI.

ChatGPT is better at Google Sheets Formulas than Gemini

There are many AI tools to choose from. I when I first started making this dashboard I started using Google's AI tool, Bard. I liked it, so I tried it with Google Sheets. I thought that since it is also a Google product it would but it was shocking to me how poor it was at handling Google Sheets formulas and solving problems I had. 

I tried ChatGPT because Bard Gemini wasn't cutting it and I didn't look back. I think ChatGPT is simply better than Gemini. I have since tried it, but it's not there yet. 

I never did get around to trying that other AI spreadsheet tool.

Pro Tip - When using AI for spreadsheets, give it the actual range you are using so it can add it to the formula for you.

Know What You Should See

When I work with students in math classes, I often show them that if they can estimate the answer before they solve a problem, they can tell if the answer they got is "in the ballpark" or not based on their estimate.*  

As with anything you do with AI, you should have an idea of what you are looking for - or at least be able to recognize when the result is not what you want. At this point, AI is a thought partner. 

The best results come when I know what to look for in the answer. For example, I often question ChatGPT, or tell ChatGPT when the formula it gives me can be streamlined. Sometimes I even find redundancies in the formula or answer. 

When Caught in a Loop, Start Over

I said before, but I will say it again, when you use AI, you should check the results. AI can give some very inaccurate answers. It can also lead you into a feedback loop where the answer is the same again and again. When this happens, I start the query over and try to break the steps into the smallest chucks I can think of to give the AI some grounding when thinking how to solve the problem.

Over the last couple of months, I have learned a lot more about Google Sheets and visualizing data. I do think that AI helped me to learn faster than I would have learned had I simply been searching for answers one by one.

* I am well aware that sports analogies are an Americanism and I do try to not use them when explaining concepts to people. But, sometimes, it just fits.

Import Data from Another Workbook

To be honest, I don't know the proper terminology. But, after spending about 4 hours working on this formula, I got it working.

The problem I was trying to solve was that we had a spreadsheet that was being updated regularly with data, and we wanted to feed that data into a different spreadsheet. 

I knew that. I could simply do this with IMPORTRANGE, which can get the data for each cell, but then I would have to create a formula for each entry. I wanted a single formula that matched the student name to the data.

I knew from past projects that the INDEX and MATCH combination was probably something that I wanted to use. So I started playing around with it. 

Student Name

student ID

gender

grad year

current grade

DOB

RTI

reading level

The particular data I wanted was the reading data. 

In the end, ChatGPT helped me figure out that the formula would be something like the following.

=VLOOKUP(A7, IMPORTRANGE("URL", "SHEET!RANGE"), dataCOLUMN, FALSE)

My next step is to make the formula a little more automatic by messing around with the data column part of the formula.

... and another version

=QUERY('Sheet 1'!A:O, "SELECT * WHERE B = 'COLUMN NAME'", 1)

Create a Timetable where You Can Count Certain Lessons

Made with Craiyon

Where to Learn Google Sheets

One question we received from the last Tech PD was about where to learn more about Google Sheets. Here are a few resources.

Ben Collins - Google Sheets Training [Courses]

Ben Collins is a data analyst who is also a certified Google Expert. (That's the actual badge name from Google.) He has several courses, most of which are paid, but check out the Advanced Formulas 30-Day Challenge, which is free.

Learn Google Sheets [Channel]

This is one of my favorite Google Sheets-related channels on YouTube. There are a range of videos here from beginner to expert.

Google Sheets - Full Course [Video]

An in-depth video all about Google Sheets. It is 3 hours, but there are chapters so you can jump around.

Google Sheets Advanced Tutorial [Video]

Another video lesson. This one is for users more familiar with Google Sheets.

If you just need an assistant ...

Excel Formulator [Website] 

This site allows you to type in what you want to do, and the AI will generate a formula for you. 

via Shukesandgiff.com

===

So, where to start? 

I recommend starting by actually trying to use Google Sheets in your work. Instead of opening a Doc, try doing the same task in a Sheet. Then gradually start seeing what functionality you can add to your work. Then maybe start by browsing one of the above resources.

Get the Average from Date from Checkboxes

I am trying to put together a sheet for tracking student scores based on criteria. I have made checkboxes (that work!) that when checked, give the averages of criteria. 

My next step is to make a formula that will gather all same criteria as the row in the checkbox and average them from that point. 

I have the formula that will get the minimum date and the matching criteria to that date. From here I need to figure out how to get the average of all criteria in a date greater than the date from the box that is checked.

Add a Label to Chart Data

I created a chart from some data. Gridlines would definitely help to make it easier to read, but a label would be even better. 

To add a label, go into the chart editor and click on Customize in the top menu. Then scroll down to Series. In the Series menu towards the bottom are three checkboxes - the last one is Data labels. You might need to change the format. 

Click on the drop down menu and select from source data.