Tableau Custom Number Formatting – Part 2

In part 2 of this blog I’ll cover the following topics:

• Formatting thousands, millions and billions
• Adding prefixes and suffixes
• Formatting currency values
• Internationalisation (locale settings)
• Formatting percentages (% vs. “%”)
• Using Unicode characters
• When to use quotes in formatting codes

Positive, Negative and Zero

A quick reminder from part 1 of the blog that number codes in Tableau are split into three sections, separated by semicolons:

For simplicity, most of the examples below will omit the negative section as this can easily be replicated by copying the positive number section and adding a “-” sign or parentheses as desired, as described in part 1 of the blog.

That said, note that if only the positive numbers section is specified, Tableau will automatically add a negative sign “-” to the display of negative numbers and will use the positive numbers section to format zero values, for example, the below number formatting code only includes the positive numbers section:

Also, see part 1 for explanations of the primary placeholder characters (“0” and “#”).

Thousands Separators

As we start to look at larger numbers we’ll want to show a comma separator after each group of three digits so the numbers are easier to read.

Let’s use Tableau’s Number (custom) dialog to create a template to start with. Here I’ve set the number of Decimal places to zero (0), and checked the Include thousands separators checkbox.

Here’s the effect in the view on the Formatted numbers. One or more thousand separators are added depending on the size of the number and no decimal places are displayed:

Clicking Custom will show the number code that Tableau generated to create these settings:

You can see that both the positive and negative sections now include a comma.

We can actually simplify this code to be #,0 and still get the same effect. Remember from part 1 that, left of the decimal place, a single placeholder can be used to represent all digits in the underlying data (at least this was the case throughout my testing).

Here’s the simplified number code and the formatted results, again excluding negative values. The results are the same as the earlier screenshot that used the longer generated code:

There needs to be a zero at the end to ensure zero values in the data are shown. If two hashes were used (#,#) the zero would not show.

Thousands, Millions, Billions

Let’s now format all the numbers as thousands, first with no decimal places. To do this, add a comma at the end of the code:

The comma at the end essentially divides the raw data value by a thousand, thereby removing the last three digits. Because we’ve not added decimal places yet, we’re starting to lose information. We’ll add decimals shortly.

To show the numbers as millions, we simply add another comma:

Any number less than a million is now showing as zero. If #,# was used at the start of the code (instead of #,0) the zeros in the table above would not show and those cells would be blank.

To show billions, we again add one more comma:

Two types of comma

The above demonstrates the different uses of a comma in custom number codes.

• A single comma added between two placeholders (0 or #) will ensure thousands separator(s) are displayed throughout the whole number
• Adding a comma with no placeholder immediately after it scales the whole number by a thousand for each successive comma added

Adding decimal places to large numbers

For the previous example to make more sense, 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 am forcing the display of two decimal places by adding .00 at the end:

#,0,,.00

The third row, above, represents 1.23 Million. 

Adding display units as a suffix

To make the above clearer we can add an “M” display unit by literally adding an M at the end of the code:

#,0,,.00M

If you prefer a space before the M, you can add one in.

Here, I’ve switched back to billions, forced the display of four decimal places and added a space before the B display unit (not a format I’d recommend but just as an example):

Adding a prefix

If the raw data is currency values, we might want to add a currency symbol to the format code to make that clear. Here I’ve added a $ symbol in quotes at the start (and switched back to millions with two decimal places).

We can add other text to the prefix, such as “2Q Sales $”, to specify these are second quarter sales in $.

"2Q Sales $"#,0,,.00 M

Currency and internationalisation (locale settings)

If text is enclosed in quotes “” as above, it will always display as shown in the format code. 

Going back to a simple number code that just adds a thousands separator and a dollar symbol at the start:

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

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

Changing my locale to “Spanish (Spain)” will display a euro € currency symbol with the exact same number formatting code: 

Note also 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. That is because this is the standard number format in most European (and many other) countries – see this page for a more complete list.

This is obviously very important if you are working with currency data and need to display the correct currency symbol regardless of where in the world people may be opening your workbook!

To be sure the correct currency symbol displays for the currency data you are working with, always enclose it in quotes, like so: “$”

Formatting percentages (%)

Again, let’s start by having Tableau generate a number formatting code for us by selecting Percentage and specifying one decimal place:

Selecting Custom, we can then see the code Tableau generated and the effect this has on our new raw data in the view:

If you have a mix of percentage values that are less than and more than 1%, you can’t turn off the decimal place on those values over 1% only. Removing the decimal place code, will remove decimal places from values less than 1% and they will then round up to 1% (or down to 0%, if the raw values are less than 0.005):

The % symbol has two purposes:

• Convert the raw data to a percentage (i.e. shift the decimal place two positions to the right)
• Add a % symbol to the display

If we remove the % symbol and format the display to two decimal places we see this:

Adding the % symbol performs the two previously mentioned actions:

What if our data is storing percentage values as whole percentages and not as decimal values representing percentages? 

In that case we need to wrap the percentage symbol in quotes: “%” so it becomes a text suffix only and has no effect on the decimal point position.

This example has raw data containing percentages not in decimal format (i.e. 0.5 is 0.5% and not 50%):

If the quotes are removed from the above example, erroneous results will be displayed:

Unicode Symbols

For visual encoding, we can add unicode characters to each part of the number format code. The most common use case is adding up and down arrows to highlight a positive or negative change/value, for example, in a year-over-year (YoY) comparison:

Showing a percentage change in values using ▲▼ unicode characters:

Note, the zero section of the code needs to be added (without a unicode character), otherwise the positive section will also be used to format zero values resulting in an upward arrow for zeros:

Another example, using currencies formatted with a $ symbol and zero decimal places. Zero values are displayed as an em dash “—” only:

"▲ $"#,#;"▼ $"#,#;"—"

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

See this site for many more Unicode characters.

The use of quotes in custom number codes

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

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

"Sales "#,##0.00" in Q2";-"Sales "#,##0.00" in Q2"

If the quotes are removed from the suffix, “ in Q2”, it is 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 “, then the formatting breaks:

What’s happening? 

Custom codes can also be used to determine the formatting of dates and times. Various codes (mostly letters) are used to determine which date/time parts to show and in what format. These include letters to represent: days (d), weeks (w), hours (h), seconds (s) and so on. See here for a detailed list. 

Because “Sales” contains one of these letters, “S”, Tableau interprets the raw data to be of that date/time part and displays it as such. The whole number code is then interpreted to be formatting dates, so the “n” and “Q” that are contained within the suffix “in Q2” are then also interpreted as date formatting (as minutes and quarters respectively in this example). 

So everything should be in quotes? 

Not necessarily. Various characters can be added without quotes. Indeed, Tableau does this when generating codes that contain Display Units (K, M, B) or negative values. The below Number (Custom) settings will generate a code that shows negative values surrounded by parentheses and uses the letter “M” to signal to the reader that values are displayed in Millions. (Remember, it’s not the letter M that converts the raw data values to millions, that is done with the use of two commas as seen 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:

#,##0,,.00M;(#,##0,,.00M)

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

#,##0,,.00"M";"("#,##0,,.00"M)"

To summarise, below are recommendations for using quotes based on my testing: 

• Always use quotes with text prefixes
• Use quotes on longer suffixes for consistency
• Quotes are optional for display unit suffixes (e.g. K, M or B)
• Quotes are optional for the minus sign and parentheses used to format negative numbers
• For currency data, unless you require a dynamic currency symbol based on the consumer’s locale settings, use quotes around the currency symbol.
• For percentage data stored as decimals (e.g. 0.1 = 10%), don’t use quotes around the percentage symbol: %
• For percentage data stored as percentage values (e.g. 10 = 10%), use quotes around the percentage symbol: “%”
• Quotes are optional for Unicode arrows (that I tested), such as: ▲▼

Combination of codes

This final example combines many of the codes we’ve looked at so far. Just to be clear, this isn’t a format I’d recommend for this data but it’s useful for demonstrating the various options:

"Profit $"#,0,,.### M;🠋 ("$"#,0,,.000) M;"⚑ NO SALES ⚑"

Let’s break down each part:

Thanks for making it to the end of this fairly long and detailed post. I hope it was useful.

The guidance in this post is based on my own test results, so if you find anything that doesn’t work as expected or you have any other general feedback, please feel free to message me on Twitter.

Thanks!
Marc