Negative Numbers in Brackets - HELP!

[Guest]

Hi,

I have read every questions and replies on this site in order to get my
negative numbers to show in brackets and nothing works. I am desparate. The
regional settings are changed to indicate negative numbers in brackets but
when I try formatting the cells in Excel, I cannot get the numbers to show in
brackets.

I have Windows XP, Office 2003.

Please help me...

Thanks.

 

[Peo Sjoblom]

Are they seen as numbers at all? Widen the cell, don't force any aligment,
are the numbers left or right aligned, if they are left they are seen as
text
you can also try

=ISTEXT(cell)

if it returns TRUE they are text, if so try copy an empty cell, select the
numbers
and do paste special and select add.

 

[Guest]

Thank you for your reply. They are seen as numbers, I have not forced any
alignment and they are right-aligned, by default.

I have seen multiple suggestions on this site, I have tried them all, except
for the "unregister/register", etc.

Could it be a bug?

 

[Peo Sjoblom]

Put -10 in a cell, select the cell, do format>cells>number>custom and use

General;(General)

what happens?

 

[Guest]

Hi Helene,

A couple of questions:

(a) I take it that right now negative numbers show up as if they were
positive, e.g., minus 35 shows up on your worksheet as "35." Correct?

(b) In Control Panel did you change the regional setting for numbers, for
currency, or for both?

Regards,

Batara

 

[Gordon]

Hi,

I have read every questions and replies on this site in order to get my
negative numbers to show in brackets and nothing works. I am desparate. The
regional settings are changed to indicate negative numbers in brackets but
when I try formatting the cells in Excel, I cannot get the numbers to show in
brackets.

I have Windows XP, Office 2003.

Please help me...

Thanks.

Excel does NOT use the windows regional setting! (ANOTHER example of MS
not doing joined-up thinking - Lotus DOES however, but that's not an MS
product is it?). You have to use Custom Formatting.

 

[Guest]

Batara,

a) No the number shows as -10.
b) I changed both.

;0(

 

[Guest]

I did as suggested and nothing happens, i.e., the format stays at -10.

 

[Guest]

Oh! Well, this is the first time I read this although, in this case, it
makes perfect sense... However, many times, on this site, the suggestion to
change the Windows settings was made. Interesting...

Well, in any case, I cannot find a way to force the custom formatting to
display negative numbers in brackets (no decimals, simple numbers).

Hélène

 

[Guest]

Gordon, it seems that you were right after all. Windows settings have
nothing to do with this. Here's the solution: Go to Format, Style, Number
and apply this format: _ * # ##0_);_ * (# ##0);_ * "- "_);_ @_ .

Thank you everyone for all your suggestions.

Hélène

 

[Gord Dibben]

Helene

Gordon is not correct.

Windows Regional Settings control Excel's default number formatting.

Make the changes in Regional Settings to bracketed numbers and currency.

Excel must be closed and re-opened for changes to be seen in Excel.


Gord Dibben Excel MVP

 

[Gordon]

Helene

Gordon is not correct.

Windows Regional Settings control Excel's default number formatting.

Well it's *NEVER* worked for me in *ANY* version of Excel on *ANY*
machine I've *EVER* worked on, and that goes back to Office 95 on
Windows 95 right through to Office 2002 on XP SP2. And I use Excel
extensively every day in every job I've had for the last 10 years.

 

[Gordon]

Well it's *NEVER* worked for me in *ANY* version of Excel on *ANY*
machine I've *EVER* worked on, and that goes back to Office 95 on
Windows 95 right through to Office 2002 on XP SP2. And I use Excel
extensively every day in every job I've had for the last 10 years.

And I've never known it work for any of the hundreds of colleagues I've
worked with over those ten years either. EVERYONE has ALWAYS had to use
custom formats.

 

[Guest]

1234Hi all;

On my Windows XP Home:
Start> Control Panel> Regional and Language Options> Regional Options
"English (United States)> Customize>
Numbers> Negative number format: -1.1
Currency> Negative currency format: ($1.1)
I switch to Excel 2003.
Enter -10 in a cell, it shows as -10. Change the format of that cell to
currency, it shows ($10.00). Both happen to match the Windows regional
settings. Is that a coincidence? I'm not curious enough to change those, as
they suit me for a default, but I've never had a problem formatting a
negative number with brackets, and without going all the way to a Format>
Cells> Number> Custom.
Format> Cells> Number> Number> Negative numbers
shows -1,234.10 in black, then the same in red, then (1,234.10) in black,
followed by (1,234.10) in red. This matches my admittedly poor memory of
previous versions of Excel. Choosing the black with brackets and reducing the
decimal places to zero shows the format preview (1,234), and results in (10)
displayed in the cell.
Going to Format> Cells> Number> Custom
on that cell now shows: #,##0_);(#,##0)
a far cry from: _ * # ##0_);_ * (# ##0);_ * "- "_);_ @_ .
I am real curious as to why it was not that simple for Hélène. I don't
suppose anyone knows?

Regards,
Ian.

 

[Gord Dibben]

Ian

Not a coincidence.


Gord(not Gordon)

1234Hi all;

On my Windows XP Home:
Start> Control Panel> Regional and Language Options> Regional Options
"English (United States)> Customize>
Numbers> Negative number format: -1.1
Currency> Negative currency format: ($1.1)
I switch to Excel 2003.
Enter -10 in a cell, it shows as -10. Change the format of that cell to
currency, it shows ($10.00). Both happen to match the Windows regional
settings. Is that a coincidence? I'm not curious enough to change those, as
they suit me for a default, but I've never had a problem formatting a
negative number with brackets, and without going all the way to a Format>
Cells> Number> Custom.
Format> Cells> Number> Number> Negative numbers
shows -1,234.10 in black, then the same in red, then (1,234.10) in black,
followed by (1,234.10) in red. This matches my admittedly poor memory of
previous versions of Excel. Choosing the black with brackets and reducing the
decimal places to zero shows the format preview (1,234), and results in (10)
displayed in the cell.
Going to Format> Cells> Number> Custom
on that cell now shows: #,##0_);(#,##0)
a far cry from: _ * # ##0_);_ * (# ##0);_ * "- "_);_ @_ .
I am real curious as to why it was not that simple for Hélène. I don't
suppose anyone knows?

Regards,
Ian.

 

[Gordon]

1234Hi all;

On my Windows XP Home:
Start> Control Panel> Regional and Language Options> Regional Options
"English (United States)> Customize>
Numbers> Negative number format: -1.1
Currency> Negative currency format: ($1.1)
I switch to Excel 2003.
Enter -10 in a cell, it shows as -10. Change the format of that cell to
currency, it shows ($10.00).

In my version of Excel 2002, when you Format cells to currency, you have
to choose a format! Either -£ or (£) - there's no "generic" format for
currency. And an ordinary negative number, ie NOT currency STILL DOES
NOT USE THE REGIONAL SETTINGS! IT NEVER HAS!

 

[Dave Peterson]

Close excel

Change that windows regional setting|currency tab|Negative currency format
to show ($1.1)

Open excel

If you click on Format|cells|Number tab
then choose Number, do your formatting options include ()'s for negative
numbers.

They do for me.

Close excel

Change that windows regional setting|currency tab|Negative currency format
to show -$1.1

Open excel

If you click on Format|cells|Number tab
then choose Number, do your formatting options include ()'s for negative
numbers.

They do not for me.

 

[Gord Dibben]

I'm staying the hell out of this one<g>

Don't wish to upset the other Gordon and his hundreds of colleagues.


Gord

 

[Gordon]

Close excel

Change that windows regional setting|currency tab|Negative currency format
to show ($1.1)

Open excel

If you click on Format|cells|Number tab
then choose Number, do your formatting options include ()'s for negative
numbers.

They do for me.

They do. That's NOT the point I was making. the point I was making, is
that in Lotus, if Regional Settings are set to (1.1), then when you
enter -1.1 in a cell, it is AUTOMATICALLY formatted to (1.1) - you don't
have to DO anything!

 

[Gordon]

I'm staying the hell out of this one<g>

Don't wish to upset the other Gordon and his hundreds of colleagues.


Gord

I think we are talking at cross-purposes here! Yes, if regional settings
are set to (1.1) then in Format Cells-Numbers then yes there is a format
(1.1). My point was, that in Lotus, if Regional settings are set to
(1.1) and you enter -1.1 in a cell, then Lotus AUTOMATICALLY formats it
to (1.1) - you don't have to DO anything. Excel has NEVER done that.