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.