Technology Blog

As school leaders, we know that data drives decision-making. From tracking student progress to identifying intervention needs, our spreadsheets hold the key to unlocking student success. But too often, we find ourselves stuck in inefficient workflows, spending more time organizing, manipulating, and simplifying data than actually using it to make impactful decisions.

Over the last 10 years, I’ve worked in various school leadership positions, in schools with as many as 4000 students (in just 9th through 12th grade). And, because public school budgets are what they are, our student information systems (SIS) are also out-dated, poorly designed, or heavily restricted to not be able to do what should be easy for an SIS to do. As a result, I have seen school leaders of all types make repeated mistakes that cost hours and hours of work, simply because they don’t know better.

My response to this problem was to combine my years of experience in computers and programming before I went into education, with my understanding of the needs of a school leader, to create systems that save me hundreds of hours of work, and lead people to ask me “how do you do that?”

Here are three repeated mistakes I see school leaders make that can be easily fixed.

Mistake #1: Generating Data for a Singular Purpose

Many school leaders create spreadsheets for one-time use—a tracking sheet for the most recent test data, a behavior log for one specific program, a check on the number of absences or tardies for the last few weeks. Then, a few weeks, a quarter, or a month passes, and they do it all over again with the new report data.

Instead of thinking of your spreadsheets as temporary solutions, start building systems that adapt and grow with your school’s needs. A well-designed Google Sheet should:

• Import the raw report data from the source
• Update dynamically without requiring manual input
• Be structured in a way that hides the “work” of the sheet, and simply shows users the results

The Fix

Desig your spreadsheets with the future in mind.

One tab on the sheet should be unmodified, raw export from the source data. On a separate sheet, use INDIRECT, FILTER, or QUERY formulas to reorganize the raw data on a new tab in the needed structure to run calculations on. Once the Calcs tab is built, add a Data Display tab that outputs the organized results in tables or actionable student lists. The end user only interacts with the data display, and when you update the raw report, the data display dynamically updates.

Mistake #2: Wasting Time on Repetitive Tasks

Those that aren’t familiar with the intermediate and advanced formulas in Sheets and Excel will use tools like filters, sorting, deleting rows, and copying data from one sheet to paste into another. This all has to be redone every time that you get new data.

For example, you run a report on student absences. You then filter to show only those with more than 3 absences in the last two weeks, copy that list of students and send it to your attendance dean. Sure, that might take you 10 minutes. But next week, you do it again, and then the next week, etc. etc. A 10 minute process once a week is 6 hours of work a school year. That is the power of dynamic Sheets.

The Fix

Use built-in Google Sheets features to automate these tasks.

• FILTER() functions to dynamically show only relevant data
• QUERY() functions to pull exactly what you need without manual work
• ARRAYFORMULA() to analyze data when the size of the dataset can change

Mistake #3: Thinking One Formula at a Time

Even school leaders that have an intermediate understanding of Google Sheets formulas like those mentioned above, often forget that formulas can be nested into each other. The power of formulas basically exponentially grow when you nest formulas within formulas.

For example, let’s say you have a report that lists class rosters for every student in your school, and each scheduled class is a new row. If you try to use a VLOOKUP() formula, you will only return the first result. If you try to use a FILTER() formula, it will return multiple results per student, which will cause an error depending on where you try to output it, since it can’t overwrite other data.

Instead, consider using a FILTER() inside your VLOOKUP() as the range to search through. VLOOK for the student ID in a Filtered range that only includes math teachers, for example.

• FILTER(), QUERY(), and SORT() can all be used inside a VLOOKUP() to control responses.
• VLOOKUP(), IF(), COUNTIF() and many more can be used inside an ARRAYFORMULA() to apply across entire columns regardless of data size.
• FIND() and LEN() formulas inside of LEFT() or RIGHT() formulas to manipulate and remove excess data from cells.

Make Your Data Work for You

If any of these mistakes sound familiar, don’t worry—you’re not alone! The good news is that fixing these habits will save you hours every week and make your spreadsheets work for you instead of against you.

That’s why I created the Google Sheets for School Leaders Course—an online training that walks school leaders through Google Sheets formulas, tips, and tricks to help them simplify and organize their data management strategies so that they can spend more time on the “decision making” part of data-based decision making.

• Learn how to structure your data for efficiency
• Discover shortcuts and automation tricks that save time
• Build a system, not just a spreadsheet

Get instant, free, access to the Level 100 course at BradShreffler.com/100!

Don’t let data management slow you down—checkout the Google Sheets for School Leaders Course now to start making all your data dynamic so you never have to do the same thing twice with your spreadsheets.

Yesterday, I had the pleasure of presenting a session on building morale during #MADPD. MADPD (or Make a Difference PD) is an full day online conference for professional learning put together by Peter Cameron and Derek Rhodenizer. This year, over 100 teachers presented either a session on a topic they were interested in, or a discussion panel.

Each session for this conference is 15 minutes, followed by 15 minutes of Q&A, and they are all done as YouTube Live events, so anyone, anywhere in the world can watch live or catch the replay. Below is the recording of my session. I hope you find it useful!

For the list of all sessions presented for MADPD, checkout this FlipGrid Conference Guide.

I was also on a panel with Shane Laurence, Randall Black, and Kasey Bell talking about Making Technology Disappear in Schools. This panel focused on the impact technology is having in school and how at some point “Technology Instruction” should just become synonymous with “Instruction.” You can watch that below as well.

Anyone who know me, knows I’m a technophile. I’m an instructional technology coach, a constant Twitter user, and podcaster. I Snapchat most of my life, text friends, have multiple active Voxer groups, and take tons of pictures of my son. My phone is in my hand constantly.

Last week, I popped into my friend’s office and started playing with these fortune card things. You shuffle the deck of cards and pick on that speaks to you. They’re really silly and sarcastic. I got this one:

Now, I hate baths, so all that stuff is garbage. But the idea of going somewhere and getting “unplugged” for a bit sounded like fun, especially since I already had plans to go to the beach for the weekend with my fiance and a couple friends of ours. I reached out to them and suggested that we make Saturday a Disconnected Day, turning our phones off when we woke up in the morning and not touching them until the next day when we wake up.

I thought it would be a struggle, that I would go through withdraw symptoms of sorts, but I didn’t. Sure, for the first hour or so I kept instinctively reaching for my pocket as if I would find my phone there, but after that it was wonderful.

I found that without my phone, I was more present in the day. We had to talk to each other, and actually pay attention. We talked about topics without immediately jumping to our phones to find the exact details and “prove” things. We played card games instead of video games. We read physical books instead of Reddit posts. We sat on the beach and enjoyed the waves and sand instead of framing the perfect picture.

Those were all the things I expected. What I didn’t expect was how clear it made my mind. Normally, my mind runs a thousand miles a minute in 4 or 5 directions. I’m always thinking about the next thing, or some project I need to finish. Rarely do I find that my mind is quiet. That’s productive for me, it works. I thought that given a day without the outside distractions of my phone, I would solve problems that had been plaguing me or have creative inspirations and new ideas.

Throughout the day on Saturday though, I regularly found myself not thinking. I was just experiencing the moment, enjoying the company, and being content.

I highly recommend the experience to everyone. We are so constantly bombarded by signals and information, and while I advocate for that level of connection the majority of the time, the value of getting away from it from time-to-time shouldn’t be dismissed.

I, for one, am going to start doing disconnected days once a month. I will be announcing it on twitter the day before each time and encouraging people to join me. Or, pick your own disconnected day.

To help, I’ve made a quick document to give you the rules and agreement. You can use it with a group of friends and spend the day enjoying each other’s company and the world around you.

Google Drive Disconnected Day Contract
PDF Disconnected Day Contract

As a teacher at a one-to-one school who uses G Suite and Canvas as an Learning Management System (LMS), few things are more annoying than the “You must request permission” screen.

This happens a lot for my teachers and I. You give an assignment description on Canvas, the student creates their work in a Google Doc or Slides presentation, they turn it in on Canvas, but they only share the local version from their computer, which is really just a fancy link.

Or, the student just gives you a share link, so you can see it, but only by loading the link in a new tab and not in the Spred Grader window. Annoying!

Google Classroom gets around this issue by creating a new folder for the assignment and putting all the created Docs there. It’s great because you can see the work in progress, letter-by-letter as the student works. This isn’t perfect either though, because Google Classroom is very limited in functionality compared to a full-featured LMS.

Often over the last few years I have said that eventually Google will spin-off that shared folder ability from Classroom so that you can use it elsewhere. Then, a few weeks ago, I got impatient and decided to stop waiting for Google and just make it myself.

And, Class Folder Creator was created as a Google Sheets Add-On. Now, simply by creating a by period list of student names and email addresses, you can create a folder for each of your students that they will create all their work in, and you will have access to all of it.

Here’s how it works

1. Click here to install Class Folder Creator (free).
2. Click Setup Sheet from the Class Folder Creator Menu.
3. Put your class name and largest class size.
4. Input the list of student names and email addresses into the associated columns.
5. Click “Create Folders” in the Class Folder Creator Menu .
6. Magic!

Now, each of your students had a folder shared with them on Google Drive with Their Name – Class Name – Period #. You will have a folder for each period, and within that, each of that period’s student folders.

When Johnny raises his hand in class and says they need help, you can go straight to his folder and pull up the document he is working on to start providing feedback and support.

When Johnny finishes his work, you will have access to it no matter which way he turns it in.

This was a labor of love, and like all Google Add-Ons is completely free. It is my first Google Add-On, so if you have any thoughts for improvement or comments, let me know!

You can find more details about the add-on at the add-on site.

On Google Teacher Tribe Podcast last week, I heard about a Google Docs Add-On called Story Speaker. In the description on the show and on the Add-On page, it was said to be for making choose-your-own-adventure style stories that connect with Google Assistant to be read aloud and choices made through voice. I remember the Goosebumps Choose-Your-Own-Adventure books from my childhood, so I was instantly excited.

I love story telling (see NaNoWriMo, my book, my podcast…), so while that feature in of itself seems cool, I started thinking about ways to implement it in classrooms. Today, I spent a lot of time playing with Story Speaker (and by “a lot of time” I mean my entire work day).

Overall: IT IS AMAZING!!

First of all, there is absolutely no coding required. The system is entirely based on levels of indent. If you indent a level, it knows that the previous level is looking for a response. How you phrase the bolded text on the next line is what it is looking for.

Story Speaker provides templates as well (see the green and purple buttons on the right side of screenshot above), so all you have to do is change out the text to make your own story.

Once you’re done, you have a few different output options. One of them lets you run a demo version on the same window. All you do is click “Play Your Story” and click “Play Story.” And that’s it.

You can also click another button to throw it to your own Google Home or play on your mobile device as well with Google Assistant.

But telling stories isn’t the goal. The real goal is to use it in the classroom. The way I’m doing that now is making a test using the tool. My students are student tech support, so I am building out a test in which they respond verbally to a read scenario that occurs regularly. See the example below.

This is something that my students deal with regularly, but I think they forget basics too, so it’s important that they’re reminded.

I can also imagine this being used for something like a lab. You can build out the program so that it gives the directions step by step, and they have to reply with the current result before it will provide the next step. It is really easy to make it a text-based chat, which would be better in that case. You can click this link here to see what I’m talking about. Type anything in the box to start it.

 

I will be playing with this a lot more in the next week to build some more features into it. I feel like this tool has a LOT of power, and I will share out as I do more.

Thank you so much Kasey and Matt for this info! It has me really excited!