Financial Model Formatting: Advanced Numbers​

Custom number formatting

Why is formatting important?

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 on the end user. A sloppy model may obscure good financial analysis if it passes of a bad impression to the client, manager or executive using the model.

A good financial model should be consistent, efficient and clear. One method of achieving this is through formatting the color of text within cells. Another method is to maintain a consistent formatting with regards to decimals in numbered data. The latter can be expanded to take advantage of Excel’s custom formatting capabilities.

Number Formatting Basics

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 # signify 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.

Take our financial modelling course to brush up on formatting skills.

More formatting

There are other areas of formatting that a financial analyst can use to improve a model. Peruse the articles below to learn more about other aspects of financial modelling within Excel.