Pals, I’m currently experiencing the very exhilarating feeling of having a popular Tweet. As of writing, the bombastic missive that I sent out into the world yesterday has garnered 70 likes, 10 re-tweets, and a slew of banter-filled replies from people around the world. Rest assured, I was not making a comment on the Democratic primary. Rather, I was sharing a “fun fact” that I learned about Salesforce… that you can leave little notes inside of complicated formulas to give colleagues (and future you!) clues about why the formula works the way it does. Those notes are called “comments” and while they are common in most coding languages, it was news to me that we could embed them in a formula in this way.
What. A. Gamechanger.
I can’t even count on my fingers and toes the number of times I’ve opened up a formula field that I, MYSELF, WROTE only to have no clue what the heck I was thinking, what the 18 digit number/letter unique ID’s were referring to, or why the formula was necessary in the first place. And that’s not even counting the formulas that I inherited!
If this is news to you, like it was to me, today is your lucky day! Here are the instructions for how to DIY:
Audience participation
I had a check-in this week with my spreadsheet soulmate Emily and they rattled off a concept that blew me away. If I remember correctly, it was “we all need to think about if a spreadsheet will be used for tracking, displaying, comparing, summarizing, analyzing, visualizing, or (…) sharing a dataset.” It sounded so simple, and yet so insightful to put into words. A spreadsheet is all about how to make data useful for an audience of people, whether they are co-conspirators in your changemaking efforts, a co-hostess of a birthday party, or the boss at your day-job.
I think it’s a best practice to design ALL spreadsheets as if they will be manipulated by other people, which means leaving a strong “breadcrumbs trail” of your assumptions, formulas, variables, data sources, and more. In my case, it’s a matzo meal trail until tomorrow evening when Passover ends and I will devour pizza with all my might!
When I have a clear answer of the audience/purpose of a spreadsheet, I can create the best possible supplemental materials. Here are some of my go strategies. I use these in my everyday spreadsheeting and I encourage you to try them out, too, and then let me know how it goes!
READ ME… like one of your French girls
One of the best things you can do to help a spreadsheet weather the test of time is to create a Read Me tab. These are the things I like to include there:
- Spreadsheet purpose
- Data source(s) and criteria and date of last refresh
- Table of Contents (what each tab is for, in order)
- Privacy (is there any sensitive data that people should be careful with?)
- Any “head’s up” announcements or FAQs
- Anything that needs to be updated periodically (like exchange rate, IRS reimbursement rate, fiscal year, staff names, etc)
- Uncommon abbreviation/acronyms or definitions
- Email address of a real person to ask for help if they get stuck
OMG that sounds like a lot of work, right? Yep. It certainly is. But I honestly, humbly believe that this will save you or someone else time and exasperation in the future – and I live by that principle myself! #2 is especially crucial. I regularly download data (whether it’s from the census or Salesforce or anywhere else!) and by the time I need to download it again (maybe it got messed up or maybe it just needs to be refreshed as time as passed) I have forgotten where I got it in the first place, or what assumptions I used to filter down the data. Now I have a whole spreadsheet situation that I can’t recreate and it can become (1) useless or worse, (2) misleading! Y I K E S. Better to leave a breadcrumb trail the first time!
Form + U + La’s
Spreadsheet buffs might (in my opinion) cut some corners when making formulas, including typing extremely long math equations like Salary = 40*20*1.22*48… which tells the rest of us, well, absolutely nothing about the original assumptions of the math itself. Is that 40 hours/week at $20/hour or 20 hours/week at $40/hour? It would be impossible to figure it out!
Instead, why not create a key so that each number is connected to a label? Imagine a spreadsheet that looks like this…
| A | B | |
|---|---|---|
| 1 | Hours/week | $40 |
| 2 | Dollars/hour | $20 |
| 3 | Tax rate | 22% or 1.22 |
| 4 | Weeks of pay/year | 48 |
With a format like that, you can create a formula that looks like this:
Salary = B1*B2*B3*B4.
This has some huge benefits, including (1) you know what each number means and (2) you can change one number (in a cell rather than a formula) and see how it impacts your results. Super useful for budgeting!
Plus, you can take this one step further to create “human readable” formulas which are like the Vespas of breadcrumb trails imho (hehe). If you give each of the cells in the B column (above) a “name,” then your formula will look like this:
Salary = [Hours/week] * [Dollars/hour] * [Tax Rate] * [Weeks of pay/year]
Google Sheets and Microsoft Excel are smart enough to reference back to the right cell, even if you give it a more descriptive name to replace “B2.”
What was that? You want to watch a video and learn how to give a cell a name and then reference the name in a formula? Well, I have just the vid for you! I use this strategy ALL THE TIME and I still learned new things in the video. It’s only 1:46 minutes – well worth it.
Documentaries are “in!”
As Netflix churns out more and more awesome documentaries and older documentaries like Helvetica remain ever popular, I think the artform is experiencing a renaissance as a creative and entertaining learning/storytelling experience rather than a snoozefest. I want to see the same thing happen with spreadsheet documentation.
In so many ways, providing clear, concise, friendly, up-to-date instructions about how to enter data, change, formulas, or complete other spreadsheet tasks can be completely transformative for a social justice organization. One one hand, you be more impactful, save time, and have better data! On the other hand, you can reduce frustration and produce resilience. What a win/win/win/win! And you don’t have to be a spreadsheet whiz to succeed. In fact, I think folks who are slightly more hesitant on the spreadsheet-side-of-things are the best suited to write instructions because they are more likely to support other people who are new to the system.
At work, I have a whole software platform singularly dedicated to documentation. Every week, I set aside time to write and update “articles” about how to use various features of the Salesforce database that I am in charge of. But documentation does not need to be nearly so complex/intimidating to be effective! Just think about (1) what your audience needs to be successful and (2) what assumptions/decisions you made that may not be obvious and then (3) write them down making each step nice and clear. See what I did there?
Wherever possible, I like to write instructions as CLOSE to the action as possible (like in a Read Me tab of the spreadsheet itself) but in more complicated scenarios where you need to write documentation about how several spreadsheets relate to each other, or WHEN to find a specific spreadsheet and update it, the documentation needs to live somewhere else. I’ve seen people tackle this dilemma with separate files for each task, or one mega-doc with tons of instructions, or even a “wiki” dedicated to instructions. There’s no perfect format… I would encourage you to pick one and stick with it until it is no longer serving your goals!
Spreadsheets are for all of us (yes – even if you think you’re the only person who’s ever going to use a spreadsheet … I’m here to say that you’re probably wrong!), and when we start designing with that premise in mind, we can accomplish amazing “organizing” feats! I can’t wait to hear about your wins, both in justice work and your behind-the-scenes systems. Where ever you fall in your spreadsheet learning journey, I believe in you!
It sems to me that Hours/Week should be 40 and not $40, just a number.