Microsoft Office Tutorials and References

In Depth Information

**Enhancing Reporting with Custom Number Formatting**

The number formatting syntax tells Excel how a number will look in various scenarios.

Number formatting syntax consists of different individual number formats separated by

semicolons. In this example, you see two different formats:

The format to the left of the semicolon. By default, any formatting to the left of the first

semicolon is applied to positive numbers.

●

The format to the right of the semicolon. Any formatting to the right of the first

semicolon is applied to negative numbers.

●

So in this scenario, negative numbers are formatted with parentheses, whereas positive

numbers are formatted as a simple number.

(1,890)

1,982

Notice 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 syntax ensures that positive and negative numbers align nicely when

negative numbers are wrapped within parentheses.

Note

You can edit the syntax in the Type input box so that the numbers are formatted differently. For

example, try changing the syntax to:

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

When 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 quite 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 this:

+43%

-54%