28 October 2012

Australian Accounting Number Formats

Number format codes for Excel and Libre Calc used in Australian accounting (Excel does not have this number format code available as a default while Libre Calc has the second one).

Number format code123456.78-123456.780.00
#,##0_);(#,##0);-_)123,457 (123,457)
#,##0.00_);(#,##0.00);-_)123,456.78 (123,456.78)

Notes:

  • Semicolons delimit the sign of the number being formatted: positive;negative;zero.
  • The underscore before the right parenthesis: append a trailing space the size of the parenthesis so that positive numbers are right-aligned with negative numbers (which are enclosed in parentheses).
  • Using the first format code, your numbers are formatted without cents.

The underlying value in the cell hasn't changed, just the presentation. Use sparingly; you can confuse a user who tries to use what they see in the cell in their formula and wonder why their formula doesn't work.