KNOWLEDGE BASE

Displaying Zeros as Blanks or Dashes


Published: 09 Jun 2014
Last Modified Date: 13 Dec 2016

Question

How to format zeros to display as blanks or dashes.

Environment

Tableau Desktop

Answer

Option 1: Display Zeros as Blanks

  1. Right-click the field that you want to format, and then select Default Properties > Number Format.
  2. Select Custom, enter #,##0.000; -#,##0.000;"", and then click OK.

Option 2: Display Zeros as Dashes

  1. Right-click the field to that you want to format, and then select Default Properties > Number Format.
  2. Select Custom, enter #,##0.000;-#,##0.000;-, and then click OK.

Option 3: Display Zeros as Dashes

  1. Select Analysis > Create Calculated Field.
  2. In the Calculated Field dialog box that opens, do the following and then click OK:
    • Name the calculated field. 
    • In the formula field, create a calculated field similar to the following:
      IF [<field name>] = 0 THEN "-" ELSE ROUND(STR([<field name>]), n) END
    • Where n is the number of decimal places that are to be shown in the view.
Note: This calculated field will convert the measure to a string. You will not be able to use this calculated field for calculating averages and other numeric operations.

Additional Information

  • Note: Right-clicking the field under the 'Measures' shelf and selecting Default Properties > Number Format will not apply that format to a field that is already part of the view, if the field's formatting is not already set to Automatic. This allows separate formatting options to be set across worksheets that use the same field.
  • A field's formatting can be changed on a worksheet-by-worksheet basis by right-clicking the field on the view and selecting 'Format...'. Otherwise, default properties will be used when a field is first brought to a new worksheet.
  • Clearly and Simply: Tableau Quick Tip #2 – Custom Number Formats (How to use Tableau Software’s Custom Number Formats)
  • Microsoft Office: Create or delete a custom number format
Did this article resolve the issue?