User Permissions Needed: Account Administrator, Reports Viewer With Cost, Reports Viewer
Insights Permissions: Can Edit
Overview
You can use the Number format editor in Insights to format metrics (numbers) to show or hide decimal places, truncate large numbers, apply text colors and background colors, and more.
How to Format a Metric in One Report Only
- While editing a report, select the What button that appears above the report. The list of metrics will open.
- In the center column, select the metric you would like to format.
The Detail column will populate with the metric information. - In the Detail column, click Edit in the Metric Format section.
The Number format editor modal opens. You can edit the number format, select format templates, and preview the number format as you make changes. - After you make changes to the number format, click Apply to save the formatting.
- Then click Done in the Detail column to regenerate the report.
How to Set the Default Format for a Metric
You can set the default format for a metric to ensure the metric follows a consistent format any time it’s used in a report. This will also change the metric's format in all the reports it's used in, excluding reports where the metric format was changed for that specific report.
- In the left navigation, select Insights then select any dashboard.
- Below the dashboard name, click Open in Advanced Editor.
- You'll receive a confirmation dialog alerting you that the Advanced Editor is for experienced Insights users. To continue, click OK.
- In the app bar at the top of the Advanced Editor page, select the Manage tab.
- In the Data section located on the left, select Metrics.
- Once the list of metrics populates, select a metric. The metric page opens.
- In the Metric Format section, select Edit.
The Number format editor opens in a modal. You can edit the number format, select format templates, and preview the number format as you make changes. - After you make changes to the number format, click Apply to save the formatting.
How to Round and Adjust Decimal Places
In the Number format editor, the number signs (#) that appear to the right of the decimal point determine how many decimal places appear.
Display More Decimal Places
To display more decimal places, add more number signs (#) to the right of the decimal point.
Example#,##0.###
Result
The number 12.6314 is displayed as 12.631
The number 3.7 is displayed as 3.7
Display Less Decimal Places
To display less decimal places, remove the number signs that appear to the right of the decimal point.
Example#,##0.##
Result
The number 12.6314 is displayed as 12.63
Force Zeroes to Display
To force zeros to display in empty decimal places, use 0 instead of #in the Number format editor.
Example#,##0.00
Result
The number 12.6 is displayed as 12.60
The number 3.7 is displayed as 3.70
Round Numbers
To round a number, remove the decimal point and the number signs that follow the decimal point.
Example#,##0
Result
The number 12.6 is displayed as 13
How to Truncate Large Numbers
Truncating large numbers can make them more readable. You may want to truncate large numbers when they appear in places like graphs or large tables.
- In the Number format editor, select the Truncate large numbers template.
The Number format editor will populate with several conditional rules. Each rule appears on its own line and defines the number range the rule will apply to and the number format to apply to numbers that fall within the range. - You can remove the currency symbols from the rules if necessary. Use the preview to the right to confirm that the number formatting is correct.
- Click Apply to save the formatting.
- Click Done in the Details column to regenerate the report.
How to Conditionally Format Numbers
You can create conditional formatting in the Number format editor using the following syntax:
[Numeric range][Text color][Background color]Number format;
- Numeric range—Specify the range of numbers that the number format should apply to. You can use the following symbols to define the numeric range:
>— greater than>=— greater than or equal to<— less than<=— less than or equal to=— equal to
For example, to apply a format to numbers greater than 0, the numeric range would be[>0].
Note: If the numeric ranges for different rules overlap, the first rule will override other rules.- Text color (Optional)—Specify the text color for the numeric range. The format is
[color=######]or[color code]. See the Text and Background Colors section for more information. - Background color (Optional)—Specify the background color for the numeric range. The format is
[backgroundcolor=######]. See the Text and Background Colors section for more information. - Number format—Specify the number format to apply for this numeric range. For example,
#,##0.00. - End each line with a semicolon (
;) to separate the rules.
Conditional format example:
[<0][white][backgroundcolor=FF4444]$#,#.##;
[<1000][white][backgroundcolor=0000FF]$#,#.##;
[>=1000][color=ffffff][backgroundcolor=00AA00]$#,#.##
TIP
The Bars, Truncate large numbers, and Conditional colors templates use conditional formatting. You can select these templates then modify them to your specifications.
How to Insert Characters
Characters such as currency symbols ($, £, etc.) and letters (h, K, M, B, etc.) can be added directly in number formats.
Example$#,##0.0
Result
The number 12.6 is displayed as $12.6
If you use a percent symbol in a number format, the number will be multiplied by 100 and display the percent symbol.
Example#,##0.0%
Result
The number 12.6 is displayed as 1,260.0%
To display a percent symbol without multiplying the number by 100, add \ in front of the percent symbol.
Example#,##0.0\%
Result
The number 12.6 is displayed as 12.6%
Text and Background Colors
You can use hex values to apply text and background colors.
Examples:
[color=99AE00]#,##0.0
[backgroundcolor=00FF00]#,##0.0
You can also use color codes to apply text colors. The following color codes are available:
BlackBlueCyanGreenMagentaRedYellowWhite
Example: [Blue]#,##0.0
Related Articles
