Custom Number Formatting in Tableau – Advanced Techniques Part 2

In the second part of this blog, I'll dive into:

• How to format numbers in thousands, millions, and billions.
• Adding prefixes and suffixes to your numbers.
• Formatting currency values for clear presentation.
• Handling internationalization with locale settings.
• The right way to format percentages (% vs. “%”).
• Incorporating Unicode characters for better readability.
• Guidelines on when to use quotes in formatting codes.

Positive, Negative and Zero

ere's a quick reminder from part 1 of the blog: In Tableau, number codes are divided into three sections, separated by semicolons.

To keep things simple, most of the examples below will skip the negative section. You can easily replicate it by copying the positive number section and adding a “-” sign or parentheses, as explained in part 1 of the blog.

However, keep in mind that if you only specify the positive numbers section, Tableau will automatically add a “-” sign to negative numbers and use the positive number format for zero values. For instance, the following number formatting code includes only the positive numbers section:

For explanations of the main placeholder characters (“0” and “#”), be sure to check out part 1.

As we begin working with larger numbers, it’s important to include a comma separator after every three digits to enhance readability.

To create a template in Tableau, use the Number (Custom) dialog. Here, I've set the Decimal places to zero (0) and checked the option to Include thousands separators.

Here’s how it looks in the view with formatted numbers: depending on the size of the number, one or more thousand separators are added, and no decimal places are shown.

When you click on Custom, Tableau will display the number code it generated to apply these settings.

You'll notice that both the positive and negative sections now include a comma.

We can simplify this code to #,0 and still achieve the same result. As mentioned in part 1, a single placeholder can represent all digits in the underlying data to the left of the decimal point (this was consistent in my testing).

Here’s the simplified number code and the formatted results, excluding negative values once again. The outcome is identical to the earlier screenshot that used the longer, generated code.

It's important to include a zero at the end to ensure that zero values in the data are displayed. If you used two hashes (`#,#`), the zero would not be shown.

Thousands, Millions, Billions

Next, let's format all the numbers in thousands, starting with no decimal places. To achieve this, simply add a comma at the end of the code.

The comma at the end effectively divides the raw data value by a thousand, removing the last three digits. Since we haven’t added decimal places yet, we're losing some information, but we’ll address that shortly.

To display the numbers in millions, just add another comma.

Any number less than a million is now displaying as zero. If #,# had been used at the start of the code instead of #,0, the zeros would not appear in the table above, and those cells would be blank.

To display numbers in billions, simply add another comma.

Two types of comma

The above example illustrates how commas are used in custom number codes:

- A single comma placed between two placeholders (0 or #) ensures that thousands separators are displayed throughout the entire number.

- Adding a comma without a placeholder immediately after it scales the entire number down by a thousand for each additional comma.

Adding decimal places to large numbers

To make the previous example clearer, we need to add decimal places to show fractions of the large number. Here, I've switched back to millions (by removing one of the three commas) and ensured two decimal places are displayed by adding .00 at the end.

The third row, above, represents 1.23 Million. 

Adding display units as a suffix

To clarify the above, we can add an "M" display unit by simply appending an "M" to the end of the code.

If you prefer a space before the "M," you can include one in the code.

In this example, I've switched back to billions, set the display to four decimal places, and added a space before the "B" display unit (though this format is not recommended, it's shown here for illustration).

Adding a prefix

If the raw data represents currency values, you may want to include a currency symbol in the format code for clarity. In this example, I’ve added a "$" symbol in quotes at the beginning and switched back to millions with two decimal places.

We can include additional text in the prefix, like “2Q Sales $”, to indicate that these values represent second-quarter sales in dollars.

Currency and Internationalization (Locale Settings)

When text is enclosed in quotes (""), it will always appear exactly as specified in the format code.

Returning to a basic number code that adds a thousands separator and includes a dollar symbol at the start:

If we remove the quotes (“”) and keep the $ symbol, the display will now update according to the Workbook Locale settings. For example, with my locale set to “English (United Kingdom)”:

So the $ currency symbol displays as a pound symbol £:

If I change the locale to “Spanish (Spain)”, the same number formatting code will display a euro (€) currency symbol instead.

Note that the thousands separator (in the formatted column) has changed from a comma to a period, and the decimal point separator (in the raw data column) has changed from a period to a comma. This is because this is the standard number format used in most European (and many other) countries—see this page for a more complete list.

This is crucial when working with currency data to ensure the correct currency symbol is displayed, no matter where in the world people are viewing your workbook!

To ensure the correct currency symbol appears for your data, always enclose it in quotes, like this: “$”.

Formatting percentages (%)

Let's begin by having Tableau generate a number formatting code for us. Select "Percentage" and specify one decimal place.

By selecting "Custom," we can view the code Tableau generated and see how it affects our new raw data in the view.

If you have a mix of percentage values both below and above 1%, you can’t selectively turn off the decimal places for values over 1%. Removing the decimal place code will eliminate decimal places from all values, including those less than 1%, which will then round up to 1% (or down to 0%, if the raw values are below 0.005).

The % symbol serves two functions:

1. It converts the raw data into a percentage by shifting the decimal place two positions to the right.

2. It adds a % symbol to the display.

If we remove the % symbol and format the display to two decimal places, the result will be:

Adding the % symbol performs the two previously mentioned actions:

If our data is storing percentage values as whole numbers rather than decimals representing percentages, we need to wrap the percentage symbol in quotes: “%”. This will treat it as a text suffix only and will not affect the decimal point position.

In this example, the raw data contains percentages as whole numbers (e.g., 0.5 represents 0.5% rather than 50%).

Removing the quotes from the above example will lead to incorrect results being displayed.

Unicode Symbols

For visual encoding, you can add Unicode characters to each part of the number format code. A common application is to use up and down arrows to highlight positive or negative changes, such as in a year-over-year (YoY) comparison:

Displaying percentage changes with ▲ and ▼ Unicode characters:

Note that you need to include the zero section of the code (without a Unicode character); otherwise, the positive section will be used to format zero values, which could result in an upward arrow appearing for zeros.

Here’s another example: using currencies formatted with a $ symbol and zero decimal places. In this case, zero values are displayed as an em dash “—” only.

Other unicode characters are below, which you can copy and paste as needed:

▲ ▼ 

△ ▽

↑ ↓  

⇧ ⇩

⏶⏷

⭧ ⭨

⮝ ⮟

🠉 🠋

The use of quotes in custom number codes

If you add characters to the prefix or suffix boxes in Tableau’s Number (Custom) dialog and then switch to the Custom view, you’ll see that Tableau’s generated code wraps both of them in quotes. For example, if you add “Sales” as a prefix and “ in Q2” as a suffix, the code will look like this:

In the Custom view, Tableau has generated the number code below, which includes quotes. I’ve highlighted the prefix and suffix in the positive section of the code in bold:

If the quotes are removed from the suffix “ in Q2,” it remains unaffected and displays as before. (I’ve removed the negative and zero sections for clarity.)

However, if we also remove the quotes around the prefix “Sales,” the formatting will break.

What’s Happening?

Custom codes can also be used to format dates and times. Different codes, primarily letters, dictate which date/time parts to display and their format. These include letters for days (d), weeks (w), hours (h), seconds (s), and more. For a detailed list, see here.

Since “Sales” contains the letter “S,” Tableau interprets the raw data as a date/time part and formats it accordingly. The entire number code is then treated as date formatting, causing the “n” and “Q” in the suffix “in Q2” to be interpreted as minutes and quarters, respectively.

So, should everything be in quotes?

Not necessarily. Various characters can be used without quotes. Tableau, for example, includes Display Units (K, M, B) or negative values without quotes. The following Number (Custom) settings will generate a code that displays negative values in parentheses and uses the letter “M” to indicate that values are in millions. (Note: The letter “M” itself does not convert the raw data to millions; this is done by using two commas as explained earlier).

Switching to the Custom dialog, we can see that the code Tableau generated does not have quotes around the parentheses or the display unit M:

Quotes could be added around each of these characters but it will have no effect on the formatted output: 

To summarize, here are recommendations for using quotes based on my testing:

- Always use quotes for text prefixes.

- Use quotes for longer suffixes to ensure consistency.

- Quotes are optional for display unit suffixes (e.g., K, M, B).

- Quotes are optional for the minus sign and parentheses used for formatting negative numbers.

- For currency data, use quotes around the currency symbol unless you need a dynamic symbol based on the consumer’s locale settings.

- For percentage data stored as decimals (e.g., 0.1 = 10%), don’t use quotes around the percentage symbol: %.

- For percentage data stored as whole numbers (e.g., 10 = 10%), use quotes around the percentage symbol: “%”.

- Quotes are optional for Unicode arrows (e.g., ▲▼) based on my testing.

Combination of codes

This final example combines many of the codes we’ve discussed. While this isn’t a recommended format for this data, it serves as a useful demonstration of the various options available.

Let’s break down each part: