Topics: 8 min readtime

How to use Tableau's Custom Number Formatting

Written by Chris Beagley
Tuesday 3 November, 2020

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 value 

Q

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

H

The letter ‘H’ forces the value to assume an Hour value

S

The letter ‘S’ forces the value to assume a Seconds value 

 

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 - therefore conversely to ‘#’ can be used to add leading and trailing zero's.

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!