Strategy 101: How to Format a Spreadsheet
A 1 minute process to make your spreadsheets clean & comprehensible
In the “Strategy 101” series, I’ll share simple trainings on Strategy basics. This includes simple analyses, foundational skills, and more!
Even in 2024, spreadsheets are the foundation of most strategy analysis.
No matter how good the rest of your analytics toolkit is, you’re still going to find yourself using spreadsheets. And for good reason! They’re an incredibly powerful, adaptable tool that almost anyone can pick up.
Yet I often see spreadsheet pros make a simple mistake: poor formatting.
In this post, I’ll go over a simple way to format your spreadsheets that will help you stay organized, keep your work clean, and look like an Excel Wizard. This is trimmed-down version of the approach I learned at Bain, and it’s still the first thing I do when I open a new sheet.
Let’s dive in!
What is formatting, and why does it matter?
Formatting is everything outside of the analysis in a spreadsheet. This includes the title, the tab title, margins, the color of the cells, gridlines, the numbers, and more.
On its face, formatting doesn’t seem important. All style, no substance - right?
Well, not really. In spreadsheets, formatting is key to comprehensibility. Like docs, you want spreadsheets to be able to stand alone - someone else should be able to pick them up without explanation. Formatting makes that possible.
To demonstrate this, I’ll use this sheet as an example (yes, it’s a Google Sheet since they’re much easier to share - I’m not an Excel purist).
This is our starting point. We have a four column dataset, but it’s not labeled. At first glance, it looks like transactions - but is it purchases, or reimbursements, or an expense report? Who’s the vendor?
Let’s get to formatting!
Step 1: Add the title & date (tab and top of sheet)
First, you need to add a title to your sheet. It’s best to do this in two places: the tab name, and at the top of the sheet.
By default, I add 5 rows at the top of the sheet for the title and date label. I also add one column on the left (more on this later). Title goes in cell B2, date in cell B3. Tab should have the same name as the title.
This immediately gives you a sense of what the data in the sheet is, and when it was created / produced.
Where possible, more descriptive titles are better (“Customer Transactions” is still a bit vague) but the high-level title doesn’t have to be perfect - just enough to go off of.
Step 2: Add top border
This is just for flare, but a double line border under the header makes your spreadsheet look very official. It also adds clear visual separation between the header & the rest of the sheet.
Step 3: Adjust column A & add table-level title
Column A is just there to add margins to the sheet, so it should be small (you don’t want it taking up a bunch of the page). Shrink it down so it sits nicely on the edge of the page.
Then, you’ll want to add a table-level title. Each sheet is likely to have multiple tables or areas of analysis, and each should be labeled (in addition to the title). In this case, since the data is just a raw dataset, I label it as such + the date it was exported.
For ease of navigation, I also like to add an “x” in the left column. This means as you add tables, you can navigate between them on the left hand side by holding the CTRL / CMD key, and hitting the up or down arrows.
Step 4: Format the table itself
Once you have this set up, you need to format the data in the table itself. There are a few basic things that massively improve readability:
Bold the headers
Add borders between the identifiers for the data (e.g., name, date, company name, etc.) and the data itself
Apply standardized date / number formatting across the table
Adjust column width so that all entries are readable in full
Now you’ve got a clean, readable dataset!
Step 5: Quality of life formatting (optional)
Finally, you can make a few quality of life changes. You don’t have to do any of this - all the practical formatting is already done!
A few things I like to do:
Increase size of title font
Align title date left (just looks nice)
Remove gridlines (makes everything look way cleaner, though I know this is controversial)
Grey out the “x” on the left hand side (also just looks nice)
Make sure all fonts are standard (Arial is my preference)
That’s it! You now have a well formatted spreadsheet that’s much easier to read & has a lot more context than the start.
Closing thoughts
The total time of all these videos was 100 seconds. If you did this without recording it, it would take about 60 seconds - pretty quick!
This effort makes a world of difference. Adding just a bit of formatting makes spreadsheets way easier to interpret and adds credibility to the work itself.
There are more advanced tips on formatting that I won’t cover here (color coding cells for model building, etc.) but this short version is really all you need to get started.
Of course, different people have different preferences when they’re creating sheets, but this is a super light-weight version of formatting that’s very easy to pick up. If you have strong opinions or think I missed something, let me know!
Why such a boring topic? I’m going to be doing a few other excel-based tutorials in this series (running segmentation analyses, creating a forecasting model, etc.) and I figured it would be useful to cover formatting first.
Thanks Skye - found this to be useful! If possible, would love to see you cover driver-based modeling / scenario modeling in the future and how you incorporate reasonable assumptions into them.