It is important for numeric values to have labels so that the values are clear. If you simply add text in the cell with the number, the resulting value will be text and will not be usable in formulas. Therefore, it is ideal to use custom formatting to ensure your values are clear, and can be still used as numbers.
For example, with custom formatting you can have a cell that says '25 years'. The adjacent cell can add +1 to this cell and then display '26 years'.
Custom formatting is powerful because the cell is clear to the reader, but also because the value is still a number that can be used in calculations.
In this video Ian Schnoor, FMI’s Executive Director, explores how to customize number formatting in Excel, helping you present financial data clearly and efficiently.
These techniques will streamline your workflow and improve data presentation. Watch now for practical Excel formatting tips that enhance your financial models.
Transcript (Edited for Clarity)
Formatting in Financial Models
Let’s look at the second topic: formatting.
It is very important in a financial model that every cell be clear. Your reader must understand what each number represents.
For example, suppose this company plans to depreciate fixed assets over 25 years. If I type "25 years" into a cell, it looks fine, but technically it is not. I typed 25 plus the word "years," which makes the entry text rather than a number. The same issue happens with a debt covenant. If I type "<4.5x," it looks right, but Excel treats it as text. In both cases, the value cannot be used in calculations.
On my Assumptions page, depreciation is 25 years for old assets and 30 years for new ones. If I enter "25 years" directly, it is not numeric. If I only type "25," the meaning is unclear. Does it mean years, a percent, or something else? Either way, it is not clear enough.
The solution is a very powerful Excel feature called custom number formatting. With this approach you enter only the numeric value into the cell and then apply formatting so Excel displays both the number and the label.
Example 1: Debt covenant
-
Underlying value: 4.5
-
Apply a custom number format: "<0.0X"
-
The 0.0 placeholder means one decimal place. The cell contains 4.5 but displays as "<4.5X." It looks like text but it remains a number that can be used in formulas.
Example 2: Depreciation period
-
Underlying value: 25
-
Apply a custom number format: 0 "years"
-
Because "years" is more than one character, it is placed in quotation marks. The cell contains only 25 but displays as "25 years."
Advanced tip: four-part custom formats
Custom formats can handle four different cases separated by semicolons: positives, negatives, zeros, and text. For example:
+0.0; (0.0); "-"; "This cell should contain a value"
-
Positive numbers display with a plus sign, such as +45.0
-
Negatives display in parentheses
-
Zeros show as a dash
-
Any text entry displays the message "This cell should contain a value"
This ensures every outcome is clear to the reader while keeping the underlying data numeric and usable.
Why this matters
This is one of my favorite techniques because in any strong spreadsheet or financial model every number must be crystal clear. Custom number formats are one of the best ways to achieve that clarity.
