Excel Reports: Custom Number Formatting - dummies

By Michael Alexander

You can improve the readability of your Excel reports with custom number formatting. You can apply number formatting to cells in several ways. Most people just use the convenient number commands found on the Home tab.

By using these commands, you can quickly apply some default formatting (number, percent, currency, and so on) and just be done with it, but a better way is to use the Format Cells dialog box, in which you have the ability to create your own custom number formatting.

Follow these steps to apply basic number formatting:

  1. Right-click a range of cells and select Format Cells.

    The Format Cells dialog box appears.

  2. Open the Number tab and choose a starting format that makes the most sense for your scenario.

    In this figure, the format chosen is Number and the selected options are to use a comma separator, to include no decimal places, and to enclose negative numbers in parentheses.


  3. Click the Custom option as shown in this figure.

    Excel takes you to a screen that exposes the syntax that makes up the format you selected. Here, you can edit the syntax in the Type input box to customize the number format.


The number formatting syntax tells Excel how a number should look in various scenarios. Number formatting syntax consists of different individual number formats separated by semicolons.

In this case, you see: #,##0_);(#,##0)

Here you see two different formats: the format to the left of the semicolon, and the format to the right of the semicolon.

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


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, which 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 differently. For example, try changing the syntax to


When this syntax is applied, positive numbers will start with the + symbol and negative numbers will start with a – symbol. Like so:


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:


This syntax gives you percentages that look like this:


You can get fancy and wrap your negative percentages with parentheses with this syntax:


This syntax gives you percentages that look like this:


If you include only one format syntax, meaning you don’t add a second formatting option with the use of a semicolon separator, that one format will be applied to all numbers — negative or positive.