I recently had a dream about Steven Universe (my favorite TV show) where some wildly elaborate cookies come to life and then shiver dramatically, demanding to go into the oven to get toasty warm. Yes, I really do dream about dancing cookies and pubescent, genderbending superheroes! Welcome to my subconscious, y’all. At least, the cute part of it!
In the show, Steven and his pals (Garnet, Amethyst and Pearl, plus some human characters) live in a quirky town called Beach City and fight an intergalactic, good-v-evil battle to vanquish nefarious gems (and their deputies) from Homeworld while protecting Earth from assured destruction. As the story unfolds (with plenty of lyrical, palette cleansing, world building, not plot advancing episodes), Garnet and Pearl mete out historic memories about Homeworld, prior allegiances, tragedies, and monsters.
Each character is allowed different insights into past battles, secret powers, family ties, access to yet unknown bases and landscapes, etc. First Steven gradually learns the ropes of the Crystal Gems and then he brings his best friend, Connie, into the fold. They don’t always understand the full historic context of the monsters they are battling (and neither do we, as the audience!) but little by little, we learn the backstory. It’s a beautiful literary device that requires patience and commitment from all parties! Kiddos can enjoy the delightful animation and the suspenseful battle scenes, but for “grown ups” there is much more below the surface!
This metaphor came to mind while I was doing a complex spreadsheet project this week! Each person needed to see exactly their view of the data without seeing each others’. The overall goal was to centralize information for an end-of-year fundraising drive without exposing sensitive information about major donors to a large group of volunteers. One option was to make separate spreadsheets for each person, but I thought that would create a big administrative burden for the staff and coordinators of the campaign! Another option was to make filters and let each person filter for their slice of donors. The problem here is that in Google Sheets, a filter applies to everyone who’s viewing the spreadsheet! This has caused some major headaches in the past, so I knew it wasn’t the right solution for us. Enter, spreadsheet magic!
The problem
We have a team of volunteers working on a project who shouldn’t access each other’s data.
The solution
Use Google Sheet’s “Filter View” feature to create custom slices of data. Then, use =Hyperlink() formula to easily create a central repository of all of the Filter View links for easy access and usage.
The caveat
This solution assumes that my volunteers will respect the system and not snoop on each other’s views. However, it does not PREVENT them from seeing each other’s data. If you need it to be super private, this method is probably not right for you – and you might need to go the route of completely separate spreadsheet docs.
The steps
- Get to know your data! How many columns? How many rows? If you are going to be creating filters, do the rows you need to filter have data in every column?
- Create “filter views!” This step is easier than you might think! Watch this helpful .gif for a detailed demo. Prefer written instructions? I got you! Here’s a helpful guide from a reliable source.
3. Create a “Table of Contents.” Creating filter views is great, but how will people access the filter views? The menu is certainly an option, but you have to go there and find it, which might not work for people who are not as tech-savvy. Plus, there might be extra filter views there that are not relevant. First, I created a new tab in the bottom of my Google Sheet window. I named it “Table of Contents.” Then, I used the Hyperlink formula to create a list of peoples’ names, where each name linked to their specific Filter View URL.
Here are some helpful instructions straight from the Google Sheets help team! The nice thing about the Hyperlink formula is that you can hide a link inside of some nice text, so that you don’t clutter your spreadsheet with https://www.etc-etc-etc.com/blahblah. Look at the image above to see how you can include a “text label” with the text that should be hyperlinked.
Future applications
What’s that, Garnet? You see future options to use this tool to help freedom fighters achieve our goals? Pssssh you’re so good! (This is an inside joke for people who watch the show).
As I was working on this project, I realized that Filter Views could be incredibly useful for other types of “data dashboard” / “key indicator” types of projects. Google Sheets has an inherent limitation that when you “Filter” data, EVERYONE sees the filter. So if I’m trying to look at the big picture and someone else is trying to see “all donations under $50,” suddenly my view can abruptly change. Yep, in real time. Yikes! This is especially nervewracking (imo) if someone leaves a filter on, exits the spreadsheet, someone else opens it, and draws conclusions from the data WITHOUT REALIZING that it was filtered. Mega yikes!
I’m interested in exploring a cultural shift where instead of filtering the data tabs directly, we all start using filter views. Then, we could make a Table of Contents slide for all of the relevant filters that people might need, like “lapsed donors”, “major donors,” “donors in PA” or to use a different example, “Invited to bday party,” “RSVP yes,” “RSVP no,” “no response.” I think this could have added bonuses of saving time, helping people see more actionable/useful views of data, standardizing how to apply filters the same way every time, and avoiding the filter dilemma that I wrote about above.
Filter Square, Population One
Fit-ler Square is an upscale neighborhood in Center City Philadelphia with a beautiful dog park where I like to hang out sometimes. However, in my mind, I’ve started to call it Filter Square.
I might be the only one in this Filter Square mindset, but I’d love for you to join me or at least try it out! And if you do… leave me a comment! I’d love to hear from you and see how we can extend this framework to help even more grassroots data pals work their magic.
“This is especially nervewracking (imo) if someone leaves a filter on, exits the spreadsheet, someone else opens it, and draws conclusions from the data WITHOUT REALIZING that it was filtered.”
SO TRUE! I still have stress dreams from 10 years ago when I was an intern, still in college, and I sent out a file with my analysis that was filtered and someone on the phone call where I was presented it was like “I ONLY SEE THREE POLICIES” and it was a few moments of terror before I figured out what was happening.