Microsoft Office Tutorials and References
In Depth Information
Enhancing Reporting with Custom Number Formatting
3. In the Type input box, add a comma after the format syntax.
This syntax cosmetically changes your number to thousands place:
After confirming your changes, your numbers will automatically show in thousands place.
Here’s the beauty of this technique: It doesn’t change or truncate your numbers in any way. Excel is
simply applying a cosmetic effect to the number. To see what this means, take a look at Figure 2-13.
The selected cell is formatted to show in thousands: You see 118. But when you look in the formula
bar, you see the real unformatted number (117943). The 118 you see in the cell is a cosmetically
formatted version of the real number shown in the formula bar.
Figure 2-13: Formatting numbers applies only a cosmetic look. Look in the formula bar to see the real
Custom number formatting has obvious advantages over using other techniques to
format numbers to thousands. For instance, many beginning analysts convert numbers to
thousands by dividing them by 1,000 in a formula. But that changes the integrity of the
number dramatically, and it forces you to keep track of and maintain formulas that
could cause calculation errors later. Using custom number formatting avoids that by
changing only how the number looks, keeping the actual number intact.
If needed, you can even indicate that the number is in thousands by adding a “k” to the number syntax.
This syntax shows your numbers like this:
You can use this technique on both positive and negative numbers.