A former employer hired me to set energy prices and create stupidly wicked compensation metrics for its sales team.
The job paid well, but guess what 75% of my days involved?
Being the office’s spreadsheet alchemist.
My employer could have hired on someone at half my salary to handle the tasking that accompanied said status, but it didn’t.
A typical day would begin with the Operations Manager asking me to dynamically link up a slew of spreadsheets that tracked his team’s “saves” (customers who called to cancel service only to be convinced otherwise). There was no template – it was a very manual process.
Later, the Sales Manager would come over to my cube in a frenzy needing to know RIGHT NOW whether I could make sense of some random calculations that were provided to him in a spreadsheet that contained no documentation on how to use it.
Then, as I’m refilling my coffee, a sales team supervisor would corner me and ask if I could automate her spreadsheet that she shares with the spreadsheet-illiterate VP every morning – a spredsheet that required two-hour manual updates. EVERY. FREAKIN. DAY.
What does all this have to do with you, dear reader?
That job taught me the importance of structuring spreadsheets well.
It taught me how to structure my spreadsheets so that they would effectively and efficiently accomplish their intended tasks.
It also taught me how to create spreadsheets with enough foresight to keep them relevant over time.
My friends, when you are working with spreadsheets, everything boils down to how your tables are structured. And I mean everything.
Well-structured tables will save you boatloads of time.Well-structured spreadsheet tables will save you boatloads of time.Click To Tweet
Well-structured tables don’t require advanced spreadsheet proficiency to create but will elevate you into goddess territory if you create them because so many people don’t.
Quick note: If you haven’t already downloaded From Spreadsheet Virgin to Goddess in 30 Minutes, it would behoove you to do so because I cover this and related topics there.
Here’s how to structure your spreadsheets so that they don’t drain your most valuable asset – your time:
Step 1: Put a bit of thought into the how and what and do you questions:
- What is your objective?
- What do you need the data to tell you?
- How should you organize your data to manipulate them effectively?
- Do you have the expertise to accomplish your goals efficiently?
Seriously, I can’t overstate how critical these questions are. I ask myself them every single time I design a spreadsheet.
Step 2: Spend a few minutes creating a mockup.
I don’t care if you sketch it out on a napkin while the Starbucks barista pulls your espresso, or if spend a bazillion hours laying it out dashboard-style in Excel or even Photoshop (just don’t waste your time at this stage picking out colors, border styles, etc.).
The point is to have a rough idea of what you want the finished product to look like.
Step 3: Create your basic tables – without formatting.
Nothing sucks more than having to constantly undo and recreate borders during the creation phase. Just leave them for the end.
Note: if you use Excel’s “insert table” option, you will be presented with a preformatted table. There are advantages and disadvantages to using them and must be evaluated on a case-by-case basis.
Anyway, here are the types of tables you will most likely create:
- Tables that are intended for raw data entry
- Tables that display information for the end user to digest
- Tables that function as points of reference for other tables
- Tables that function as calculators
Often, your table may contain elements of multiple table types. Some of mine contain all four!
Now, here’s where people screw up.
They create tables that are intended to not only display information, but also to make calculations and serve as the raw data entry point. Because of the former, they make them pretty.
They add spacer columns and rows.
They may even merge header rows with similar groupings.
And, they just multiplied their workload by a thousand.
That former colleague I told you about did just that. She had a pretty spreadsheet that was supposed to be easy on the spreadsheet-illiterate VP’s eyes. So, she added a bazillion spacers and organized the output by group (in her case, it was by sales territory). Not that either of these things are terrible things to do – I do them sometimes – but by doing so, she had to spend two freakin’ hours every freakin’ morning adding new spacers, adjusting existing formulas to account for the new blank spacer rows, and linking to other reference tables. Because she was manually adjusting everything and because the VP was her end user, she had to cross-check everything to make sure that she didn’t screw things up.
Instead, she could have pulled the spacers, created new variables for each of the header groupings, and added a pivot table. If the VP didn’t like the look of the pivot table output, no worries. She could update the same original pretty table with the information that the pivot table spit out. So, I spent a few hours overhauling her spreadsheets.
Once I redesigned her spreadsheets, she was able to accomplish the same daily two-hour updates in less than 15 minutes.
Do the math. Every week, she now saves almost nine hours of time. That’s 450 hours a year, assuming that she gets two weeks of PTO.
This single post can be turned into an entire course topic – it’s way too much information to include in a single blog post. But the takeaway is that most tables are best built with efficiency in mind.
It may be more efficient to create two or more tables as I did for my colleague than to simply create one master table.
Step 4: Populate your tables entirely with sample data.
This step is critically important if you will share this spreadsheet with others or rely on its calculations to make important decisions.
For instance, every time you cut and paste something, you will remove the cell’s formatting and any formula that it may contain. If you don’t correct this at the moment it occurs (and you probably won’t), your table – at best – will use the spreadsheet program’s default formatting and fonts.
Also, if you use complex formulas like I do, they may not always carry down the way you want them to. Or, maybe they won’t reference the cell you need them to reference, which will only become evident at row 132 and on.
So, run sample data through your spreadsheets before sharing them with anyone.
Step 5: Format your spreadsheets.
Okay, I fully admit to being guilty of formatting my spreadsheets too early in the process because I dig pretty spreadsheets. But, I pay for it with tons of time fixing screwed up borders, font changes, etc. Just sayin’.
The takeaway of this post is to put some thought into creating well-structured tables that emphasize efficiency. Doing so will save you time, mental energy, and propel you into spreadsheet goddess territory.
Now, I’d love to hear from you. Feel free to share your thoughts or ask questions in the comments section below.
And, if you have spreadsheets that could use an overhaul (and maybe a little love), drop me a line. I have been known to take on the occasioal freelance project. Thanks! Until next time…