Anyone who regularly works with spreadsheets knows the anguish of screwing up said spreadsheets.
It goes like this. You’re powering through for hours – filtering, sorting, formulating. Then you notice that one row of data looks slightly off.
After frantically checking a few more rows, it hits you. Something went wrong in your manipulations, and the data’s been messed. You need to start over from the beginning!
I know. As much as I love spreadsheets, they’ve also destroyed many of my work days too.
Fortunately, we can do things to reduce the odds of this scenario happening…and do our best to work backwards when it does.
8 Tips When You’re Wrangling Spreadsheets
1. Minimize any and all distractions
Spreadsheets require focus. Make sure your environment lends to that as best you can. You can hole up in a conference room, or try using headphones to drown out distractions.
2. Work on your spreadsheets before lunch
If you do your best work in the afternoon, bless you. But if your brain gets fried by 2pm too, don’t wait! Get this done while your brain is still at peak performance.
The other nice thing about doing this in the AM? You’ve got lots of buffer in the event of a spreadsheet catastrophe.
3. Before you do anything, 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. Then, duplicate your tabs 👏 as 👏 you 👏 go
In my best Alec Baldwin voice, these are the ABC’s of spreadsheets: Always Be Copying.
This can be the difference between 30 minutes and 3 hours of remedial work. Make a copy of your tab every few manipulations. Even if you mess up, you’re much less likely to have to start from scratch this way.
5. Save your file after each step
We’ve all experienced the horror of spending hours on a document, not saving along the way, and then having the computer crash. That’s why I save after every sort, cut and paste…especially if my sheet is massive.
Tip: If you only ever learn one keyboard shortcut, let it be to Save. That’s Ctrl+S for Windows and ⌘+S for Mac.
6. Use color & columns to ‘document’ your steps
Some of us are visual creatures. I like to use color and “notes” columns to distinguish my database rows. Just makes things a little easier to see.
So for example. a file that starts out like this ↓….
..will undoubtedly look like this ↓ once I really get to work.
Help yourself out. Take the extra few seconds to add color and notes to your data.
7. Anchor your formula references!
If you’re used to working with formulas, then you know references are finicky. If you make the wrong adjustment, all your calculations can change. That’s where anchors come in. Here’s a quick video on how to use them.
p.s. If you’ve never worked with formulas, you really ought to start! Don’t believe me? I promise these 6 formulas will save you tons of time in the long run.
8. When disaster strikes, try to restore a previous version of your file
Maybe you really fudged up this spreadsheet. And you already know that you’ll have to start over to make things right.
Before you do that though! Try to restore a previous version of your file first. Computers often auto-save as we go, even when we’re not aware of it. It’s possible that there’s a hidden version of your file pre-screwup that’s still recoverable.
And finally, treat yourself when it’s all over
I’m not saying that spreadsheets are quite like dementors. As someone who happens to love both.
Bu we all have those exhausting, soul-sucking spreadsheet days. So after you have yours, do make sure to get a sweet treat and recover. 🍩