Seasons are a-changin’ and I think I’m getting into “spring cleaning” mode. Maybe I’m also inspired by the new, baby lambs that I see frollicking on Instagram, or my farmer friends starting their seeds. Or, perhaps I’m just a little giddy about rolling out Lightning Experience at work this week. Or, it could be that I finally, FINALLY hung art up in my office, after months of ordering, shlepping, framing, and requesting. Change is abundant! My surroundings (software, office, and outside environment!) are getting more beautiful as the days go on.
That being said, I’m also slogging through some hard times where the data are NOT alright. From reconciling headache/pound-the-table-and-cry-on-the-couch inducing payment transaction failures to logging ticket after ticket of support cases to exploring a new workflow management tool, nothing seems simple or easy. None of these are problems that I’ve solved before. While I appreciate the challenge and I care deeply about the folks who are digging in to these problems with me, I also acknowledge the uncertainty and even despair that comes along with feeling helpless in the face of techno-chaos. If you’re reading this and you can relate, know that you’re in good company!
I think about the visionary, creative, problem-solving work that I aspire to do, and sometimes I wonder if it’s “lipstick on a pig.” All of this work with Lightning (months! months!!!) is really just a new wallpaper on the same, old system with the same, old problems. I’m trying to balance introducing the feature enhancements and usability improvements while explaining that the stubborn bugs and inefficiencies are still there – but hopefully less prominent. Same with my office decorations. It’s still pretty much a generic white box, but now it has 4 pieces of art on the walls! I guess what I’m trying to say is, I have to accept that change is incremental. And… piggies are really cute 😉
This line of thinking raised some worthwhile questions about design… I guess what techbros would call “user experience” (which was a term I’d never even heard of a few years ago). Given certain structural limitations about technology, what can we do to make it as helpful as possible? Ahh, self, I’m so glad you asked! I believe there are simple things that ANYONE can do to make spreadsheets easier to build, use, share, and maintain. *Enter soapbox* *ahem* As changemakers, I believe we have a responsibility to hone our craft and use effective, efficient tools that promote empowerment rather than impostor syndrome. So, next time you need to make a budget, a project plan, RSVPs, organizing plans, projections, or any other spreadsheet, consider trying out these easy tips. It might be “lipstick on a pig” but (1) pigs are cute and (2) lipstick is cute and (3) it might make a huge difference for your co-conspirators!
I CANNOT believe I am saying this, but in the research I did to prep this post, I wound up on a website called Journal of Accountancy . com and yes, that does sound like the boring-est resource ever, but they put together a REALLY thorough “listicle” of best practices for good spreadsheet design. Some of their pro tips are themes that I’ve written about on the blog in the past. Check out their article here.
Here are the two most insightful tips of all: “avoid embedded assumptions” and “organize assumptions well.” For example, instead of creating a formula like “19.99 x .08” why not have a section for your assumptions where you can notate “Philadelphia Sales Tax: 8%” and then make a formula that is “19.99 x PHLsalesTax.” It’s pretty easy for anyone using the spreadsheet to know that PHLsalesTax is 8% or go find the cell called PHLsalesTax to find out. Much, much simpler than having to rack your brain to figure out what .08 represents. Even better, you can change the PHLsalesTax value to 8.5% to create a what-if scenario if the tax rate increased… without having to re-do all of your formulas. Here’s an example from the article above of assumption rates that are probably referenced in formulas elsewhere in their modeling sheet.
Sure, changemakers generally aren’t thinking about real estate taxes (although HELLO 10-year tax abatement in Philly… or should I say, GOODBYE!) but we do use assumed rates all the time. Some examples off the top of my head include: hourly pay rate, number of hours, voter turnout rate, donor conversion rate, volunteer hubs, number of eligible volunteers, etc.
Being clear about assumptions is a “best practice” regardless of spreadsheetlandia. Lots of groups that I volunteer with are consistently working on being transparent about their goals, the services they provide, the assumptions that may never see the light of day. It’s a healthy and worthwhile exercise to examine assumptions, so why not get the practice while building spreadsheets? Do you assume that everyone knows the Sales Tax rates? Do you assume that everyone is familiar with your Chart of Accounts? Do you assume prior knowledge when you present charts and graphs? If you do, take the opportunity to untangle those assumptions. Leaving a breadcrumbs trail in your spreadsheet will (1) not attract mice (2) also help YOU down the line when you open up the sheet and can’t remember what the heck you were doing. It all seemed so obvious (to you…) at the time!
I try to be vigilant about avoiding jargon and assumptions about blog readers. You should always tell me if I miss the mark; while I want this blog to be hyperfocused on social justice and data, I also want to make it accessible and fun to read!
Format as trough
Well, for humans it’s “format as table” but we’re going for a farm animal theme today and I wanted to include some horses! I humbly believe that Format as Table is the single most effective thing you can do to make your spreadsheets easy to use and easy on the eyes. Here’s a GIF that walks you through the steps. In just a few clicks, you have a colorful, sortable, expandable, formatted array! Hooray!
Now, let’s put those two ideas together. What happens when you combine a Table and Formulas With Clear Assumptions? Well, watch the GIF below and see what happens!
Notice how the Table is smart enough to know the “column name” in the formula AND copy the formula down to the bottom. It’s like a one-stop-shop for everything you need in a healthy, useful spreadsheet!
Ahhh, if only it was this simple to accomplish morning barnyard chores…
We can’t fix all of the tech problems at once and we can’t win all of the liberatory campaigns at once – as much as I desperately want to. So for today, I’ll have to be content with lipstick. I don’t believe that the lipstick is hiding or avoiding the messes underneath, but rather, making it more sustainable to keep working on those stubborn roots. I am thrilled by the artwork on my walls, the sleek formatting of a well designed spreadsheet, the contemporary flair of Salesforce Lightning. I know that these aren’t everything we need, but they’re still interventions informed by careful strategy, implemented with intention, and reducing strain. For today, that has to be enough. I celebrate the “pig” and I celebrate the “lipstick” because both represent the culmination of my and our very best efforts.