Blogger
Share this content
0
16
35199

Brackets in excel

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

Please login or register to join the discussion.

By SteveOH
14th May 2011 12:34

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.

Thanks (0)
avatar
14th May 2011 13:18

Thanks for that

But now I feel really silly.  Where is the control panel?  I can't see anything with regional and language options on it?  I am using excel 2010.

Thanks (0)
By SteveOH
14th May 2011 13:57

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.

Thanks (0)
avatar
By DMGbus
14th May 2011 14:24

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.

 

 

 

Thanks (0)
avatar
14th May 2011 14:41

Fab

Thanks you both very much.  Steve - I was looking in excel rather than on the desktop.  Shouldn't be allowed loose with a computer....

Thanks (0)
avatar
14th May 2011 18:39

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

Thanks (0)
avatar
14th May 2011 19:29

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

Thanks (0)
15th May 2011 09:31

This is how I do it

Format Cells

Number

Custom

#,##0.00_);(#,##0.00)

Thanks (0)

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.

Thanks (0)
avatar
By titian
19th May 2011 13:12

Negative number in brackets

And if you would like the negative number in red

#,##0.00_);[Red](#,##0.00)

Thanks (0)
avatar
By Briar
19th May 2011 14:35

Or better still

To display zero numbers as - rather than 0.00, add at the end ";- "

Thanks (0)
avatar
19th May 2011 14:38

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.

 

 

Thanks (0)
avatar
20th May 2011 00:49

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

Thanks (0)
20th May 2011 01:34

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

Thanks (0)
avatar
20th May 2011 09:15

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.

Thanks (0)
avatar
30th Dec 2014 03:42

Brackets in excel office 365

any ideas how this type of formatting is achieved?  also "-" if there is zero.

Thanks (0)