Although there are infinite ways to format numbers, in my humble opinion, there is a superior number formatting standard. As someone who worked in finance and received spreadsheets from countless clients and counter-parties, I always reformatted every file before I began any analysis to make sure that it was easy to analyze the numbers and not get distracted by the formatting.
After viewing thousands of spreadsheets filled with numbers, I determined that the following formats are the best to use in the vast majority of cases. Sometimes I wish Excel and Google Sheets defaulted to and only allowed these discrete styles.
The most common number types in my experience have been Numbers, Dollars, Dates, Percentages, and Multiples, so these are the formats that I will be speaking about.
My Preferred Number Formats
Numbers: #,###; (#,###)
Dollars: $#,###; ($#,###)
Date: MM/DD/YY
Percentages: #.#%
Multiples: #.#x
Generally, any number or dollar value with more than three digits should include a comma. For percentages and multiples, the % or X character should come directly after the final digit.
Luckily, any good spreadsheet software program should have shortcuts for these formatting styles already built in.
Excel Shortcuts
Numbers: CTRL + SHIFT + !
Dollars: CTRL + SHIFT + $
Date: CTRL + SHIFT + #
Percentages: CTRL + SHIFT + %
Multiples: Make a custom format because there is no preexisting formatting shortcut in Excel for multiples. In my opinion it should be CTRL + SHIFT + *).
There are also time format and scientific (1.45E+03) shortcuts, which are CTRL + SHIFT + @ and CTRL + SHIFT + ^ respectively, but those are less used in finance.
With these formats as the standard, the only thing that should be changed is the decimal places. So if you wanted dollars with cents, you could increase the digits after the decimal place to look like this $#,###.##. Whether you show decimals is a judgement call, for example if you were showing data in billions, then 1.4 billion is very different from 1 billion, and it may make sense to show at least one decimal point. The only rule here is that if you decide to have some numbers in a dataset have decimals, you need to show the same number of decimals for all of the numbers.
You want to be able to compare every number by digit (tens, hundreds, thousands, etc.) by scanning vertically up and down. What you don’t want is to have to adjust your eyes left or right to make sure you’re comparing the right numbers.
There is also a shortcut to change the decimal places: ALT—>H—>9 to decrease a decimal and ALT —> H—>0 to increase a decimal.
Another tip is that although something may technically be a dollar format, you can keep it in the number format if there is a subtotal/total that you can show in the dollar format. This makes the spreadsheet less crowded and cleaner and implies that everything above is the same currency. See example below.
With all your numbers formatted properly and consistently, your analysis just became infinitely more legible!
