Negative numbers with parenthesis – Excel

Negative numbers in Excel

In Excel, the basic way to format negative numbers is to use the Accounting number format.

This option will display your negative number in red.

 

But, for some reports, negative numbers must be displayed with parenthesis. Let's see how to do that

Customize your number

To display your negative numbers with parenthesis, we must create your own number format.

Open the dialog box "Cells format" with the shortcut Ctrl + 1 or by clicking on the last option of the number format dropdown list.

 

Parameters of numbers in Excel

Excel can return 4 different visualisations of a number. There is parameters for

  • Positive value
  • Negative value
  • Zero
  • Text

They are not compulsory. You can just write your parameter with the positive value only.

The order of the parameters are

Positive value;Negative value;Zero;Text

And also, you have to specify the way the number will be return.

  • Use 0 to display number or 0 if empty
  • Use # to display number or nothing

For instance, if you fill the custom format to 0000, the number 123 will be display 0123 in a cell.

Now if you have # ##0, the number 10 will be 10 in a cell (the first 2 # will be ignore)

Creation of the custom format

So with these informations, we are able to easily create of format with negative number with parenthesis

  • For the positive setting ###0.00
  • For the negative setting (###0.00)

The code you have to fill in the Type area is

0.00;(###0.00)

And if also you want the negative numbers in red, just add the color setting in the code.

###0.00;[Red](###0.00)

Related posts


Permanent link to this article: https://www.excel-exercise.com/negative-numbers-with-parenthesis-excel/


Leave a Reply

Your email address will not be published.