jeh
Blogger
Share this content
0
12
7528

Excel negative number display

to display as brackets rather than a minus sign

I can never see that option on the Excel formats?

Thanks

Replies

Please login or register to join the discussion.

By fpurves
09th Aug 2012 08:47

custom format

you have to use the custom format option for numbers

input #,##0.00_;(###0.00) to get the bracket format

or    #,##0.00_;[Red](###0.00) if you want both brackets and red numbers.

 

Note the

Used to be  a standard in 2003 but dissapeared in 2007 onwards

 

Thanks (1)
avatar
09th Aug 2012 09:00

Adjust your default computer settings

For Windows 7, Go into Control Panel, "Clock, Language and Region" and "Change the date, time or number format". On the Formats tab, select Additional settings. On the Numbers tab, change the negative numbers format to the bracketed version and do the same on the currency tab. (You may need to reboot - I can't remember).

In Excel, the numbers and currency formats will now give you a bracketed option and Accounting will default to bracketed.

 

 

 

 

Thanks (3)
avatar
17th Aug 2012 14:09

Awesome

paulwakefield1 wrote:

For Windows 7, Go into Control Panel, "Clock, Language and Region" and "Change the date, time or number format". On the Formats tab, select Additional settings. On the Numbers tab, change the negative numbers format to the bracketed version and do the same on the currency tab. (You may need to reboot - I can't remember).

In Excel, the numbers and currency formats will now give you a bracketed option and Accounting will default to bracketed.

 

 

 

 

Well worth logging in today

Thanks (0)
avatar
17th Aug 2012 21:15

Glad I read this thread today

paulwakefield1 wrote:

For Windows 7, Go into Control Panel, "Clock, Language and Region" and "Change the date, time or number format". On the Formats tab, select Additional settings. On the Numbers tab, change the negative numbers format to the bracketed version and do the same on the currency tab. (You may need to reboot - I can't remember).

In Excel, the numbers and currency formats will now give you a bracketed option and Accounting will default to bracketed.

This also works in Windows XP - Control Panel - Regional and Language options - Regional Options Tab - then hit the Customise button and change as paulwakefiels1 describes.

I tried it and now Excel 2007 has bracketed negatives as an option in number format, brilliant!

Thanks Paul

Thanks (0)
By SteveOH
09th Aug 2012 10:35

Agree with paulwakefield1

The only thing with changing the custom settings that was outlined by fpurves above, is that it will only change the format for that particular spreadsheet. If you change the default settings via the Control Panel, all your spreadsheets will then have bracketed minus figures.

Thanks (0)
09th Aug 2012 16:11

Non-currency numbers

SteveOH wrote:

The only thing with changing the custom settings that was outlined by fpurves above, is that it will only change the format for that particular spreadsheet. If you change the default settings via the Control Panel, all your spreadsheets will then have bracketed minus figures.

I also have non-currency numbers - eg.cheque numbers and dates. Would these be changed if you use the control panel?

Thanks (0)
avatar
By BKD
09th Aug 2012 10:44

Slight correction to the first response

There's a missing closing bracket - should read #,##0.00);........

Also, further to Steve's comment, first suggestion affects only those cells selected, so possible to have only part of a sheet so formatted.

Thanks (0)
avatar
By jeh
09th Aug 2012 12:46

Fantastic thank you

Thanks (0)
avatar
09th Aug 2012 17:13

Peter

Only if they are negative (Unlikely?)

Thanks (0)
By SteveOH
10th Aug 2012 10:59

@Peter

Further to Richard's comment, if you did happen to have a negative non-currency number then you could format that particular spreadsheet cell as "text".

Thanks (0)

Return of an old favourite

Thanks for raising this query jeh - it's probably the most frequently asked question about Excel on the site.

Our contributor Simon Hurst has tackled the issue several times over, and it forms part of his most recent Excel number formatting FAQs article.

If you follow the link, you should pick up a few more formatting tips (eg dealing with zero values), and there are further links to the full archive of ExcelZone numbering and formatting tutorials.

I hope you enjoy them!

Thanks (1)
18th Aug 2012 10:10

Does anyone know . . .

Does anyone know how these negative numbers would display in Excel if the spreadsheet is (say) forwarded to a client and opened by him on his computer?

My guess would be that if the brackets are being shown on my computer as a consequence of a change made via Control Panel then on the client's computer negative numbers will not have brackets, whereas if the brackets are being shown on my computer as a consequence of a custom format option in the Excel spreadsheet then those brackets will appear on the client's screen when he looks at the spreadsheet.

Am I right about this?

The point is that I do email spreadsheets to clients and I would like the client to see what I see.

An option for me is to convert the spreadsheet to a PDF before emailing it over - but that of course means that the client cannot amend or update the figures.

David

Thanks (0)