Excel Custom Finance Formatting

The aesthetics of a spreadsheet can be improved further through the use of custom formatting of the cells.  Custom formatting allows unlimited possibilities to enhance a spreadsheet and to personalise your sheet.

finance formatting - 1

The custom formatting option is found at the bottom of the Format Cells options.

Selecting a pre written option puts the puts the selection into the “Type” box which then allows for further customisation.

In the above example the operator is broken down as : –

#,##0.00;[Red]-#,##0.00

The # sign specifies the number of digits to display so in the above example the format is 0.00 meaning that the number will be rounded to two decimal places.

For accounting and finance the favourite custom format is

#,##0.00;(#,##0.00);-

Positive numbers – Formatted to 2 decimal points

Negative numbers – Formatted to 2 decimal points within brackets

Zero – If the value is zero then “ –“ is returned

 

#,##0.00;(#,##0.00);-

This produces all numbers to two decimal places, negative numbers are bracketed and a zero is denoted by a “ – “.

finance formatting - 3

To apply the above custom format to the cells in your sheet: –

finance formatting - 4

Select the cell and right click to reveal the above pop up menu.

finance formatting - 5

Select the “Custom” Category and input the above formula into the Type box.