Templates that are created in the Report Editor are able to be styled and formatted as desired. Users have varying preferences on how financial data cells should appear: with dollar signs or without dollar signs, decimals or no decimals, negative values in parentheses verses the preceeding dash, zeros as blank cells verses a single 0 verses 0.00, etc. The list of preferences goes on and on, as well as the combination of how positive values, negative values and zeros should be mutually displayed.
The Report Editor offers some quick click options, much like Excel, for formatting of the financial data cells. The format dropdown offers this quick formatting capability:
However, when a user needs more options related to custom formatting of financial data cells, this is when Qvinci's "Format Customizer" tool comes in quite handy. The Format Customizer allows a user to insert an Excel string of characters to drive the format for positive values, negative values, and zeros.
Example Format Strings
The Format Customizer follows the same string format as Excel strings:
- positive number format; negative number format; zero format
- semi-colon delineation is required
Some examples of Excel formatting strings include:
|Excel String||Description||Positive Value Appearance||Negative Value Appearance||Zero Appearance|
|#,##0.00;(#,##0.00);0.00||two decimals on all values and negatives in parentheses||1,000,000.00||(1,000,000.00)||0.00|
|#,##0.00;-#,##0.00;0.00||two decimals on all values and negatives with preceding dash||1,000,000.00||-1,000,000.00||0.00|
|#,##0;[RED](#,##0);0||no decimals, negatives in parentheses, zeroes displayed as single digit||1,000,000||(1,000,000)||0|
|$#,##0;$(#,##0);$0||no decimals,$ on all values, negatives in parentheses, zeros displayed as single digit||$1,000,000||($1,000,000)||$0|
|_($#,##0_);_($(#,##0);_($"-"_||no decimals on non-zeroes, $ on all values, negatives in parentheses, zeros display as a dash||$1,000,000||($1,000,000)||$ -|
|_($#,##0.00_);_($-#,##0.00;_($"0"_||two decimal on non-zeroes, $ on all values, negatives with preceding dash||$1,000,000.00||-$1,000,000.00||$0|
|_($#,##0.00_);_($ (#,##0.00);_($"0.00"_)||two decimals on all values, $ on all values, negatives in parentheses||$1,000,000.00||($1,000,000.00)||$0.00|
Apply Custom Formatting to Financial Cells
The Format Customizer allows for formatting strings to be free-typed into it however, it also does accept strings that already exist in Excel. If a specific formatting string is identified inside Excel, the user may simply copy/paste that string into Qvinci.
- Open the desired template from Reports & Libraries > My Reports
- Highlight the desired contiguous cells (whether a full column, a full row, multiple contiguous columns, or a single cell, etc)
- Open the Format Customizer in the top right of the tool bar
- Paste the desired format string into the Format Customizer and "submit"
- "Save" the template
Reminder: The format string should always follow this format:
positive number format; negative number format; zero format
Enable Null Cells to be Formatted as Zeros
In order to format null cells as zeros a toggle must be made in the Options tab of the template. View this article to see how this can be enabled. Once enabled, format strings may be used to drive the desired format for cells that are zero.
Apply Conditional Formatting to Financial Cells
The Format Customizer does also allow for a type of conditional formatting. When users need to set up conditional formatting, in order to color x-values "green", y-values "red" and other-values "yellow", etc, they will still use the Format Customizer to create this type of formatting. This type of formatting cannot be used to shade the background color of the cell itself, but rather just the dataset value.
For conditional formatting the user should use this string:
- High value format; low value format; other value format
- semi-colon delineation is required
Note that when inputting the color selection in the formatting string that the colors should be listed in capital letters: