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:

#,##0,

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

unformatted number.

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.

Note

If needed, you can even indicate that the number is in thousands by adding a “k” to the number syntax.

#,##0,”k”

This syntax shows your numbers like this:

118k

318k

You can use this technique on both positive and negative numbers.

#,##0,”k”; (#,##0,”k”)