Custom Number Formats in Excel

Clear presentation of numerical and logical information in Excel is fundamental when you want to improve usability of your financial models. Custom number formats in Excel is critical to good presentation.

This Tutorial outlines different key techniques which can be quickly applied to any project finance model, and is recommended for Excel users at all levels.

Excel often formats numbers as it sees fit, for example, if the user types in 10%, Excel will automatically format this cell as a percentage and any future values entered into this cell will be formatted similarly. To custom format a cell click on the target cell then go to Format -> Cells (the shortcut is Ctrl+1). The Format Cells window should look like this:

How to Set Custom Number Formats in Excel

To set custom formats click on “Custom” in the Category.

The custom format is entered under Type. Excel allows for four (4) format sections:

0.00 ; (0.00) ; 0.00 ; “Text “@ 
(Positive) (Negative) (Zero) (Text)

Each section needs to be separated by a semi colon “;”. 0 represents where the numbers should appear, for example to display 2 d.p. “0.00” is used and for no d.p. “0” will suffice.

To use commas as separators “0,000” is required. Only the Positive section is mandatory by Excel. By omitting all of the other sections Excel will assume the positive format. If however the user wishes to customize the zero section, all sections prior, i.e. Positive and Negative sections must be specified or empty cells will be displayed for the value corresponding to the omitted section.

Examples of custom formats in Excel

Comma with 2 Decimal Places

Type is written as: 0,000.00; (0,000.00);

The format represents 2 d.p. for positive values with comma separators (“0,000.00”), 2 d.p. enclosed in brackets for negative numbers with comma separators (“(0.00)”) and zero displayed as a dash (“-”)

Comma with No Decimal Places

Type is written as: 0,000; (0,000); -

Here the format is quite similar to 2 d.p. but with “.00” removed to represent zero d.p.

Combining Text and Numbers in a custom format 

Type is written as: For Op Qtr 1 – “Op Qtr” 0, and for 5.25 yrs – “0.00” yrs

To combine text and number the text portion must be enclosed by double apostrophes. Negative, Zero and Text sections are omitted.

Ratios

Type is written as: 0.00“x”

This is another example of combining text and number however in this case there is no space between 0.00 and “x” as x should follow immediately after the number.

Percentages with 0% Displayed as “-”

Type is written as: 0%; -0%; -

Since the zero section is customised, both Positive and Negative sections must be specified as well.

Displaying 1 and 0 as “Yes” and “No”

Type is written as: “Yes”; ; “No”

Because 1 is a positive number only the Positive and Zero sections need be defined. Any positive number will return “Yes” but if a negative value is entered the cell will remain empty as the Negative section is undefined.

To demonstrate the custom number formats in the above examples, we have put in a sample workbook. To view the number formats in certain cell, click on the cell then go to Format -> Cells -> Number -> Custom.

The format cells in the above examples are as illustrated in the screenshot below.

Advanced Custom Number Formatting

If you want to explore more advanced features of the custom formatting in Excel, look into the following features 

• Use the Wing Dings font to interpret symbols – “vw” = <> 
• Use Custom formatting to add another dimension of conditional formatting 
• Customise your Styles for the key formats – e.g. ‘Ratios’

Want more Tutorials?

See All Resources Get the latest Project Finance Tutorials and Blog Posts...