LibreOffice Calc Custom Currency and Scientific Formats

This guide provides an overview of how to create and apply custom numeric formatting strings for both scientific notation and currencies in LibreOffice Calc. Custom formats allow you to control decimal precision, exponent signs, thousands grouping, currency symbols, and negative value indicators without changing the actual underlying cell values.

How to Apply Custom Format Codes

To apply a custom format code in LibreOffice Calc: 1. Select the cells you wish to format. 2. Open the Format Cells dialog by pressing Ctrl + 1 (or right-click and select Format Cells). 3. Navigate to the Numbers tab. 4. Under Category, select User-defined. 5. Type your custom string into the Format code field and click OK.

Custom Formatting for Scientific Notation

Scientific notation strings in LibreOffice Calc use E or e combined with + or - to separate the mantissa from the exponent.

Key Syntax Elements

• 0: Forces the display of a digit (displays a zero if no number exists at that position).
• #: Displays a digit only if it is significant; suppresses leading and trailing zeros.
• E+ or e+: Displays the exponent sign for both positive and negative powers (e.g., E+04, E-02).
• E- or e-: Displays the exponent sign only for negative powers (e.g., E04, E-02).

Common Scientific Format Codes

• Standard Scientific Notation (Fixed Decimals):
  0.00E+00
  Formats 12345 as 1.23E+04 and 0.005 as 5.00E-03.

• Engineering Notation (Multiples of 3):
  ##0.00E+00
  Formats 123456 as 123.46E+03 by adjusting the mantissa to multiples of thousands.

• Minimal Exponent Digits:
  0.0E+0
  Formats 1200 as 1.2E+3 without forcing a leading zero in the exponent.

• Scientific with Color Coding:
  [BLUE]0.00E+00;[RED]-0.00E+00;0.00E+00
  Displays positive values in blue and negative values in red.

Custom Formatting for Currencies

Currency formatting controls the placement of symbols, negative number representations, thousands separators, and decimal accuracy.

A standard format string consists of up to four sections separated by semicolons:
Positive Format;Negative Format;Zero Format;Text Format

Key Syntax Elements

• $ / € / £: Inserts standard currency characters directly.
• [$symbol-language_code]: Inserts a locale-specific currency symbol (e.g., [$€-407] for German Euro).
• , (Comma): Places thousands grouping separators.
• . (Period): Marks the decimal point (adjusted automatically based on your system locale).
• "Text": Displays static text within the number format.

Common Currency Format Codes

• Standard Currency with Two Decimals:
  $#,##0.00;-$#,##0.00;$0.00
  Formats 1500.5 as $1,500.50 and -25 as -$25.00.

• Accounting Format (Parentheses for Negatives):
  $#,##0.00;($#,##0.00);"$0.00"
  Formats -1250 as ($1,250.00) and 0 as $0.00.

• Color-Coded Currency:
  $#,##0.00;[RED]-$#,##0.00;$0.00
  Renders negative balances in red text.

• Custom Suffix Currency Code:
  #,##0.00 "USD";-#,##0.00 "USD";0.00 "USD"
  Formats 250 as 250.00 USD.

• Millions/Thousands Abbreviation:
  $#,##0.00,," M"
  Divides the displayed value by one million and appends “M” (formats 2500000 as $2.50 M).