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

Let’s look at the second one. I want to talk a little bit about formatting. It is so important in a financial model that every single cell be clear. It’s so important that your reader understand what every single cell means. So let’s take a look at this. Let’s pretend that this company was going to depreciate their fixed assets over 25 years. Watch what I’m doing here. If I type in “25 years,” I went to a cell as an input and I typed in 25 years—but what I did is I typed in 25 and then the word “years,” so now I have a number in the cell and some letters. So it’s actually a piece of text. What if this company had a debt covenant when they had to keep a debt ratio under 4.5 times? And I typed in “<4.5x.” These numbers are clear—you know what they mean—but they’re both just text. This is just text, not a value, not usable. So it’s not very helpful.

Let’s go to the model, on my Assumptions page. If I go down on my Assumptions page, I have a value here that says the depreciation is 25 years for the old assets and 30 years for the new ones—but I want to use that as a number. Well, the way I did it, if I simply type in “25 years,” it’s no longer usable; it’s just a piece of text. The only thing I really want to type in is 25 for the years and 4.5 for the covenant—those are values, but not clear. Now I have actual values in the cell, but you don’t know what they mean. You don’t know if this is 25 years or 25%; it’s not clear—neither of these are clear.

So the answer is to use a very, very powerful technique in Excel called custom number formatting. What I’m going to do is remove the “years” and the “x” from the cell entries and keep just the numbers 25 and 4.5, but display “years” or “< x” via formatting. Let’s start with the debt covenant. I press Control + 1 to open the Format Cells dialog, go to the Number tab, select Custom, and build my own format. In the Type box I enter:

  0.0 "X"

preceded by a less-than sign, so it reads:

  "<"0.0"X"

The zeroes are placeholders (0.0 means one decimal place). When I press Enter, the cell still contains the number 4.5, but it displays as “<4.5X.” It looks exactly like the text I typed before, but now the letters are only formatting—the underlying value remains numeric and usable in formulas.

Next, for depreciation I go back into Custom formatting and enter:

  0 "years"

Because “years” is more than one character, I surround it with quotation marks. Now the cell contains only the number 25, but displays as “25 years.”

That’s a very powerful technique—you type only the numeric value, but your reader sees the full label. One more tip: custom formats can have up to four parts, separated by semicolons, to handle positive values, negative values, zero, and text. For example, I might want positive numbers to show “+ 45.0,” negatives in parentheses, zeros as a dash, and any text to show an error message. To do that, I build a custom format like:

  +0.0; (0.0); "-"; "This cell should contain a value"

Here, the first section (+0.0) applies to positives, the second (0.0 in parentheses) to negatives, the third (“-”) to zeros, and the fourth (the quoted message) to text entries. When I press Enter, any positive number displays with a leading plus, any negative with parentheses, zero as “–,” and any accidental text entry shows my custom message. It keeps every cell clear to the reader while preserving the underlying values.

I’m such a huge fan of this because in any good spreadsheet or model, every single number must be crystal clear—and custom number formats are the best way to achieve that.