Share this content

How to display negative numbers in excel in brackets

How to display negative numbers in excel in...

Didn't find your answer?

Click on "Format"

Then click on "Cells"

Then click on "custom"

Now near the bottom of the list you should see:


Now click on this one and it should appear in the type box. You need to do the following changes to it:


and then click OK

You will now be able to display a negative number in brackets

Also this is not listed in Excel's help section which is pretty useless regarding this
Anil sanger

Replies (15)

Please login or register to join the discussion.

Simon Hurst
By Simon Hurst
19th May 2009 14:53

Number formatting 2007 update
Thanks Lee - 2007 updated article is here:

Thanks (0)
By Anonymous
19th May 2009 14:01

The syntax to get required format

The above is the syntax that I use to achieve the following :-

1. No floating decimal points
2. Negative numbers in brackets
3. Zero values as a dash (-)
4. Commas placed as in 1,000.00
5. A neat right alignment in a column of figures that includes both negative and positive values - all decimal points aligned

Finally go to format, alignment, shrink to fit to be rid of an illegible on screen or ion print figure where the ciolumn is too narrow accrding to Excel.

As an alternative to shrink to fit double click in right-hand top column boundary line to get column width wide enough for the figures contained in that column.

Thanks (7)
By leestevens
19th May 2009 10:14

Excelzone compendium: Formatting
I think Simon Hurst covered some of this in this article

I think he subsequently updated the article for Office 2007 last year.

Useful info none the less

Thanks (0)
By nigelredhead
19th May 2009 09:23

Also zeros
In some cases I also add ;"-" to the end so that if the number is zero then a dash is displayed. Not always useful but can be in some instances. You can add spaces at after the dash to align.


Thanks (0)
By pauljgoodman
19th May 2009 09:02

negative numbers in excel
I might add that if you add _) to the end of the first item, ie #,##0_), then the positive and negative numbers align properly underneath each other. Without this, a column of figures has a raggedy look to it.

Thanks (1)
By AnonymousUser
19th May 2009 09:00

I should mention ...
... that I have in the past found the advice in the OP to be unreliable. It could be a version-of-Excel thing, but in some cases I have found that if I format a negative number as, say
on a machine whose International Settings in the operating system are already set to display negative numbers in that format by default, and then open the same spreadsheet on another computer whose International Settings have been set to display negative numbers in an alternative format, such as preceeded by a minus sign, then the international settings seem to override the custom format defined by the creator of the spreadsheet.
To get around that I define negative numbers as
and this forces the custom format to override the international settings.

Another point that the OP might have missed is that you would want to format the positive numbers as
otherwise the decimal point will not align vertically in a column of numbers that contain both positives and negatives. Small matter but it tends to look irritating after a while.

Thanks (0)
By Anonymous
19th May 2009 08:42

Tip - format painter (brush icon) + shrink to fit
Having achieved this very useful number formatting (no thanks to poorly programmed Excel help and not even being one of the pre-set ready made formats) there are two further things to consider:-

1. Save the format for future use to avoid the inconvenience of having to type in the complicated syntax required. This can be done by saving on your desktop a file "Excel new" or "Excel blank" - an Excel spreadsheet ready made to use with this useful number format already applied. To copy the format to another cell in the same or a different workbook use the format painter - the small paintbrush icon.

2. A further advisable formatting matter - essential in my opinion for all number and dates cells is to format such cells as "shrink to fit" to stop the rubbishy "###.##" display and print outs that sometimes would otherwise occur in Excel.

Thanks (1)
Flag of the Soviet Union
By thevaliant
26th May 2017 11:27

Why is an eight year old thread 'trending'? Or is it just me?

(It'll be even more trending now)

Thanks (4)
Replying to thevaliant:
By Ruddles
26th May 2017 12:08

Excel formatting is the new ripped jeans - trendy several years ago and making a comeback

Thanks (0)
Replying to Ruddles:
Danny Kent
By Viciuno
26th May 2017 13:10

Not too sure when this happened but I noticed the other day when wondering if that other thread with like 150 comments about the VAT registration had reached the most read list...

If you sort by most liked instead of by most recent in any answers all the previous threads that were complaining about the new website have disappeared off the top of the rankings.

i would imagine it has something to do with that.

Thanks (1)
By Jaccames
27th Jun 2017 06:46

Thank you- have wondered this for a while since I lost a macro!

Thanks (0)
Simon Hurst
By Simon Hurst
11th Jul 2017 18:31

As some of the quoted links don't seem to be working, here is the latest update of the number formatting guide:

Thanks (0)
By dul50n
08th Sep 2017 10:14


If you're using Windows you could just:

Go to control panel,
Clock, language and region,
Change the date, time, or number format,
Additional settings under the formats tab,
Pick brackets for the negative number format and apply,

Then you can ditch the custom format in Excel use the accounting format with or without a currency symbol and you have brackets, thousand comma separators and they all line up in the column.

As this is a system setting that's being changed you might need a re-boot before it takes full effect.

Thanks (0)
By aroundthecorner
02nd Jun 2018 14:15

Thanks for this - which is a winner for me. I pasted #,##0;[Black](#,##0) into the custom type box. Live long and prosper

Thanks (0)
By arisenrain
29th Jan 2019 01:51

I do network administrative work for many types of businesses. Accountants always seem to ask me this question so I created a video with steps to change it with Windows 10.

None of the above was what I was looking for. This works for Windows 7 and Windows 10.

Thanks (0)
Share this content