Custom Number Formatting in Tableau – Part 1

Custom number formatting in Tableau can be quite perplexing, much like regular expressions, often appearing as a jumble of numbers and special characters.

Given the breadth of this topic, I'll begin this series by exploring two fundamental symbols: “0” and “#”. For this initial discussion, I'll concentrate on smaller numbers that don't involve thousands separators, which I’ll address in a future post.

Starting with a Template

I typically begin crafting custom format codes by using the Number (Custom) dialog. From there, I adjust the Decimal Places and Display Units according to my requirements.

If I need to make additional customizations, like incorporating Unicode characters, I then select the Custom option. This choice carries over the settings from the previous step, giving me a template to build upon.

The approach outlined will address most formatting scenarios. However, for more advanced customization, it's beneficial to understand how different encodings operate.

Positive, Negative, Zero

Custom format codes can be divided into three sections, each separated by a semicolon, to format positive, negative, and zero values differently. For instance, the following code formats:

- Positive numbers with two decimal places

- Negative numbers with one decimal place and a leading negative symbol “-”

- Zero values with no decimal places

Two Types of Digit Placeholders

A zero (`0`) in a format code will always display a digit at its position. If there is no digit in that position within the data, a zero will be shown instead.

A hash symbol (`#`) will only display a digit if one exists at that position in the raw data.

One placeholder is sufficient to display all digits to the left of the decimal point, whereas a separate placeholder is needed for each digit to the right of the decimal point.

Let's explore some examples, starting with positive numbers and their formatting.

We begin with an empty format string, so the data is shown without any additional formatting.

The Zero Placeholder

Using a single 0 will round numbers to the nearest integer and remove all decimal places.

Adding a decimal place:

Adding one zero after the decimal place means all numbers will show one decimal place:

Adding a second 0 ensures that all numbers display two decimal places. If the underlying number doesn't have two decimal places, as seen in the first two rows below, a zero will be shown. In the final row, the three decimal places are rounded from .777 to .78.

Forcing three decimal places

And with four decimal places specified, none of the underlying numbers have four decimal places, which is why at least one zero appears at the end of each number.

The Hash Placeholder

Returning to earlier examples, replacing zeros with the # symbol will yield similar results with one placeholder.

With two # placeholders, the behavior changes. Now, only numbers with at least two decimal places in the underlying data will display two digits. The first two rows, which have fewer than two decimal places, will show only one decimal place.

Displaying up to three decimal places

Replacing the zero to the left of the decimal place with a # affects only the first row, removing the zero before the decimal point.

Although it's uncommon, placing three zeros in the format code before the decimal point ensures that three digits are always displayed. If the number has fewer than three digits in that position, zeros will be used to fill the gaps.

With three zeros both before and after the decimal place, all decimal places are properly aligned.

Negative Numbers

Now, let’s examine the code following the first semicolon, which controls the formatting for negative numbers (note that the raw data has been changed to all negative values):

In this first example, a “-” is placed at the beginning of each negative number.

This examples uses parentheses instead:

Zero

Zero values are formatted using the third section of the encoding (following the second semicolon). In this case, zeros are displayed with two decimal places.

Text can also be included in the format. It is recommended to enclose all text in quotes to ensure it displays correctly.

The next example illustrates:

- Positive numbers with up to three decimal places

- Negative numbers enclosed in parentheses with one decimal place

- Zeros displayed as "Zero"

Hiding Numbers

You can choose not to display certain number types by including a semicolon for that section without any digit placeholders. For instance, including only the zero section in the formatting code will result in positive and negative numbers being hidden.

Displaying Text Only

In this final example, different text strings are shown in place of each number type.

Conclusion

I hope this gives you a solid introduction to custom number formatting codes. I'll provide more examples in upcoming posts, so be sure to follow this Twitter feed for updates. If there's a specific aspect of custom formatting you'd like me to cover in future posts, please don't hesitate to reach out!