Please could some helpful person tell me how to get negative numbers in brackets (ie (0.00) rather than as -0.00) in excel.
Thanks.
Replies (16)
Please login or register to join the discussion.
Regional Settings
In Office 2007:
Go to Control Panel, Click Clock, Language and Region
Click Regional and Language Options
The window will show how your numbers, dates etc are currently formatted. If you click the box Customise this format it will take you to all the options available. Half way down you will see Negative number format. You can change it there to a bracketed format.
I am using Vista
And the Control Panel is accessed from the Windows button on the bottom left corner of the desktop. I am afraid I can't help other than that. Sorry.
Here's another way
Click on the toolbar "Number" and you get a format dialogue box.
Type (or copy and paste from this posting) the following:
##,##0.00_-;(##,##0.00);-_;
This method will only affect Excel, the Control Panel method I expect will affect other programs too.
Thank you from me too
I have just used DMGbus's method and it works! Thank you to DMGbus and to Taxhound for asking. I had given up trying.
Kind regards
Shirley
If there is any prospect that you might wish to share your workb
... then I recommend hardcoding the brackets in the custom number format by enclosing them within quotes.
Thus, in the negative number element of the custom number format (ie after the first semicolon), something like
"("#,##0.00")"
is preferable to
(#,##0.00)
This is because between them, Windows OS and MS Office tries to be a bit too clever sometimes in trying to translate the formats when the file is being opened on machines with different international settings and/or different versions of Excel. I have found that by correctly setting my international settings to show negatives in brackets, and despite custom number formatting negatives also with brackets (without the quotes) when it goes to another machine, possibly with a different version of Excel, or possibly with international settings setup differently, the negative numbers revert to being preceded with an "-". If you hard code the brackets into the format as described above it will override all defaults. At least, that is what I have found.
With kind regards
Clint Westwood
Sorry - should have mentioned this earlier
The bracketed negative number formatting issue is one of the most common accounting queries about Excel and has been covered by Simon Hurst in several expert guides.
See the ExcelZone Compendium formatting tips digest for more tips and tricks.
Negative number in brackets
And if you would like the negative number in red
#,##0.00_);[Red](#,##0.00)
Negative numbers in Excel
The easiest way I have found to produce negative numbers in brackets is as follows
Highlight the whole spreadsheet
Format all cells to "Accounting" with 2 decimal spaces and no "£" sign
Then conditional format the whole sheet so that any number less than zero has a red font.
Brackets in excel
-Select the cells you need to format
-from the format tab, select format cells
-click on the number tab and select custom
-the format you need is #,##0.00,(#.##0.00)
-you can either type this in from scratch, or click on another format, such as the one using the mnus sign, and then edit, and click OK
-- John E Wild FCCA
John
"the format you need is #,##0.00,(#.##0.00)"
It should be #,##0.00_);(#.##0.00)
you need the ";" and you also need the "_)" otherwise the numbers are not alligned consistently
Currency option
I just use the currency option with the symbol set to None. This gives you the choice of black or red and minus sign or brackets.
Brackets in excel office 365
any ideas how this type of formatting is achieved? also "-" if there is zero.