On February 16th, 2017, I gave a presentation to a packed house entitled “10 Google Sheets to Blow Your Mind (plus some tips and tricks).” Here is the description of my talk (you can skip it if you were there 🙂 :
Google Sheets is a free online data management tool that is part of the Google Apps For Education (GAFE) suite. As part of a “Google District” with an almost 1:1 Chromebook situation, learning the GAFE tools is a key to our success. But, who likes staring at a spreadsheet?
That’s where I come in! As a computer nerd who completed a quantitative dissertation, I’ve probably made tens of thousands of Excel and Google Spreadsheets. Data is my thing. But data isn’t useful until you do something with it. You have to make visuals, create graphs, simplify the data, and make connections to patterns and trends.
At Massillon, we utilize Google Sheets in a multitude of ways. In this session, I’ll show you the formulas, tips, tricks, visuals, and methods behind how I created several 10 different data analysis tools:
- Progress Monitoring Sheet with benchmarking, trendlines, and scores.
- A Subgroup Tracking Spreadsheet with State Test Scores.
- Common Assessment Tracking with auto-populating for data reports.
- TBT (Teacher Based Teams) Monitoring and Feedback tool (for administrators) with automatic creation and sharing of the report sent to teachers (using autoCRAT add-on)
- Middle School Walk Through Feedback Form, aligned to OTES
- Interim Report with Weighted Grades
- Graduation Points Tracking Spreadsheet (merging close to 100 spreadsheets using VLOOKUP)
- Certificate of Completion automatically created upon course evaluation.
- Ten Cycle TBT Data Collection Form
- Calculating the Effect Size of Your Instructional Strategy (Hattie)
I’ll provide a walk through of each tool, and an example on my website so you can download these tools for yourself. I’ll also share videos and resource links to walk you through every step. Get excited about harnessing the power of your data!
(as I finish each tutorial, I’ll hyperlink the posts. I’m only on the first one!)
So, I had promised many of the members that I’d share the Google Sheets Templates with them. I’m going to remove my district’s data, and put in some placeholders, and then share with my fellow techie educators.
The first tool I’m going to share is our “Fundations Progress Monitoring Sheet with Benchmarking, Trendlines, and Scores.” Fundations is our K-3 program to support literacy and reading skills. It is multi-tiered, supports research- based instruction, alongside Tier II supports for students at risk for reading difficulties. The problem? Teachers and intervention specialists used either paper charts or the Fundations-supplied online tracking tool that was not flexible. So when our Title teacher came to me and asked me to create a technology tool, I was up for the challenge!
What were they doing first?
She showed me the paper tool all the Title teachers were using.
Here’s things we need.
Here is what you should take note of:
- On our Y axis, there are the Number of Letters Named Correctly.
- On the X axis are the dates of the “probes.” Note that we started with a baseline (the student has to start from somewhere. They probably are not on zero).
- The benchmark (or “aim line”) is how the student should be progressing over time. They should be progressing towards their mid-year benchmark.
On the paper version, there is no way to tell if you are “doing it right.” The only way to check your work is to have someone else look at it. In my humble opinion, there was a lot of room for error. The online tracking tool provided by the company was rigid – it only worked exactly the way the company (not the teacher) wanted it to work. And, it wasn’t easily shared with principals, the curriculum department, or members of their teams.
Where to start?
In comes Google Sheets. Here is the link to the Fundations Progress Monitoring Template. Make a copy, and then you can name it whatever you like. (And if you are a lead teacher or principal or tech person, and you are sharing with teachers, make this initial file “View Only” and instruct your colleagues to make a copy or send them to my website. If you give the first and only copy to a group of teachers, invariably, someone will change a formula or mess up someone’s data, and you’ll have unhappy teachers!)
Put it in a folder that you share with the team. Our folder contains 1. A “Templates” folder. 2. A folder for each of K, 1, 2, and 3. 3. A folder within for each of our 3 schools. Our lead teachers, principals, curriculum director, sped director, and I have access to the whole system. Then teachers at each grade level have access to their own grade(s).
What do we have?
Here’s what we’ve got:
- Make each student his/her own sheet. I always use the naming convention “Last Name, First Name, Teacher, Year, Fundations Progress Monitoring.” It’s important for finding documents again that it be named properly, in the right folder, and that all your teachers use the same naming convention. TRUST ME ON THIS!
- The number of probes are 15 (more later, but if you change the number of probes, it changes the trendline).
- The student’s score.
- The date the probe was administered.
- The name of the Intervention.
- Initials of who administered the probe.
- The Benchmark score (I made that using Insert -> Drawing -> Made a textbox -> Changed the outline to pink and 8pt)
Now, the techie goodness!
Note that the good stuff is hiding behind the chart!
Trendlines – it’s the GOOD STUFF!
There is the function that returns our trendline. It is the formula that takes us from where the student begins, to where the student must progress in order to achieve the benchmark. The trendline is the actual, moving line, while the benchmark is the fixed amount the student needs to score. As the student scores, if the score stays above the line, the student is making adequate progress. If the score dips below the trendline, the student is not progressing as quickly as needed, and if the student’s score matches the trendline, they are right on target.
So, to do a trendline, here’s what we need: =IF(ISBLANK($B$8),,$G$8+($G$23–$G$8)/15*A8)
- The equals sign, to begin the function
- The IF function (Returns one value if a logical expression is `TRUE` and another if it is `FALSE`.) This will place the line on the chart if there is data to place it, but will not if there is not data. That’s why there has to be an initial score for the function to work)
- Parentheses to contain the argument
- The ISBLANK function checks cell $B$8 – our student’s score of 3, which is their initial score on the probe. If it were blank, the function would not proceed. It is not blank (it’s a 3) so we move on with the function.
- The two commas (if the value is true)
- $G$8 is the first cell with data. We add (+) (cell $G$23 which is the benchmark score minus $G$3 which is the initial score) divided (/) by 15 scores times (*) the number of probes given). You never thought you’d use Algebra from high school again, huh? TAKE THAT!
- And our point is placed on the chart, and the line is drawn. The trendline stays perfectly straight, by using that formula. Notice that if you click in the cells in that column, it keeps moving towards the benchmark, step by step.
- If you click anywhere in the function, Google Sheets Help for functions will pop up, highlighting your arguments, and where you may be having troubles.
So, why would we go to all this trouble? I like to call this the “So What, Who Cares?” part.
So what, who cares?
- Instead of “One size fits all” instruction, tutors and teachers can make informed decisions.
- We can measure the growth and progression of our students, based on their own real data.
- We can change our practices that do not produce growth, instead of staying the (possibly) wrong course.
- We can compare programs for effectiveness, instead of taking a company’s word for it.
How did you do that?
As I said in my talk, there are some GREAT resources and Youtube channels out there for help with Google Sheets, Excel, and functions. Here they are, listed again.
- Presentation Template – Slide Carnival – Iras
- Link shortener – tiny.cc
- Google Spreadsheets Functions List
- GCF Learn Free – Google Sheets Function List
- Excel Is Fun – Youtube Channel
- Google Sheets Flipped Learning Tutorials
- Sync Sisters Learning Tutorials
- How to make a Trendline
- How to Use Conditional Formatting in Google Sheets
- 4 Tips for Sorting in Google Sheets – Alice Keeler
- Excel is Easy
- Sali Kaceli – Technology Simplified
- CONCATENATE to combine names or make email addresses