Displaying Negative Numbers in Parentheses – Excel

Displaying Negative Numbers in Parentheses – Excel
Last Updated on 14/08/2023
Reading time: 2 minutes

This article will show you how to display negative numbers in parentheses.

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 numbers in red.

Menu currency

But for some reports, negative numbers must be displayed with parentheses. 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 offers 4 displays for numbers, in this order

  1. Positive value
  2. Negative value
  3. Zero
  4. Text

Positive value;Negative value;Zero;Text

You also need to specify how the number will be displayed. And there is 2 symbols for the numbers

  • Use 0 to display all the 0 (important to keep the leading 0)
  • Use # to display the numbers except the 0 if not necessary

For instance, if you have the number 123

  • The code #### (4 #) will display 123 in your cell
  • The code 0000 (4 zeros) displays the value 0123
  • And with the code ###0.00 the display will be 123.00 (no leading 0 but 0 for the decimals 😉)

Related articles

Custom Code to return negative number in parentheses

To display negative numbers in parentheses, you will customize the code for negative numbers.

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

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

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

Custom format to display negative numbers in parenthesis

The display of negative numbers is now 😀😎😍

Negative numbers in parenthesis

You can improve the display by adding a the red color for negative numbers with the option [red]

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

Negative numbers in red with parenthesis

16 Comments

  1. DENIS DADSON
    17/07/2023 @ 11:27

    All this is fine but it does not seem to work in windows 11. Previously it was possible to change the negative format in numbers or accounting via the Global options in settings. I cannot find it in the Global settings in W11. I get the use of the custom format but I really do not want to have to resort to that every time I get a negative number, nor do wish to use custom format for all my accounts work, where negatives come up frequently. I am very new to W11 and would appreciate how to dela with this problem. It was so simple in 10 and earlier

    Reply

  2. Michelle Low
    14/09/2021 @ 02:55

    Thanks !! This solved the problem !

    Reply

  3. Saad
    30/08/2021 @ 12:01

    The problem usually is that the standard format defined in the system settings for Negative Numbers is without the (parenthesis). The solution is simple: update the system settings.

    Follow this guide from Microsoft:
    https://support.microsoft.com/en-us/office/negative-numbers-aren-t-showing-with-parentheses-in-excel-682a1cc5-701a-4ce7-92db-cc9eebc5ffa0#:~:text=If%20you're%20using%20Excel,setting%20isn't%20set%20properly.

    Reply

  4. Rob
    26/05/2021 @ 15:40

    Is there a way to save this format in Excel so it can be used again?

    Reply

    • RK
      01/08/2021 @ 04:45

      The format will be auto saved, unless you delete. (For all higher versions of excel after 2007)

      Reply

  5. AndyR
    19/04/2021 @ 14:41

    Is there a way to do this so that the decimal points are all lined up in the column? In your example the negative numbers are slightly out of line because of the close-parenthesis.

    Reply

    • John
      09/05/2021 @ 19:49

      To line up the decimal point you need to put a space before the ";" in which ever number format you are using.

      For example:-

      #,##0.00;[Red]-#.##0.00 (already in Custom format list will become

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

      Hope this helps

      Reply

  6. ARKOM
    23/12/2020 @ 05:20

    Hi Frederic,

    DO you know how to fix if the sample number is not showing ?

    Reply

    • Frédéric LE GUEN
      12/01/2021 @ 08:33

      No

      Reply

  7. Abdul Muhaimin bin Mohd Zamri
    10/11/2020 @ 11:36

    THANKSSSSS, SO HELPFUL SINCE MY EXCEL DIDNT HAVE THAT IN THE NUMBER FORMAT :')

    Reply

  8. EUGENIO BULLA
    26/02/2020 @ 23:50

    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?

    Reply

  9. DIAMOND KARIM
    15/11/2019 @ 23:41

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

    Reply

    • Frédéric LE GUEN
      26/11/2019 @ 15:04

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

      Reply

      • Tawanda
        20/02/2021 @ 18:00

        yes

Leave a Reply

Your email address will not be published. Required fields are marked *

Displaying Negative Numbers in Parentheses – Excel

Reading time: 2 minutes
Last Updated on 14/08/2023

This article will show you how to display negative numbers in parentheses.

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 numbers in red.

Menu currency

But for some reports, negative numbers must be displayed with parentheses. 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 offers 4 displays for numbers, in this order

  1. Positive value
  2. Negative value
  3. Zero
  4. Text

Positive value;Negative value;Zero;Text

You also need to specify how the number will be displayed. And there is 2 symbols for the numbers

  • Use 0 to display all the 0 (important to keep the leading 0)
  • Use # to display the numbers except the 0 if not necessary

For instance, if you have the number 123

  • The code #### (4 #) will display 123 in your cell
  • The code 0000 (4 zeros) displays the value 0123
  • And with the code ###0.00 the display will be 123.00 (no leading 0 but 0 for the decimals 😉)

Related articles

Custom Code to return negative number in parentheses

To display negative numbers in parentheses, you will customize the code for negative numbers.

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

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

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

Custom format to display negative numbers in parenthesis

The display of negative numbers is now 😀😎😍

Negative numbers in parenthesis

You can improve the display by adding a the red color for negative numbers with the option [red]

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

Negative numbers in red with parenthesis

16 Comments

  1. DENIS DADSON
    17/07/2023 @ 11:27

    All this is fine but it does not seem to work in windows 11. Previously it was possible to change the negative format in numbers or accounting via the Global options in settings. I cannot find it in the Global settings in W11. I get the use of the custom format but I really do not want to have to resort to that every time I get a negative number, nor do wish to use custom format for all my accounts work, where negatives come up frequently. I am very new to W11 and would appreciate how to dela with this problem. It was so simple in 10 and earlier

    Reply

  2. Michelle Low
    14/09/2021 @ 02:55

    Thanks !! This solved the problem !

    Reply

  3. Saad
    30/08/2021 @ 12:01

    The problem usually is that the standard format defined in the system settings for Negative Numbers is without the (parenthesis). The solution is simple: update the system settings.

    Follow this guide from Microsoft:
    https://support.microsoft.com/en-us/office/negative-numbers-aren-t-showing-with-parentheses-in-excel-682a1cc5-701a-4ce7-92db-cc9eebc5ffa0#:~:text=If%20you're%20using%20Excel,setting%20isn't%20set%20properly.

    Reply

  4. Rob
    26/05/2021 @ 15:40

    Is there a way to save this format in Excel so it can be used again?

    Reply

    • RK
      01/08/2021 @ 04:45

      The format will be auto saved, unless you delete. (For all higher versions of excel after 2007)

      Reply

  5. AndyR
    19/04/2021 @ 14:41

    Is there a way to do this so that the decimal points are all lined up in the column? In your example the negative numbers are slightly out of line because of the close-parenthesis.

    Reply

    • John
      09/05/2021 @ 19:49

      To line up the decimal point you need to put a space before the ";" in which ever number format you are using.

      For example:-

      #,##0.00;[Red]-#.##0.00 (already in Custom format list will become

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

      Hope this helps

      Reply

  6. ARKOM
    23/12/2020 @ 05:20

    Hi Frederic,

    DO you know how to fix if the sample number is not showing ?

    Reply

    • Frédéric LE GUEN
      12/01/2021 @ 08:33

      No

      Reply

  7. Abdul Muhaimin bin Mohd Zamri
    10/11/2020 @ 11:36

    THANKSSSSS, SO HELPFUL SINCE MY EXCEL DIDNT HAVE THAT IN THE NUMBER FORMAT :')

    Reply

  8. EUGENIO BULLA
    26/02/2020 @ 23:50

    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?

    Reply

  9. DIAMOND KARIM
    15/11/2019 @ 23:41

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

    Reply

    • Frédéric LE GUEN
      26/11/2019 @ 15:04

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

      Reply

      • Tawanda
        20/02/2021 @ 18:00

        yes

Leave a Reply

Your email address will not be published. Required fields are marked *