Formatting a financial model becomes important for two reasons. The first reason is that proper formatting helps keep the financial model clear and easy to understand. The second reason is that a financial model is often a reflection of the creator for the end-user. A sloppy model may obscure good financial analysis if it passes off a bad impression on the client, manager, or executive using the model.
Excel has different types of data that it can set its cells to contain. For example, a data cell can contain a number, but this number can be formatted as a date, as a currency, or as an integer through Excel’s different formats.
To access Excel’s number tab, press Ctrl + 1. This allows the user to select certain preset formats and subformats. For example, selecting the “Number” format will set the data cell to appear as a number. The subformats for the Number format allow the user to select how many decimals appear by default, and how negative numbers are set to be presented.
Custom Number Formatting
At the bottom of the Format Tab (Ctrl + 1), a user can set a custom format. This custom formatting is a very robust tool that can be implemented to display numbers as desired. Upon selecting the custom tab, one may notice options with lots of #s or 0’s. This is Excel’s coding for how to display a custom data cell.
The # signifies the call to only display significant numbers if they are present. The 0, on the other hand, will display insignificant numbers, including zero decimals. Insignificant numbers are any decimal beyond the last non-zero decimal in a number, or any zero before the first non-zero integer in a number. For example, in the number 005.5307000, the first two zeros before 5 and the last three zeros after 7 are insignificant, because the number will still be the same when they are removed (5.5307).
Excel can also insert text into these number formats. Doing so will allow the data cell to still be read as a number (and thus work in formulas), as opposed to identifying itself as text. This can be done by simply inserting the desired text into the custom code, surrounded by quotation marks.