Share this content
3
284734

How to display negative numbers in excel in brackets

How to display negative numbers in excel in...

Click on "Format"

Then click on "Cells"

Then click on "custom"

Now near the bottom of the list you should see:

#,##0;[Red]#,##0

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

#,##0;[Black](#,##0)

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

Please login or register to join the discussion.

19th May 2009 14:53

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

https://www.accountingweb.co.uk/cgi-bin/item.cgi?id=181668

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

The syntax to get required format
##,##0.00_-;(##,##0.00);-_;

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 (5)
avatar
19th May 2009 10:14

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

https://www.accountingweb.co.uk/cgi-bin/item.cgi?id=156080

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

Useful info none the less

Thanks (0)
avatar
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.

Nigel

Thanks (0)
avatar
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 (0)
avatar
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
(#,##0.00)
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
"("#,##0.00")"
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
#,##0.00_)
not
#,##0.00
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)
avatar
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)
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 (3)
By Ruddles
to thevaliant
26th May 2017 12:08

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

Thanks (0)
By Viciuno
to Ruddles
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 (0)
avatar
27th Jun 2017 06:46

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

Thanks (0)
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:
https://www.accountingweb.co.uk/tech/excel/ez-guide-to-number-formatting

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

Or....

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)
Share this content