When Real Data Hits, Things Get Funky (And That’s Normal)
I’m working on one of my favorite types of projects: a temporary spreadsheet solution meant to act as a bridge for an organization until they have more support and can move toward a more comprehensive tech stack and processes. I’ve built a ton of these over the years, so I usually know where to start—and where I’m going.
The General Need
The organization offers single, multi-day, and multi-week programming over the summer. Until now, they’ve taken attendance in aggregate—they could say how many kids attended each session, but not what each individual’s attendance rate was. Now they want to collect the data more granularly by participant, as part of how they measure and evaluate their impact. Taking attendance by student and centralizing the data is one part of a larger shift in their processes and systems.
The General Solution
I had just completed a similar project for a digital literacy nonprofit. I created a template for attendance tracking that allowed the central national team to create standardized trackers (for more than 500 trainings per year) connected to a central spreadsheet “database” or reporting tool. The more standardized the process, the more we could automate, and the more time and user error we could save. I wanted to do the same for this new organization.
Side note: The first org was already using Google Workspace, and I’m very familiar with how to do something like this in Google Sheets. I learned a lot, but I was confident it could be done. The new org is all-in on the Microsoft landscape, and I’m learning A LOT about how different it is to use Excel Online (M365).
The Framework: 3-Tab System
Much of the process was familiar. A big part of it comes from something I teach in my Spreadsheet Skills for Social Justice series: the 3-tab system.
If you have standardized sheets that don’t change in structure but do need to be updated with new data—like attendance trackers that look exactly the same but have different names of participants—you can automate this with three basic tabs:
Rawdata: This gets updated whenever new data is available (copy/paste or via integration). No formatting, no changing—just pull it in.
Transformations: Here’s where you shape the raw data to work for your needs—e.g., changing state names to abbreviations, splitting or combining name fields, or adding calculated fields like month/year from a date.
Reporting/Dashboard: This is where you format or display the data—attendance trackers, analysis dashboards, data visualizations, etc.
When Real Data Hit
In this project, I built the template so that on a tab called the Cover Sheet, someone could just enter the name of the program, and all the other tabs would fill in automatically:
Rawdata, pulled from an automatically updated Excel report linked to Ticketmaster via another process I built
Transformations to clean and structure the data
A tab where instructors could take attendance for all students on each day of the program
A dashboard displaying key info about the program and attendance
It all worked great—until real data came in.
That’s when things started to deviate from the standards and assumptions. Some examples:
People registering under the same name (possibly a parent registering multiple children)
Donated or sponsored seats showing up with the donor or business name instead of a participant’s name
Some programs lasting just a few days, others running daily for 8 weeks
Suddenly, the system I built had to contend with structures and quirks it wasn’t designed to handle.
What I Learned (Again)
No matter what kind of tech process or system you introduce—spreadsheet, CRM, LMS, CMS, automation—you will learn new things when real data shows up.
Outliers appear. Assumptions break. Systems strain. And you have to adapt—sometimes in small ways, sometimes in big ones.
We try to reduce the surprises by:
Building with real organizational knowledge
Testing with real data early
Stress-testing parts of the system before rolling out
But still—things always get funky with real data.
What To Remember Going Forward
At any point (even at “launch”), you might get real data that reveals a new kind of weirdness in the system. It’s frustrating, beautiful, and inevitable.
We’d be wise to build in time for this to happen—because it always will. And we should find ways to embrace it. Make it part of the process. Don’t let it derail you.
Adjust as needed. Account for the weirdness. And keep building.
