Mastering Pro-Level Excel Formatting Secrets for Data Analysts
Why are my colleagues and managers coming to completely different conclusions with my data?
Why are people not able to follow my work?
I was trying to figure out why my spreadsheets, that I had worked so hard to create, were being completely misinterpreted and, in most cases, just confusing everyone. Something was wrong — my work was being completely dismissed and I had no idea why.
I had to figure out what was going on and fast.
To have being understand and trust my work, I needed to have three double checks in place:
- I needed to focus on clarity with cell formatting
- I needed the data to tell a story
- I needed to switch perspectives
If this sounds like your situation, helpfully I can save you some time and walk you through how I became a clearer storyteller with Excel.
Check 1: I needed to focus on clarity with cell formatting
After multiple email responses saying roughly the same thing: “What am I supposed to be looking at?” “What does this mean?”, I knew I needed to figure something out.
Here are the essential formatting options that will provide clarity in your analyses:
Number Formatting
- Number formatting makes people quickly understand the data you’re presenting, especially when values get past millions. Most companies like numbers formatted with two options — either Currency or Accounting
- Below is an example of an unformatted analysis vs. a formatted analysis:
Unformatted:
- Currency Formatting:
- Accounting Formatting:
Alignment
- Alignment gives structure to the data. There are a few different stylistic preferences for alignment, but, from my experience, aligning data and headers in the center vertically and either to the left or center horizontally are the standard approaches. See the examples below:
- Centered Vertically — Left Aligned Horizontally
- Centered Vertically — Centered Horizontally
Header Formatting
- In my opinion, header formatting is one of the most important parts of an analysis. Adding coloring to the headers draws attention to certain columns, like profitability, and tells the story without much of an explanation. Here are some example of how I would recommend formatting headers:
- Bare Minimum — Bolding
- Highlighting Key Fields
- Consistent Coloring on All Analyses
- This is my recommended approach — if you use the same coloring for consistent fields (Sales, Cost, Profit Margin), coworkers and managers will begin to learn your style and can quickly understand the numbers. Their eyes will jump to the relevant parts of the analysis.
Check 2: I needed the data to tell a story
Even after fixing formatting on my files, people were misinterpreting my findings in the data. I needed to figure out how to provide more clarity.
The key to a great analysis is having the spreadsheet tell the story. All analyses are telling a story so how do you make sure that others are reading the story that you intended to provide? My answer to this is correct column order. In my opinion, column order is everything — column order gives relevant information, but not too much, it answers any outstanding questions that the reader has, and highlights the punch line of why the reader is looking at your analysis in the first place. Let’s look at a few examples:
- Unstructured, Unclear Story
- This structure doesn’t provide a story whatsoever. The starting cost column immediately raises questions for the reader — “Cost of what?” “What is this for?” “Is this an overall plant/business analysis or is this for a specific customer?” “What is the revenue?”.
- Clear Story
- This structure tells a clear story. We lead with the customer to establish the initial context of who we’re even talking about. Next, we give a plant column to give managers an idea of what internal plant this data might affect. We then jump to sales, to give an idea of the scale of the customer were dealing, next to cost, and finally to the highlight of the analysis — profit margin, the most important piece, which is why this is highlighted. Highlighting profit margin also lets managers, that are in a rush, quickly scan to the most important part of the analysis, if needed.
Column order and flow is key to telling a story with a spreadsheet.
Check 3: I needed to switch perspectives
My formatting and story telling were getting a lot better, but I was still getting more questions than I thought I should have. What was the missing piece?
Perspective
I needed to think of things from the readers perspective before I submitted the analysis. After ensuring I had proper formatting and that my columns and spreadsheet structure told a story, I needed to step back and look at the analysis as if I were the reader. I needed to forget what I, the data analyst, knew about the data and read the analysis as if I were someone that had just looked it the analysis fresh. What were the holes in the analysis? What didn’t make sense? What conclusions immediately stood out to me — were these conclusions what I had intended the reader to conclude? This is an essential step in the process and, in my opinion, is the most important. Switching perspectives unlocks a whole new level and will allow you to get recognized quickly and provide more value.
The above 3 checks were defining moments in my career — apply these to your own pursuit and you’ll be in a much better position to succeed.
