Format Numbers and Null Values

When you format a measure, you can specify the number format for both the axis and the pane text. You can select from a set of standard formats, such as number, currency, scientific, and percentage; or you can define a custom number format using Microsoft Excel style format codes.

When a measure contains null values, the nulls are usually plotted as zero. You can use formatting, however, to handle the null values in a different way, such as hiding them.

For Tableau Desktop

Specify a number format

  1. Right-click (control-click on Mac) a measure or axis in the view and select Format.

  2. In the Format pane, click the Numbers drop-down menu.

  3. Select a number format.

    Some formats require additional settings. For example, if you select Scientific, you must also specify the number of decimal places.

Here are the number formats and associated options available in Tableau.

Number FormatFormat Options
Automatic: format is automatically selected based on either the format specified by the data source or the data contained in the field.None.
Number (Standard): format is based on locale selected.Locale: number format changes based on the geographical location selected.
Number (Custom): format is customized to your choice.

Decimal Places: the number of decimal places to display.

Negative Values: how negative values are displayed.

Units: the number is displayed using the specified units. For example, if the number is 20,000 and the units are thousands, the number will be displayed as 20K.

Prefix/Suffix: characters that precede and follow each displayed number.

Include thousands separators: whether the number shows separators every thousand (example: 100,000 vs. 100000).

Currency (Standard): format and currency symbol is based on locale selected.Locale: currency format based on the geographical location selected.
Currency (Custom): format and currency symbol is customized to your choice.

Decimal Places: the number of decimal places to display.

Negative Values: how negative values are displayed.

Units: the number is displayed using the specified units. For example, if the number is 20,000 and the units are thousands, the number is displayed as 20K.

Prefix/Suffix: characters that precede and follow each displayed number.

Include thousands separators: whether the number shows separators every thousand (example: 100,000 vs. 100000).

Scientific: numbers are displayed in scientific notation.Decimal: the number of decimal places to display.
Percentage: numbers are displayed as a percentage with the percent symbol. The value of 1 is interpreted as 100% and 0 as 0%Decimal: the number of decimal places to display.
Custom: format is based entirely on what is specified in the format options.Custom: type in the format you want to use. This format can be specified by a non-conditional Excel style number code. See Number format codes in the Microsoft Knowledge Base for details.

Set the default number format for a field

Right-click (control-click on Mac) the field in the Data pane and select Default Properties > Number Format.

In the subsequent dialog box, specify a number format to be used whenever the field is added to the view. The default number format is saved with the workbook. It is also exported when you export the connection information.

Note:Formatting numbers using the Format pane overrides any number formatting applied elsewhere.

Format a measure as currency

The view in the following image shows profit over time. Notice that the profit figures on the vertical axis are not formatted as currency.

To format the numbers as currency:

  1. Right-click the Profit axis and choose Format.

  2. On the Axis tab in the Format pane, under Scale, select the Numbers drop-down list, and then select one of the following:

    Currency (Standard) to add a dollar sign and two decimal places to the figures.

    Currency (Custom) to specify the number of decimal places, how to show negative values, the units, whether to include a prefix or suffix, and whether to include a separator character.

Use locale to specify number formats

By default, Tableau uses your computer's locale and language settings to format numbers. But you can explicitly set a different locale in the Format pane.

The following steps show how to set Swiss German currency, using the same view as in the previous section.

  1. Right-click the Profit axis and select Format.

  2. On the Axis tab, under Scale, select the Numbers drop-down list and then select Currency (Standard).

  3. In the Locale drop-down list, items appear in a Language (Country) format. For this example, select German (Switzerland).

    The view updates to show the sales figures in Swiss Francs, formatted for the German language.

Tip: You can change the default currency setting so that every time you drag the Profit measure to a view it uses the settings you want. In the Measures pane, right-click Profit (or other monetary measure), and select Default Properties > Number Format. Then format the field as shown above.

Format null values

When a measure contains null values, they are usually plotted in a view as zero. However, sometimes that changes the view and you’d rather just suppress null values altogether. You can format each measure to handle null values in a unique way.

To format null values for a specific field:

  1. Right-click the field in the view that has the null values (Control-click on a Mac) and choose Format.

  2. Go to the Pane tab.

  3. In the Special Values area, specify whether to show the null values using an indicator in the lower right corner of the view, plot them at a default value (e.g., zero for number fields), hide the values but connect lines, or hide and break lines to indicate that null values exist.

  4. If you specify text in the Text field, it will appear in the view for null values when mark labels are turned on. See Show and Hide Mark Labels .

Note: The Special Values area is not available for dimensions or discrete measures.

For Tableau Server or Tableau Online

Specify a number format

When authoring a view on the web, you can specify the number format for a field used in the view.

  1. In web editing mode, right-click a measure in the view and select Format Number.

  2. In the dialog box that appears, select a number format.

    Some formats provide additional settings. For example, if you select Currency, you can also specify the number of decimal places, as well as the units, and whether or not to include separators, such as commas.

    In this example, Sales is formatted as a Currency with zero decimal places and thousand (k) units. Sales numbers in the view update with these settings. Labels and tooltips update as well.

    Here are the number formats and associated options available in Tableau.

    Number FormatFormat Options
    Automatic: format is automatically selected based on either the format specified by the data source or the data contained in the field.None.
    Number (Custom): format is customized to your choice.

    Decimal Places: the number of decimal places to display.

    Units: the number is displayed using the specified units. For example, if the number is 20,000 and the units are thousands, the number will be displayed as 20K.

    Include separators: whether the number shows separators every thousand (example: 100,000 vs. 100000).

    Currency (Custom): format and currency symbol is customized to your choice.

    Decimal Places: the number of decimal places to display.

    Units: the number is displayed using the specified units. For example, if the number is 20,000 and the units are thousands, the number is displayed as 20K.

    Include separators: whether the number shows separators every thousand (example: 100,000 vs. 100000).

    Percentage (Custom): numbers are displayed as a percentage with the percent symbol. The value of 1 is interpreted as 100% and 0 as 0%Decimal Places: the number of decimal places to display.
    Scientific (Custom): numbers are displayed in scientific notation.Decimal Places: the number of decimal places to display.

 

Thanks for your feedback! There was an error submitting your feedback. Try again or send us a message.