New Feature Adds to Custom Formatting Capabilities

The Format Customizer

Based on important feedback received from our users, we recently added Excel-like custom formatting to the Report Editor. This new feature – Format Customizer – adds an important layer of flexibility when it comes to creating reports the way you want them.

Previously, the formats available under the Custom Format toolbar button may not have had all the formatting you needed (Fig. 1). If this was something that you ran into in the past, I would encourage you to try out the new Format Customizer.

Fig. 1

You can use the new Format Customizer toolbar button to create your own number and text formats (Fig. 2 and 3):

Fig. 2

Fig. 3

The Format Customizer lets you create formats using the same custom format strings available in Excel. A format string has up to four format sections separated by semicolons. These sections define distinct formats for positive numbers, negative numbers, zero values, and text, in that order.

<POSITIVE>;<NEGATIVE>;<ZERO>;<TEXT>

For example, if you need a format where negative numbers are red surrounded by parenthesis, and positive numbers are blue, you would select the cells to format, click Format Customizer, then enter the following format string:

[Blue]#,##0.00;[Red](#,##0.00)

Here’s what it looks like in the Format Customizer (Fig. 4) and the results of the formatting (Fig. 5):

Fig. 4

 

Fig. 5

Now, let’s say you now want zeros to be Green instead of Blue. That becomes:

[Blue]#,##0.00;[Red](#,##0.00);[Green]#,##0.00

And, this is how it will look (Fig. 6):

Fig. 6

There are many characters and combinations of characters you can use within the format strings which allows for the creation of an almost infinite variety of formats. The following tables list the more common formatting characters and their meanings:

Text and Numbers

CharacterMeaning
0Digit placeholder. Displays insignificant zeroes. For example, 8.9 with the 00.000 format renders 08.900.
#Digit placeholder. Does not display insignificant zeroes. For example, 12.34 in the ###.### format renders 12.34.
.Displays a decimal point.
,Displays a thousands separator (when used between Digit placeholders).
\Escapes the next character (display literally).
_Skips the width of the next character.
“text”Includes a piece of text in the format. Characters inside are not interpreted in any way, but are literally output.
@Text placeholder. Is replaced with the text in the cell.

Dates

Format StringMeaning
MDisplays the month number without a leading zero.
MmDisplays the month number with a leading zero.
MmmDisplays the short month name
MmmmDisplays the full month name
DDisplays the date number without a leading zero.
DdDisplays the date number with a leading zero.
DddDisplays the abbreviated weekday name.
DdddDisplays the full weekday name.
YyDisplays the year as a two-digit number.
YyyyDisplays the full year number.

Time

Format StringMeaning
HDisplays the hour without a leading zero.
HhDisplays the hour including a leading zero.
MDisplays the minute without a leading zero.
MmDisplays the full month name
SDisplays the second without a leading zero.
SsDisplays the second including a leading zero.
AM/PMDisplays hours in a 12-hour clock accompanied by an AM or PM indication.
am/pmDisplays hours in a 12-hour clock accompanied by an am or pm indication.

Be aware that month and minute specifiers are ambiguous (m or mm). These strings are interpreted as a month number, unless preceded by an hour part (h or hh). In such cases, it displays minutes.

Report Editor vs. Excel

If you have prior experience using the Excel custom format strings, be aware that the following options available in Excel are not available in the Report Editor:

  • Exponent (scientific) notation i.e., the E+, E- Excel formats
  • Filling cell width i.e. the * Excel format

Summary

In summary, the Excel-like Format Customizer provides a very powerful and flexible means of creating your own number and text formats. It can be used just like you have already in Excel with only a few minor differences. For more information please reference the Qvinci Knowledge Base article titled: Apply Custom Formatting for Financial Data Cell. Or, use your favorite search engine to find some of the excellent and rich sets of Microsoft Excel documentation and examples.

 

2017-05-26T20:11:24+00:00 May 26th, 2017|Helpful Articles|0 Comments