So we have all been in this situation where we need to present some data to an important stakeholder. Whether that be our boss, our client or an investor.
We pore over the analysis and the numbers making sure everything is perfect. Then we present our spreadsheet to our stakeholder.
We notice they are straining their eyes trying to figure out what’s going on.
Then they ask a simple question you thought you had clearly answered “so, what was the total profit for last year?”
Your heart sinks as you answer them and point out where on the spreadsheet this number can be found.
Why did this happen?
Simple, it’s because of poor spreadsheet formatting. Or in other words your spreadsheet looks ugly and is hard to read.
When we present numbers we often think (especially if we are analytically inclined) that the numbers will speak for themselves.
But we often forget that our audience might not be so familiar or discerning. That’s why it’s important to format your spreadsheets properly and make them look pretty.
How can we make our spreadsheets more pretty?
By borrowing principles from UI design. A rough outline of the process is described below.
- We need to find out who we are presenting to and what they most care about
We are going to be presenting our department’s cost, revenue and profit/loss for the past 4 quarters to our CFO and a Data Analyst that works for him.
Our CFO is most concerned with the Profit/Loss for each quarter.
The Data Analyst needs to know the line items that make up the Revenue and Cost. They also need to know when these items occurred.
2. Start with a First Draft of all the numbers
Like with any other design process, we are first going to do a rough draft or version 1.
In this instance, we are not concerned with making things too pretty BUT we are concerned with making sure the Data Shape is good for our analysis.
What do we mean by Data Shape?
Without going too deep, Data Shape refers to how detailed/summarized the data is presented. Along with how it is arranged in rows and columns.
Since we need to break down the revenue and cost by quarter. We will make 1 sheet for each quarter.
Then we will make 1 column for each of the 12 weeks, 1 column to put our items and 1 to put our item type.
The money amount will then go under the appropriate week. It will look something like this:
This will make sure our Data Shape reflects both the totals that our CFO cares about and also the line item details and dates that our analyst cares about.
3. Calculate the important metrics
Now we need to calculate the sum of the cost and revenue for each quarter and the total profit.
4. Make it Pretty
Now we are on our final step. To make things pretty.
A lot of these steps can be taken no matter how the table or data looks. However, the above step (figuring out the Data shape) will be unique to each data or table you encounter.
- Bold All Important Headers
We need to bold all column headers and other headings that denote the data
- Color Code Data to Show Major Differences
We will color code the costs with a red font and the revenue streams with a green font
- Make Borders to Group Data More Clearly
This is an important step. We are going to be using the border tool to create borders that will group the data
Highlight different sections and click on the border tool to create groupings like below.
This shows our cost and revenue very clearly. Along with the Total Cost and Total Profit.
- Put emphasis on our key metrics
Since we really need to show the totals to our CFO, we are going to increase their size and give it a gray background.
This way they can be easily seen.
- Make aesthetic changes
In our current state, the numbers look good and our key metrics can easily be seen.
The final changes we are going to make are purely aesthetic. They will make the spreadsheet look very professional.
First, we will move the data 5 lines and 2 columns over. This will give us room to add our company logo and the title of this report.
Secondly, we will make all the space not occupying any data or part of any table a white background. This will really draw people’s eyes towards the data itself.
Finally, we will give the differently grouped column headers different background colors to really emphasize the different groupings.
As you can see we added the Quarter 1 Profit and Loss Statement header along with our logo.
Then we gave different colors to the headers based on their groupings.
Finally, we made all non essential cells hav white background.
Giving thought to and working on formatting your spreadsheet data is just as important as the numbers themselves.
Without clear design your audience will be confused as to what they are seeing.
We would love for you to take this as an exercise and let us know how you might do things different with the example above!
Also, please share any tips and tricks you have found to make spreadsheets pretty!