Anyone who regularly works with spreadsheets knows the anguish of screwing up said spreadsheets.
It usually goes like this. You’re powering through – filtering, sorting, formulating – until you notice that one row of data looks off. After frantically checking a few more rows, it hits you. Something went wrong in your manipulations, and now you need to start from scratch.
Spreadsheets are great, but they have also destroyed many a nonprofit worker’s day. Fortunately, we can do things to reduce the chance of this happening…and keep calm when it inevitably does.
So! Here are my essential lessons-learned-the-hard-way for dealing with spreadsheets. 😌
1. Minimize any and all distractions.
Spreadsheets require focus. Make sure your environment is conducive to that – whether it’s by holing up in a conference room, or using headphones to drown out distracting office banter.
2. Work on your spreadsheets before lunch.
If you do your best work in the afternoon, then bless you. But if your brain gets fried by 2pm like most of us, don’t wait! Get this done while your brain is still at its peak. It also gives you buffer in the event of any spreadsheet catastrophes.
3. Before you do anything else…make a copy of your tab.
Before I begin manipulating a file, I always make a copy of the tab I’m working on (and rename it ‘ORIGINAL’). This way, I always have a way to refer back to the original data.
4. Duplicate your tabs 👏 as 👏 you 👏 go.
In my best Alec Baldwin voice, I’m going tell you the ABC’s of spreadsheets: Always Be Copying.
This can be the difference between 30 minutes and 3 hours of work. Every few manipulations, make a copy of the tab you’re working on. Undoing in Excel (or Google Sheets) is limited, and by doing this, you really save yourself if you discover that you made an error 20 steps ago.
5. Save 💾 your file after each step.
Haven’t we all experienced the horror of spending hours on a document, not saving along the way, and then having the computer crash on us? Save after every sort, calculation and cell fill – especially when working with a larger data set.
*Tip:* If you only learn one keyboard shortcut in your entire life, this is it. Ctrl+S (for Windows) and ⌘+S (for Mac) lets you save your files quickly and easily as you work.
6. Use colors & columns to ‘document’ your steps.
Some of us are visual creatures. 100 rows of data in all-white is fine for some….but if I know some data is from one source and I’m reconciling it with our database, you can bet I’ll use color & notes to help my mind make the distinction more easily.
So a file that may start out like this….
..will look like this once I really get to work:
Help yourself out. Use color in your cells, and add notes in new columns to help you keep your data straight.
7. Try to restore a previous version of your file.
Assuming you skipped the above advice, maybe this spreadsheet error is reeaaal bad. You know that you’ll basically have to start from scratch in order to make things right.
But wait! Try restoring a previous version of your file first. Our computers can save our work as we go, even when we’re not aware of it. It’s totally possible that there’s a hidden version of your file timestamped from before you made the ghastly mistake.
*Tip*: Google Sheets makes this super easy: just click File > Version History > See Version History. Computer files are a little harder, but here are some instructions for Mac and Windows. And when all else fails, you can try bribing your IT team with donuts.
And that’s a wrap! Treat yourself when it’s over.
I’m not quite saying that spreadsheets are like dementors…especially since I happen to like both. But I think we all have those exhausting Excel days sometimes, where it feels like our souls are being drained right through our fingertips!
So when you’ve spent hours manipulating a data file, a donut or other sweet of choice is a must. 🍩
BONUS – Spreadsheets are all about having the right tools at your disposal. That’s why I’ve started a video series on 1-minute spreadsheet hacks. Check ’em out!