Blogger
Share this content
0
16
34843

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.

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)

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)

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)

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)

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)

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)

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)

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)

Negative number in brackets

And if you would like the negative number in red

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

Thanks (0)

Or better still

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

Thanks (0)

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)

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)

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)

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)

Brackets in excel office 365

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

Thanks (0)