NumberFormat function

Syntax

NumberFormat(number, pattern, [options])

Parameters

• number: The numeric value to be formatted. Note: This parameter accepts an expression, meaning you can provide a literal value, a column reference, or the result of another function. Required
• pattern: A string that specifies the format for positive numbers, and optionally for negative numbers separated by a semicolon (e.g., "#,###.#;(#,###.#)"). Optional
• options: Optional named arguments that control additional formatting behavior. Supported options include:
  • compact: Apply shorthand formatting (see Compact Notation Reference section below for supported values)
  • negSymbol: Use dash, parens, none, or default for how negative numbers are displayed
  • posColor, negColor: Set font colors using HTML hex codes (e.g., #FF0000)
  • posBold, negBold: Apply bold styling (true, false)
  • posItalic, negItalic: Apply italic styling (true, false)
  • posUnderline, negUnderline: Apply underline styling (true, false)
  • posPrefix, negPrefix: Add HTML/text before the number
  • posSuffix, negSuffix: Add HTML/text after the number Optional

Behavior

• Converts a numeric input into a string formatted according to the provided pattern(s).
• Supports rounding, currency symbols, percentages, exponential notation, and byte/bit shorthands.
• Can convert durations (in seconds) into formatted strings like '2 hours, 2 minutes'.
• Provides binary formatting for data sizes using codes like XA, XK, etc.
• Applies Half-Up rounding: values ending in 1–4 round down, 5–9 round up.
• When no pattern is provided, numbers are rounded to whole integers and displayed with commas.

Examples

NumberFormat({Revenue}, "$#,##0.00"): Formats the {Revenue} column as currency with two decimals, e.g., "$1,234.56".

NumberFormat(7322, "s"): Converts 7322 seconds into a formatted duration string: "2 hours, 2 minutes, and 2 seconds".

NumberFormat(922, "MB"): Formats 922 MB into a human-readable string like "0.9 GB".

NumberFormat(Percent, "00%"): Formats a percentage with two digits, e.g., 0.42 becomes "42%".

NumberFormat($_, "#,###.#B", compact="XA"): Formats a data value using binary shorthand, e.g., "1.0 GB".

NumberFormat(-62619000.185, "#,###.#B;(#,###.##)"): Formats the negative number -62,619,000.185 using the positive pattern #,###.#B and the negative pattern (#,###.##), resulting in (62,619,000.19).

NumberFormat({NetIncome}, "$#,##0.00", compact="M", negColor="#FF0000", posBold=true): Formats the {NetIncome} column as currency in millions. Negative numbers appear in red, and positive numbers are bolded.

Notes

• Use this function only in Label-type columns or in the format formula of metric/reporting new columns.
• Formatting is ignored in numeric columns.
• If neither pattern nor negativePattern is provided, values are rounded to the nearest integer with thousands separator.
• When formatting includes HTML, make sure prefix/suffix tags are balanced and valid.

Compact Notation Reference

The following tables show the supported compact notation values. In the tables, the Code column shows the values you can put after the compact= optional parameter. The Pattern for Charts column shows additions you can put at the end of the format pattern.

Standard Shorthand Notations

The following standard shorthand notations are supported. The symbols displayed for each standard shorthand format are locale-specific, and are reconfigurable in the project settings dialog. So, for example, this number format function would use all standard shorthand notations:

=NumberFormat($_, "#.#", compact=A)

Binary Shorthand Notations

The following binary shorthand notations are also supported:

When you use binary shorthands, you will most often be summarizing a number of bytes. In this case, use this as your NumberFormat statement:

=NumberFormat($_, "#,###.#B", compact="XA")

Similarly, to display this in a chart, enter this as the Chart Number Format: #,###.#B{@Xa}

Return type

String