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 (15)
Please login or register to join the discussion.
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.
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
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
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.
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.
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.
Why is an eight year old thread 'trending'? Or is it just me?
(It'll be even more trending now)
Excel formatting is the new ripped jeans - trendy several years ago and making a comeback
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.
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
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 for this - which is a winner for me. I pasted #,##0;[Black](#,##0) into the custom type box. Live long and prosper
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.