Custom formats in Excel

Using custom formats in Excel can significantly improve the presentation of your spreadsheets and financial models. In this tutorial we give you an overview of the main functionality and also look closer at some more advanced examples with conditional formatting.

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.

There are some built in cell format in Excel which are accessible via Format -> Cells (The shortcut is Ctrl+1). In case none of the build in formats you’re your needs then go to Format -> Cells and click “Custom” in the Category.

Custom number formats in Excel

The custom number format is entered under Type. Excel allows for four format sections: 

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

Each section need 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.

Basic custom Excel formats

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)’ 
• 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

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” respectively

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

Because 1 is the only positive number 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.

Custom formatting of dates

Various types of dates could be written such as: 

• dd-mm-yyyy : 01-01-2009 
• ddd, d-mmm-yy: Thu, 1-Jan-09
• dddd, d/mm/yy: Thursday, 1/01/09

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.

Advanced custom Excel format example 1

For example, there are ten Portfolios (Portfolio A to J) and there are eight types of assets (Asset 1 to 8) that could be included in each Portfolio. Each Portfolio could be activated during Phase 1, Phase 2 or even Not Activated.

Displaying 1,-1, 0 as “Phase 1”,”Phase 2” and “Not Active” respectively

Type is written as: "Phase 1";"Phase 2";"Not Active” in format cells. We also need to enter data validation source as 1,-1, 0 (Data „³ Validation „³ Source)

The format represents: 

• Number “1” will return “Phase 1” 
• Number “-1” will return “Phase 2” and 
• Zero “0” will return “Not Active

Displaying 1 and 0 as “Include” and “Exclude” respectively, with conditional formatting.

Type is written as: "Include";;"Exclude". We also need to enter data validation source as 1, 0. To contrast the “Include” and “Exclude” cells we could conditional format the cell that equals to “0” by formatting it in lighter colour such as grey (Go to Format „³ Conditional Format)

Screenshot: Conditional Formatting

The screenshot for this example is shown in the next page. Please also note from the workbook that by custom formatting certain cells, the presentation is improved, and we could avoid the long Multiple IF formulas which are often used.

Screenshot: Advanced Example 1

Advanced example 2

How to use WingDings in Custom Formats to improve presentation?

Screenshot: Example of Scenario presentation

It is pretty simple! Format ”s” and “q” as “Wingdings 3” and they will appear as characters that could be applied for scenario presentation. “Red” conditional formatting could also be used as shown in this example.

COMMENTS