How to use Tableau Software’s Custom Number Formats
Tableau provides a variety of built-in number and date formats. There are the standard formats “numbers”, “currency”, “scientific” and “percentage”. For scientific and percentage you can only change the decimal places, but for numbers and currencies you can also define the way negative values are displayed, the displayed units, the thousand separator and you can add a prefix and/or suffix to the number. For date fields, Tableau offers 17 different standard formats.
Tableau’s built-in number and date formats will meet most of your requirements. From time to time, however, you need something different. E.g. you want to see “Quarter” instead of “Q” on your date axis, you want to display numbers in hundreds or you need to add some text to the numbers displayed. This is where Tableau’s Custom Number Formats come into play. Today’s post provides some links to learn the principles and the syntax, includes some examples and discusses Tableau’s limitations compared to Microsoft Excel.
Tableau’s Custom Formats
The Tableau Software Manual states:
“Custom: Type in the format you want to use.
This format can be specified by an Excel style number code.”
Well, I think there is no need to go into the details here. There are tons of how-to-tutorials on Microsoft Excel’s Custom Number Formats. Here are just 2 links to get you started: Microsoft Office Excel Custom Formats or Ozgrid: Excel Custom Formats.
Except for some limitations (see below), the principle and the syntax are exactly the same with Tableau. If you aren’t already familiar with Custom Number Formats in Excel, simply refer to one of the links provided for Excel.
Examples
Here is an arbitrary selection of examples:
| Add text next to the numbers: | "Profit:" #,##0.00;"Loss:" -#,##0.00; |
| Replace the numbers by text: | "Profit";"Loss" |
| Numbers in tens: | 0"."0;-0"."0 |
| Numbers in hundreds: | 0"."00;-0"."00 |
| Numbers in trillions: | #,##0,,,,.00T;-#,##0,,,,.00T |
| Show only positive values: | #,##.0; ; |
| Show only negative values: | ; -#,##.0; |
| Add leading zeros to a number: | #,### 000000000 |
| Split numbers into dollars and cents: | #,## "USD &" .00 "Ct"; - #,## "USD &" .00 "Ct" |
| Add symbols: | #,##0º |
| Show Quarter instead of Q: | yyyy "Quarter" q |
| Times instead of dates: | hh:mm:ss |
| Only the months: | mmmm |
| Show the full date and am/pm time: | ddd, mm/dd/yyyy, hh:mm am/pm |
| Special syntax like telephone numbers: | (###) ###-###-### |
| Don’t show anything at all: | ; |
| (I am not sure why you should want to hide everything, but you never know.) |
How to get familiar with the concept
If you are not yet familiar with the concept of Custom Number Formats, I recommend simply selecting a standard format first, click on “custom” then and see how the standard format code looks like. If you repeat this step for a couple of standard formats, you will quickly understand how to use Custom Number Formatting.
Limitations
As mentioned above, Tableau’s Custom Number Formats are following Microsoft Excel’s standards. There are some limitations, though. The following Excel features are not available in Tableau:
- No Color Coding
Excel allows you to color code the text font of a cell with custom formatting:
e.g. [Green] 0.00;[Red]-0.00;[Blue] 0 displays a positive number in green text font, a negative number in red and a zero in blue.
- No Comparison Operators
[<50]"Low";[>100]"High";"Average" in Excel will display the defined texts depending on the values. Cells with a value lower than 50 will display “Low”, etc. Of course, you can combine the color coding (see above) and the comparison operators. None of them are available in Tableau.
- No Repetition of Text
Excel allows you to fill the cell with any selected character and the number by using the asterisk (*), e.g. *_ 0.00 will show underscores left to the number. Tableau doesn’t provide this feature.
Especially the color coding is helpful sometimes and would be nice in Tableau,too. However, you can always use Tableau’s Calculated Fields to get what you want.
A Tableau Oddity: Is it a bug or a feature?
During the preparation of this post, I found a little oddity. It’s not a problem at all, but I thought you might be interested: If you are custom formatting a number and use the letter “C” (case insensitive), the number is displayed as a date including the time. I have no clue why.
Stay tuned.