Tableau Desktop and Web Authoring Help

For Tableau Desktop

Specify a number format

1. Right-click (control-click on Mac) a number 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 FORMAT	FORMAT 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, including special characters (optional). See "Define a custom number format" in this topic for details.

Define a custom number format

To apply a custom number format in your viz:

1. Right-click (control-click on Mac) a number in the view and select Format.
2. In the Format pane, click the Numbers drop-down menu and select Custom.
3. In the Format field, define your formatting preferences using the following syntax: Positive number format;Negative number format;Zero values.

When defining your number format code, keep in mind that:

• You can specify the formatting for up to three types of numbers in the following order: positive numbers, negative numbers, and zeros.
• Each number type must be separated by a semicolon (;).
• If you specify only one number type, the format for that type is used for all numbers.
• If you specify two number types, the format for the first type is applied to positive numbers and zeros, while the format for the second type is applied to negative numbers.
• If you skip types in your number format code, you must include a semicolon (;) for each of the missing types.

Custom number format examples

Refer to the following table for examples of commonly used custom number format codes that you can use in your viz.

The syntax has three portions: <positive number format>;<negative number format>;<zero format> separated by semicolons.

USE CASE	NUMBER CODE SYNTAX	EXAMPLE OUTPUT
Show only positive values	#,##; ; (note a blank space after the 2nd and 3rd semicolon)	Positive values: 1,234 Negative values: (only the blank space displays) Zero values: (only the blank space displays)
Show only negative values	;-#,##;	Positive values: (nothing displays) Negative values: -1,234 Zero values: (nothing displays)
Show only zero values	;;0;	Positive values: (nothing displays) Negative values: (nothing displays) Zero values: 0
Hide zero values	#,###;-#,###;;	Positive values: 1,234 Negative values: -1,234 Zero values: (nothing displays)
Show negative values in parentheses	#,###;(#,###);;	Positive values: 1,234 Negative values: (1,234) Zero values: (nothing displays)
Add a character prefix to a value	$#,###.##;-$#,###.##;$0;	Positive values: $1,234.56 Negative values: -$1,234.56 Zero values: $0
Add a character suffix to a value	#%;-#%;0%;	Positive values: 12% Negative values: -34% Zero values: 0%
Add text descriptors	"$"#,#" Surplus";"$"-##" Shortage"; "$"0;	Positive values: $1,234 Surplus Negative values: $-1,234 Shortage Zero values: $0

There are several ways to customize your number format to meet your needs. For more code guidance and examples, see Review guidelines for customizing a number format in the Microsoft Knowledge Base.

Note: There are slight deviations between the options described by Microsoft and those available in Tableau. For example, custom number formats that align text within columns aren't relevant in Tableau. Additionally, custom number formats to color code text aren't applicable because you can apply color to text using the Marks card. (For more information on applying color, see Control the Appearance of Marks in the View(Link opens in a new window).) Be sure to only use custom number formats that apply in Tableau.

Include special characters in a custom number format

One of the benefits of custom number formatting is the ability to include special characters or symbols. Symbols can make it easier to quickly understand comparisons between measures and calculations in your viz.

For example, let’s say you want to show a month-over-month comparison of profits for three of your company’s top-selling products. Rather than using the standard label to show that the profit for these products changed +5%, -2%, and 0% label from last month, you can set the custom number format as follows to show these changes as ▲5%,▼2%, and ►0.

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’s 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 aren’t 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 as 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 Data 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’re 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 value (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 value using an indicator in the lower right corner of the view, plot it at a default value (such as zero for number fields), hide the value but connect the line, or hide and break the line to indicate that a null value exists.

   

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

Note: The Special Values area isn’t available for dimensions or discrete measures.