Custom formatting can be confusing. Like regex, it sometimes looks like a random mix of numbers and special characters.
There’s quite a lot to cover in this area, so in this first post I’ll focus on two of the main symbols used: “0” and “#” and only on smaller numbers that don’t require thousands separators (which I’ll cover in a future post).
Starting from a Template
My usual approach for creating custom format codes is to start with the Number (Custom) dialog and change the Decimal places and Display Units as needed:
If I need to customise further, such as adding unicode characters, I’ll then click the Custom option, which caries over the settings from the previous step providing a template to start from:
The above approach will cover the majority of formatting use cases, however, if you need more customisation it’s useful to understand how the different encodings work.
Positive, Negative, Zero
The custom format codes can be split into three parts, separated by a semicolon, with each part allowing for different formatting based on whether the number is positive, negative or zero. For example, the below code will format:
- Positive numbers with two decimal places
- Negative numbers with one decimal place and a negative symbol “-” at the start
- Zeros with no decimal places
Two types of digit placeholder
- A zero will always show a digit at the position it is placed. If there is no digit in that position in the underlying data, a zero will be displayed.
- A hash symbol (#) will only show a digit if it exists at that position in the raw data.
- Only one placeholder is required to show all digits to the left of a decimal place
- To the right of a decimal place, a separate placeholder is required for each digit
Let’s walk through some examples looking first at only positive numbers and positive number formatting.
We start with an empty Format string. The data is displayed without any change in formatting.
The Zero Placeholder
With a single 0 all decimals are removed and the numbers are rounded to the nearest integer.
Adding a decimal place:
Adding one zero after the decimal place means all numbers will show one decimal place:
Adding a second zero means all numbers will show two decimal places. If the underlying number doesn’t have two decimal places, such as in the first two rows, below, a zero is shown. On the final row, the three decimal places are rounded up from .777 to .78
Forcing three decimal places:
And four. None of the underlying numbers has four decimal places, which is why we see at least one zero at the end of all numbers:
The Hash Placeholder
Going back a few steps, we’ll use the # symbol in place of zeros. With one placeholder, the result is the same:
With two # placeholders, we see a difference. Now only numbers with two decimal places or more in the underlying data are showing two digits. The first two rows show one decimal place only:
Displaying up to three decimal places:
Changing the zero on the left of the decimal place to a # only affects the first row by removing the zero before the decimal place.
This would be an unusual thing to do, but placing three zeros in the code before the decimal place forces three digits to be shown and uses zeros where the number contains fewer than three digits in that position:
With three zeros before and after the decimal place, all decimals places are in alignment:
Negative Numbers
We now focus on the code that is after the first semicolon, which determines the formatting for negative numbers (note, I’ve changed the raw data here to be all negative numbers):
This first example puts a “-” at the start of each negative number:
This examples uses parentheses instead:
Zero
Zero values use the third part of the encoding (after the second semi-colon). Here zero is formatted with two decimal places:
Text can also be used. It advised to put all text in quotes to ensure it displays as intended:
The next example shows:
- Positive numbers with up to three decimal places
- Negative numbers enclosed in parentheses with exactly one decimal place
- Zeros as “Zero”
Hiding Numbers
It’s also possible to not display one of the number types by including a semicolon for that section but with no digit placeholders. For example, here only the zero section of the formatting code is included so positive and negative numbers are not displayed:
Displaying Text Only
This final example shows how a different text string can be displayed in place of each number type:
Quick Reference Guide
I’ve attached a high-res, A4 image to the below tweet that you can download and use for reference:
Conclusion
I hope that’s a useful start for understanding custom number formatting codes. I’ll cover more examples in a future post (follow this twitter feed for blog post updates). If there’s any area of custom formatting you’d like me to include in future posts, feel free to message me.
Thanks!
Marc
Latest YouTube video
This video tutorial steps through how to create a dot plot in Tableau.
datavis.blog 