Excel 2019 Bible

Chapter 23: Visualizing with Custom Number Formats and Shapes

23

Number formatting strings consist of different individual number formats separated by

semicolons. In this case, you see two different formats: the format to the left of the semi-

colon and the format to the right of the semicolon.

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

By default, any formatting to the left of the first semicolon is applied to positive numbers,

and any formatting to the right of the first semicolon is applied to negative numbers. So,

with this choice, negative numbers will be formatted with parentheses, whereas positive

numbers will be formatted as a simple number, like the following:

(1,890)

1,982

Note that the syntax for the positive formatting in the previous example ends with _). This tells Excel to leave a
space the width of a parenthesis character at the end of positive numbers. This ensures that positive and negative
numbers align nicely when negative numbers are wrapped in parentheses.

You can edit the syntax in the Type input box so that the numbers are formatted differ-

ently. For example, try changing the syntax to the following:

+#,##0;-#,##0

When this syntax is applied, positive numbers will start with the + symbol, and negative

numbers will start with a − symbol, like so:

+1,200

-15,000

This comes in handy when formatting percentages. For instance, you can apply a custom

percent format by entering the following syntax into the Type input box:

+0%;-0%

This syntax gives you percentages that look like the following:

+43%

-54%

You can get fancy and wrap your negative percentages with parentheses with the following

syntax:

0%_);(0%)

This syntax gives you percentages that look like the following:

43%

(54%)