Introduction

Why did the mathematician prefer decimals to fractions, because fractions are pointless. *Badum tss*

Ok - but seriously, it turns out that the conventions for decimal and thousands separators do in fact have a long and varied history. The specifics of which aren’t particularly useful here (but here’s a link for those who are interested). For the rest of us though, it is helpful to know that number format can be interpreted differently depending on the geographical location of your viewer. As such we may need to think about how we display our numbers, to get our point across more effectively!


The custom number format Tableau uses is
similar to that of Excel which is broken down into a semi-colon delimited string as follows (taken from the Microsoft site):


Ways of formatting numbers



As you can see in the picture, each segment of the string is delimited by a semi-colon and each segment dictates the format for the number based on its value relative to zero (above/below/at).

Excels cell-based notation means that a number of format features present in Excel aren’t available/necessary in Tableau, [colour] for example, as we can use the marks card for a lot of our formatting requirements. So in Tableau, we would achieve the colour format by dragging the appropriate value to colour by onto ‘Colours’ on the marks card.

Tableau number formatting

 

Character Meanings

There are a number of key characters and formats which will address the most commonly used custom number formats, they are as follows:

Character
Meaning

#

Stand-in to display significant digits

0

Force non-significant digits to display

.

Display the character “.” as the separator

,

Use a character “,” as the separator

ALSO

Delimit the sequence segment to format

i.e. #,, would take the first triplet of significant digits and leave the remaining two blank. 

So - 123,456,789 would become 123.

“” or Text or @ # /& ^ °

Text characters will be interpreted literally. Use Quotes for string literals else key characters will be extrapolated and interpreted differently.

C

Interestingly - when testing I found that using the letter ‘C’ in custom number format will force the value to a full date

D

The letter ‘D’ forces the value to assume a Day/Date value 

M

The letter ‘M’ forces the value to assume a Month/Minute value. The selection between minutes and months seems to default to month unless the previous characters are ‘hh:’.

W

The letter ‘W’ forces the value to assume a Week value 

Y

The letter ‘Y’ forces the value to assume a Year number value. YY displays a 2 digit number. YYYY displays a 4 digit number.

Q

The letter ‘Q’ forces the value to assume a Quarter value 

H

The letter ‘H’ forces the value to assume an Hour value. HH will add leading zeros.

S

The letter ‘S’ forces the value to assume a Seconds value. SS will add leading zeros.

AM/PM
am/pm

Add AM/PM, allowing upper/lower case as directed.

Revision for 02/2021: For a more comprehensive discussion on date formats - I'd highly recommend Tableau's own post found here.

Formulation/Usage

Given an understanding of the way the format string is created and knowing the characters we have to draw from, we can now begin to string them together to create a custom format string that meets our criteria. There are countless formulae depending on the scenario, so it’d be a fruitless effort to attempt to list them all here. As such the best approach is to learn how to use the syntax.

Arguably the most important tools in your custom formatting arsenal are ‘#’ and ‘0’. As above, ‘#’ is used to display significant digits. Significant digits, for the most part, include all except leading & trailing 0’s. Using ‘0’ will force a digit to show in its place. If there is no digit, a zero will be displayed - conversely ‘#’ can be used to add leading and trailing zero's whilst limiting the threshold of significance.

Use Cases

 
Use Case
Syntax

Show only positive values

#,##; ;

Show only negative values

;#,##;

Show only zero values

;;0;

Hide zero values

#,##;-#,##;;

Represent zeros with text

#,##;-#,##;“Zero”

Add leading zeros to a number

00000#.0;

Add a Character to a number as a prefix/suffix

(Here we are adding the degree symbol as a suffix to the numerical value)

#,#.0°;#,#.0°;

Dates

d/mm/yyyy hh:mm:ss

Replace actual values with textual representation

(note - quotes are needed to prevent denote string literals and prevent Tableau picking out special characters)

"Positive";"Negative";”Zero”;


▲;▼;-;

 

So there you have it, custom number formats 101.

Do get in touch if you have any comments/queries!

 

Empower your organization with intuitive analytics

Tableau is designed to put the user first because data analysis should be about asking questions and not about learning software. With built-in visual best practices, Tableau enables limitless visual data exploration without interrupting the flow of analysis.

As the market-leading choice for modern business intelligence, the Tableau platform is known for taking any kind of data from almost any system and turning it into actionable insights with speed and ease. It’s as simple as dragging and dropping.

We are a full-stack provider and integrator, relying on extensive experience and best practices to find your unique optimal set-up allowing you to tell the data stories you are eager to tell.

 

 

Let's talk about your data challenges

Author
Chris Beagley

Chris Beagley

Chris is a junior data engineer working with Biztory for the past 2 years as an analytics consultant. He enjoys solving niche problems and building things in Python. He is very passionate about the environment and sustainability.

Read more articles of this author
Let's discuss your data challenges

Join our community of data enthusiasts

Get industry insights, expert tips and Biztory news sent straight to your inbox with our monthly newsletter.