Motivations
Today I want to share 5 golden rules of Excel formatting with you, which I have gathered through years of working in the financial industry, and observed how high-calibre professionals impress their teams and managers with attention to these otherwise small details.
At this point you may ask:
“Formatting seems so unimportant in data processing, why do we need to spend time on it?”
I would agree with you 6 years back, but having seen how successful individuals take formatting seriously when presenting their work, and actually getting recognition from it, I changed my mind: Formatting is as important as the result you are going to present. You want people to listen to you, and you want to sound convincing; that’s where professionally formatting your spreadsheets brings value to your work.
Here are 5 golden tips I have on professional Excel formatting:
Golden Formatting Tip 1: Content Page
Create a content page for your large spreadsheet
A content page is a must-have for any Excel workbooks contains more than 5 pages.
After all, navigation between different tabs is inevitable. If you don’t set up a content page, you may end up wasting your time navigating around without ever landing on the tab you want.
The content page is exceptionally important when it comes to presentation. If you are not a content page lover, I bet you have encountered situations like this.
“OMG, where is the summary tab. OK, I have to pretend that I am calm and I can’t let my boss know I am lost in my own Excel doc. But where the heck is the tab? Help~ ”
We recommend you to check out our content page add-in product, which does this automatically for you with ONE click 🙂 And here is a promo code to get you 50% off on this product:
TH4Z89GK
Golden Formatting Tip 2: Coloring Tabs
Color your tabs & create a "Main" tab to display your results.
Most likely, not every tab in an Excel workbook is as important. Coloring your tab helps you to locate the most important tab right away.
Another use of tab colors is to distinguish between the use of different tabs.
In a proper Excel workbook, you will have raw data tabs, mappings tabs and result tabs.
I like to color raw data tabs in blue, intermediate/mappings tabs in red, and result tabs in yellow, but feel free to stick to your own color codes.
Golden Formatting Tip 3: Coloring Cells
Color your inputs and formula cell differently in your intermediate tabs.
People will appreciate it when they can quickly see what are the inputs and what is the formula used to derive your results.
Golden Formatting Tip 4: Formatting Numbers
Always use #,### to format your numbers, such as 10,342 and (10,342) for negative numbers.
When I was a newbie in Excel, I got asked to format my number with thousands separator a lot. By that time, I wasn’t aware of how important this little comma is.
It was not until I get to pick up others’ Excel workbooks then I realize thousands separator can make a big difference when you only get to look at the data at a glance.
Don’t ask your audience to guess whether it is in thousands, millions or billions.
Golden Formatting Tip 5: PivotTable
Use a PivotTable if possible.
I couldn’t stress how important it is to use PivotTable.
PivotTable is like the most powerful feature in Excel. It allows you to change the presentation of the data totally with a few drag and drop.
You will regret not using PivotTable earlier.
People are very accustomed to seeing subtotals/totals in data presentation, and we check out our other posts on how to efficiently use PivotTables!
Bottomline
There are also some minor Excel formatting practices that people adopt, but we always stick to this principle: Make it visually appealing and don’t ask your audience to guess.
Can you think of more formatting rules? Feel free to send through your ideas here!
