Displaying Negative Numbers in Parentheses – 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.

Menu currency

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

Customize your number format

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

Open the dialog box "Format Cells" using the shortcut Ctrl + 1 or by clicking on the last option of the Number Format dropdown list.

Menu to open the custom format

Or by clicking on this icon in the ribbon

Open Custom format number

Code to customize numbers in Excel

Excel can return 4 different display formats for a number. There are parameters for:

  • 1st position: Format for Positive value
  • 2nd position: Format for Negative value
  • 3rd position: Format for Zero
  • 4th position: Format for Text

The order of the parameters are:

Positive value;Negative value;Zero;Text

You also need to specify how the number will be returned.

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

For instance, if you have the number 123

  • The code #### will return 123
  • With the code 0000 the display will be 0123
  • And with the code ###0.00 the display will be 123.00

Code to return negative number in parenthesis

So with this information, we can easily create a format for negative numbers between parentheses.

  • For the positive number: ###0.00
  • For the negative number in parentheses: (###0.00)

The code you need to enter in the "Type" field is:

#,##0.00;(#,##0.00)

So you write this code in the Custom field of the Format Number

Custom format to display negative numbers in parenthesis

The display of negative numbers is now 😀😎😍

Negative numbers in parenthesis

And if you also want to add the color red for those numbers, you just add the color setting in to code between bracket.

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

Negative numbers in red with parenthesis

Start Excel with your custom parameters

You can save your customs format number in a template and load then when you open a new workbook. The method is explain in this article.

Excel starts with your custom parameters

Related posts


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

5 comments

Skip to comment form

    • EUGENIO BULLA on 26/02/2020 at 23:50
    • Reply

    In all new excel we will need to create this code?
    Is there any way to save this code permanently in my Excel 2016 costum options?

    1. Hi Eugenio,
      Yes there is a solution. I write the article today and I will paste the link here

    2. Hi Eugenio,
      You can find how to load your custom parameters in this article https://www.excel-exercise.com/start-excel-with-your-custom-parameters/

    • DIAMOND KARIM on 15/11/2019 at 23:41
    • Reply

    Once we put ( ) in negative number sign how can we save it so is permanent. Thank you.

    1. You mean for all your workbook when you create a new one ?

Leave a Reply

Your email address will not be published.