Multi-player spreadsheets are awful
"Everything Starts Out Looking Like a Toy" #96
Hi, I’m Greg 👋! I publish this newsletter on finding data products and interesting data observations with the goal of finding patterns and future product insights. (Also, it’s fun.) If you need a background on how we got here, check out What is Data Operations?
This week’s toy: you can rent a giant Etch A Sketch. (No word yet on whether you need a couple of people to help you shake it if you want to clear it before doing a new drawing.) Edition 96 of this newsletter is here - it’s June 6, 2022.
The Big Idea
A short long-form essay about data things
⚙️ Multiplayer spreadsheets are awful
Spreadsheets are excellent for certain tasks. They are a great way to create a model for what happens with changing data, display a visualization of information that’s occurred, and create “what-if” scenarios that use linear programming to solve a problem like a production bottleneck. Spreadsheets let you take information about a problem and model it into lists and sets of data to analyze, pivot, and maybe even a spreadsheet viewer or a game.
The true power of spreadsheets lies in the ability to share these insights with other people. Placing your assumptions into a model makes it easier to share in a connected document or to summarize a list of data into a dashboard. Even if you’re not using a linear programming model to solve a problem, you might need a spreadsheet template to get the opinions of multiple people, to share an Eisenhower Model 2 x 2 of a problem statement, or to stack rank opinions so that you arrive at a solid group decision.
Here lies the problem. How do you make spreadsheets useful for a group, enable individuals to take action in structured ways, and also avoid the problem when they break the model you are using because they change the data, update columns and rows, and generally cause problems as they use your collaborative spreadsheet in the same way they usually use their own spreadsheets? When you give the ability to write, you also give the ability to update data.
Building Collaboration into your spreadsheet
Asking people to follow instructions in a spreadsheet generally feels like herding cats. While some people will go so far as to follow exact instructions, something on the order of “find the column with your name on it, and update only the cells in that column by selecting APPROVE, DENY, or DISCUSS”, most people will act like chaos agents and start writing their own notes in a comment or in the field you asked them to fill.
So what? Why would you care if people follow a process as long as you eventually are able to get them to see the point of your spreadsheet, and to make a decision based on the data there?
Here are a few typical errors that happen:
Errors based on filtering the wrong data for everyone
Errors based on updating or overwriting formulas
Not understanding a chart or a pivot table and accidentally changing it
What would a better model look like? It would look more like an application that displayed relevant screens and disclosed information progressively depending upon what you needed to know.
The basics of a spreadsheet application
If we think about this as an application problem with a few levels of access, one thing that’s missing from spreadsheets like Google Sheets or Microsoft Excel today is the collaboration layer. They do have comments, but those things feel limited for the following reasons:
you can lock down some of the fields but if you want comments you need to grant certain permissions
manipulating the underlying data is a higher level permission than reading it, so if you are using tricks in the spreadsheet like data validation lists to create dropdowns that change the view, that’s not possible
Spreadsheets in their current form don’t behave like applications.
people can easily change the view shared by everyone
views don’t have an empty state and it’s easy to to break them if they depend upon data from another range
There’s no easy way to provide contextual feedback
there isn’t a concept of a view powered by hidden data that can’t be manipulated, unless you manually lock the sheets where that data resides
Spreadsheet applications rely on a fragile model that works best in single player mode. Here are some examples of why they don’t work well when you share them:
spreadsheet models are based on the author’s assumptions - what’s the key data that you are using? They don’t have an affordance for explaining why you are using that data and the meaning of it.
the formulas you are using are validated for functional signature but not for correctness and validity in the context of what you’re doing - the mechanism of transforming the data. Formulas don’t have a global library like an application that makes it easier to use someone’s (correct) solution to a problem.
the visual charts in a spreadsheet often need to be updated to include information outside of a fixed range of data. Charts, graphs and pivots rely on the ranges of the data to be in a certain place and are pretty brittle if moved.
However, we keep using spreadsheets as applications, whether it’s to get agreement on a group process or to get feedback for ongoing work. What could make this better?
Thinking about a better multiplayer spreadsheet
If you could start tomorrow and build a better multiplayer spreadsheet, you might start by following the principles you would use to develop applications, where:
the logic for a “spreadsheet app” is declared and discoverable;
inputs and views are loosely coupled to data storage and don’t break if the user changes the inputs for their particular view;
there is an advanced view where you can update the formulas of individual cells, and then let other people know that you’ve made an interesting change (a “what-if” scenario)
it’s possible to review and comment on an application without breaking it
To build such a structure you’d need a flexible schema - the rows, columns, and other data structures that provide the global information for the app and the local information for this spreadsheet or model - that looked mostly the same for all users of the app. But, you’d also need temporary or local storage (in memory would be fine) to capture whatever changes the current user made and might want to share with the other players.
The data - or actual information you put into this schema - needs to be time stamped (like it is already under the covers in a Google Sheet) so that the system can keep things in sync while watching changes that happen from multiple participants. Perhaps there would be both a public and a private view, where you can preview the merged version of this information while also seeing your own private view.
Because you also need to validate the information people are adding to this application. Validation or applying rules to the data so that it is appropriate and allowable for this schema will let you know whether the change you are suggesting to the model is a change that will break things or something that might unlock a new insight. And while you are watching these changes, providing a streaming flow of updates so that you know who did what when will add to the reporting and logging needs for this application.
I’d like to use an application like this to start from a “best practices” model where you take someone’s solution for a problem and then apply it to the data in your own business. Think of a version of Stack Overflow where instead of cut and pasting a few lines of code, you could copy an entire model. Matt Wensing is doing something like this with Summit, and I’ve seen a few other similar models, like what Divyansh Saini is working on with Houseware.
What’s the takeaway? The next version of spreadsheets and decision models will look a lot more like data-driven apps, where an underlying structure based on a unified data model will make it possible to explore possibilities that start with shared metrics and end with entirely new observations. If it makes the next group stack ranking model better, I’m all for it ;)
Links for Reading and Sharing
These are links that caught my 👀
1/ Spotify Wrapped and Emotional Design - Spotify has made headlines for their end-of-year compilation of music listening called “Wrapped.” This is a great example of their overall product strategy in creating a personal application experience for every user. Spotify excels because it creates a single player experience within a multi-player game and makes both of these experiences work together. The product design reinforces the product experience.
2/ How do new hits emerge today? Thank Tiktok - Another version of product design creating a product experience is TikTok. In this explainer from Vox and the Pudding, we learn how the virality of TikTok posts is driving the music charts on Spotify.
This is not just a case of music scouts or tastemakers finding the new new on TikTok - it’s a flywheel dynamic that is changing the way the music industry engages with Spotify. It’s another example of a single-player experience (finding a viral hit) within a multiplayer game (TikTok).
3/ The classic example: a phone network - a true network effect requires at least two nodes. Bertran Fand, an engineer at Slack, installed a pay phone in her house so she could talk to her daughter from the next room. This is probably not what the engineers imagined when they designed a pay phone network, and it also a totally cool way to use retro tech.
What to do next
Hit reply if you’ve got links to share, data stories, or want to say hello.
I’m grateful you read this far. Thank you. If you found this useful, consider sharing with a friend.
Want more essays? Read on Data Operations or other writings at gregmeyer.com.
The next big thing always starts out being dismissed as a “toy.” - Chris Dixon