Excel 2019 Bible

Chapter 23: Visualizing with Custom Number Formats and Shapes

23

Custom number formatting has obvious advantages over using other techniques to format numbers to thousands. For
instance, many beginning analysts would convert numbers to thousands by dividing them by 1,000 in a formula. But
that changes the integrity of the number dramatically. When you perform a mathematical operation in a cell, you
are literally changing the value represented in that cell. This forces you to carefully keep track of and maintain the
formulas you introduced simply to achieve a cosmetic effect. Using custom number formatting avoids that by chang-
ing only how the number looks, keeping the actual number intact.

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

number syntax.

#,##0,"k"

This would show your numbers like this:

118k

318k

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

#,##0,"k"; (#,##0,"k")

After applying this syntax, your negative numbers also appear in thousands.

118k

(318k)

Need to show numbers in millions? Easy. Simply add two commas to the number format syn-

tax in the Type input box.

#,##0.00,, "m"

Note the use of the extra decimal places (.00). When converting numbers to millions, it’s

often useful to show additional precision points, as in the following example:

24.65 m

Hiding and suppressing zeros

In addition to formatting positive and negative numbers, Excel allows you to provide a format

for zeros. You do this 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 any cells that con-

tain zeros:

#,##0_);(#,##0);"n/a"

You can also use this to suppress zeros entirely. If you add the second semicolon but don’t

follow it with any syntax, cells containing zeroes will appear blank.

#,##0_);(#,##0);