Displaying Negative Numbers in Parentheses – Excel

Reading time: 2 minutes

Post Views: 260,480

Last Updated on 28/02/2021 by Frédéric LE GUEN

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

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 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.

Or by clicking on this icon in the ribbon

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 😀😎😍

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

Conditional Formatting

Format number,negative number,negative number in parenthesis

Frédéric LE GUEN

15 Comments

Michelle Low
14/09/2021 @ 02:55

Thanks !! This solved the problem !

Reply
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
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
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
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
Abdul Muhaimin bin Mohd Zamri
10/11/2020 @ 11:36

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

Reply
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
- Frédéric LE GUEN
  27/02/2020 @ 09:59
  
  Hi Eugenio,
  Yes there is a solution. I write the article today and I will paste the link here
  
  Reply
- Frédéric LE GUEN
  01/03/2020 @ 16:23
  
  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/
  
  Reply
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

Displaying Negative Numbers in Parentheses – Excel

Reading time: 2 minutes

Post Views: 260,480

Last Updated on 28/02/2021 by Frédéric LE GUEN

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

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 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.

Or by clicking on this icon in the ribbon

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

The display of negative numbers is now 😀😎😍

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)

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.

Conditional Formatting

Format number,negative number,negative number in parenthesis

Frédéric LE GUEN

15 Comments

Michelle Low
14/09/2021 @ 02:55

Thanks !! This solved the problem !

Reply
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
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
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
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
Abdul Muhaimin bin Mohd Zamri
10/11/2020 @ 11:36

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

Reply
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
- Frédéric LE GUEN
  27/02/2020 @ 09:59
  
  Hi Eugenio,
  Yes there is a solution. I write the article today and I will paste the link here
  
  Reply
- Frédéric LE GUEN
  01/03/2020 @ 16:23
  
  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/
  
  Reply
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

Most Viewed Posts

Displaying Negative Numbers in Parentheses – Excel

Negative numbers in Excel

Customize your number format

Code to customize numbers in Excel

Code to return negative number in parenthesis

Start Excel with your custom parameters

15 Comments

Leave a Reply Cancel reply

Recent Posts

Categories

Displaying Negative Numbers in Parentheses – Excel

Negative numbers in Excel

Customize your number format

Code to customize numbers in Excel

Code to return negative number in parenthesis

Start Excel with your custom parameters

15 Comments

Leave a Reply Cancel reply