Microsoft Office Tutorials and References
In Depth Information
Enhancing Reporting with Custom Number Formatting
After you apply this syntax, your negative numbers will also show in thousands.
Need to show numbers in millions? Easy. Simply edit the Type input box to add two commas to your
number format syntax.
Note the extra decimal places (.00). When converting numbers to millions, it’s often useful to show
additional precision points, as in:
Hiding and suppressing zeros
In addition to positive and negative numbers, Excel allows you to provide a format for zeros. You do
so by adding another semicolon to your custom number syntax. By default, any format syntax placed
after the second semicolon is applied to any number that evaluates to zero.
For example, the following syntax applies a format that shows “n/a” for cells that contain zeros.
You can also use this syntax to suppress zeros entirely. If you add the second semicolon but don’t
follow it with any syntax, cells containing zeros will show blank.
Again, custom number formatting affects only the cosmetics of the cell. The actual data in the cell is
not affected, as demonstrated in Figure 2-14. The selected cell is formatted so that zeros show as n/a,
but if you look at the formula bar, you can see the actual unformatted cell contents.
Figure 2-14: Custom number formatting that shows zeros as n/a.