Microsoft Office Tutorials and References
In Depth Information
Enhancing Reporting with Custom Number Formatting
Format Code
1/31/2013 7:42:53 PM Displays As
mm-dd-yyyy h:mm AM/PM
01-31-2013 7:42 PM
h AM/PM
7 PM
h:mm AM/PM
7:42 PM
h:mm:ss AM/PM
7:42:53 PM
Adding conditions to customer number formatting
At this point, you know that Excel’s number formatting syntax consists of different individual number
formats separated by semicolons. By default, the syntax to the left of the first semicolon is applied to
positive numbers, the syntax to the right of the first semicolon is applied to negative numbers, and
the syntax to the right of the second semicolon is applied to zeros.
Positive Number Format; Negative Number Format; Format for Zeros
Interestingly, Excel allows you override this default behavior and repurpose the syntax sections using
your own conditions. Conditions are entered in square brackets.
In this syntax example, you apply a blue color to cells containing a number over 500, a red color to
cells containing a number less than 500, and n/a to cells containing a number equal to 500.
[Blue][>500]#,##0;[Red][<500]#,##0;”n/a”
One of the more useful ways to use conditions is to convert numbers to thousands or millions,
depending on how big the number is. In this example, numbers equal to or greater than 1,000,000
are formatted as millions, whereas numbers equal to or greater than 1,000 are formatted as
thousands.
[>=1000000]#,##0.00,,”m”;[>=1000]#,##0,”k”
Again, the conditions you use must be relatively basic. Even so, conditions give you another avenue
to gaining control over the display of the numbers in your dashboards and reports.
Search JabSto ::




Custom Search