In Week 3 we learnt custom number formatting in Tableau. This blog post will walk you through the steps to achieve this. I’ll also provide you with a few resources to help with finding symbols to use in your custom formatting.
First, we will use the Sample Superstore data to create a chart to practice custom formatting.
- Open Tableau and connect to the Sample Superstore Data.
- Right click and drag “Order Date” to Columns and select Month (with a green calendar icon). This ensures that “Month” is continuous.
- Right click and drag “Customer Name” to Rows and select CNTD(Customer Name).
- Next, calculate the “Percent Difference” by right clicking on CNTD(Customer Name) in Rows and select “Quick Table Calculation” > “Percent Difference”.
- Holding Control, Drag CNTD(Customer Name) to Label.
- Finally, this visualisation shows the percentage of change in the number of distinct customers between month and the next and suggests there is a surge in customers in March, September and November.
Custom Number Formatting
By default, Tableau shows percentages with one decimal. With custom formatting, we can individually format numbers based on whether they are positive, negative or equals 0 and even add special characters or emoji.
The syntax for custom number formatting is as follows:
[Positive Number Formatting] ; [Negative Number Formatting] ; [0 Number Formatting]
Number formatting for positive, negative and 0 values are separated by a semicolon (;). Each formatting can be different to suit your needs.
For example, the following number formatting:
0.00 ; -0.0 ; 0
Can be interpreted as: Positive values are formatted with 2 decimals, negative values are formatted with 1 decimal and values that are equal to 0 are shown as 0.
To format with percentages, we can add the percent (%) symbol at the end of the number which looks like this:
0.00% ; -0.0% ; 0%
This can be useful for showing 0% change when the default is 0.0% which can look messy.
To apply this custom number formatting to our example,
- Right click on the label CNTD(Customer Name) in the marks card and select Format.
- Select “Numbers” > “Custom”.
- Paste in the following formatting: 0.00% ; -0.0% ; 0%.
- The resulting number formatting will have 2 decimals for positive, 1 decimal for negative and show 0 with no decimals.
Custom Number Formatting with Symbols and Emojis
Custom number formatting also accepts symbols and emojis in addition to numbers. There are many websites on the internet where you can find symbols. Here are a few I found helpful:
- Alt Codes List of Alt Key Codes Symbols (alt-codes.net)
- Examples: ▼▲♥ ♠ ☺
- Full Emoji List for Apple iPhone, Android, Windows – Unicode Character Table (unicode-table.com)
- This website also shows how each emoji looks like on various operating systems.
- Examples: 🔼🔽 ❗ 🔴 🟢 ✅ ❌ 👍 👎
To apply these, simply copy and paste the symbols/emoji in front, after or replace the numbers in the custom formatting field. The following example uses emoji in place of where the positive, negative and 0 values would go:
However, it is important to remember that emojis render differently on different operating systems and browsers. They will also look different on Tableau Desktop vs Tableau Public. It is important to understand where your dashboards will be used and the operating systems/browsers used to display them.
The following images demonstrate how different emojis look on Tableau Desktop/Tableau Public and Windows/macOS:
1. Tableau Desktop (Windows)
2. Tableau Public (Microsoft Edge, Windows)
3. Tableau Public (Safari, macOS)
Finally, to assist with emojis looking different, selecting an emoji on the second website shows what that emoji looks like on each system: