Custom Number Formatting in Excel: Part 2

In a previous tip, we introduced the basic features and syntax of Custom Number Formatting. Marquee uses custom formatting to allow our models to be Dynamic, Flexible, Intuitive and Transparent: key elements of our modeling philosophy.

For today’s tip, we would like to expand on that topic by demonstrating how to combine letters and numbers in a cell. Consider a situation where we would like to show the P/E multiple for Companies A, B, and C in the table below. These values would then be used within calculations in different parts of a spreadsheet.

If we just typed “10.0x” into the cell for Company A, the value would be transparent and intuitive but not dynamic or flexible because it could not be used as an input for any calculations. Why? Because Excel would recognize the cell as text due to the “x” being directly in the cell.

Custom Number Formatting to the rescue! In the format dialogue box below (hit CTRL 1), select the Number tab, choose Custom, and then navigate to the section labeled “Type”. We can now define our Custom Number Format as 0.0x. Zeroes act as placeholders for each digit in the number to be displayed. Since we want our number to end with an “x”, which is a text character, we place the x after the zero placeholder. We don’t need quotes around the x which is often the case for single letters in other custom formats.

We are now able to enter the number 10 into the cell but it will be displayed as 10.0x. By using Custom Number Formatting, we have ensured that the multiple can be used in calculations throughout the spreadsheet.

If these were inputs for a working capital schedule, then we may want the inputs to read as “10.0 days”. We can again use Custom Number Formatting and enter 0.0 “days” in the custom formatting box. Note that double quotations are needed when there is more than one text character.

This is just one of the many ways you can use Custom Number Formatting in your Excel files.