Excel 2000

[Guest]

Hi - I use Excel 2000 and wish to display negative number, accounting and
currency values in parentheses. However in the Format Cell function in Excel
there is no option to use parentheses. The only option is to use the -ve
symbol or to display the value in red. How do I display the value in
parentheses.

 

[Roger Govier]

Hi Mike

Try
Format>Cells>Number>Custom
$#,##0.00;[Red]($#,##0.00)

Regards

Roger Govier

 

[Gord Dibben]

Mike

If the brackets option is not available
underFormat>Cell>Number................

This is a function of your Regional Settings in Windows OS.

Start>Settings>Control Panel>Regional and Language Settings>Regional
Options>Customize>Currency>Negative Currency Format. Click on drop-down arrow
and select the ($1.1)

Note: this is under the "Currency" tab in Regional Options. Changing under
the "Numbers" tab only does not do the trick.

Apply and OK>Apply your way out.

You will have to close Excel then re-open to see effects.


Gord Dibben Excel MVP

Hi Mike

Try
Format>Cells>Number>Custom
$#,##0.00;[Red]($#,##0.00)

Regards

Roger Govier

Hi - I use Excel 2000 and wish to display negative number, accounting and
currency values in parentheses. However in the Format Cell function in Excel
there is no option to use parentheses. The only option is to use the -ve
symbol or to display the value in red. How do I display the value in
parentheses.

 

[Roger Govier]

Hi Gord
Is this different in XL2000 to XL2003?
On my copy of XL2003, I am set for -1.1 on my Regional settings, not (1.1)
and the format I posted gives me Red (£23.45) (I posted $ for the OP).

Regards

Roger Govier

Mike

If the brackets option is not available
underFormat>Cell>Number................

This is a function of your Regional Settings in Windows OS.

Start>Settings>Control Panel>Regional and Language Settings>Regional
Options>Customize>Currency>Negative Currency Format. Click on drop-down arrow
and select the ($1.1)

Note: this is under the "Currency" tab in Regional Options. Changing under
the "Numbers" tab only does not do the trick.

Apply and OK>Apply your way out.

You will have to close Excel then re-open to see effects.


Gord Dibben Excel MVP

Hi Mike

Try
Format>Cells>Number>Custom
$#,##0.00;[Red]($#,##0.00)

Regards

Roger Govier

Hi - I use Excel 2000 and wish to display negative number, accounting and
currency values in parentheses. However in the Format Cell function in Excel
there is no option to use parentheses. The only option is to use the -ve
symbol or to display the value in red. How do I display the value in
parentheses.

 

[Dave Peterson]

Gord was pointing out that if that () format isn't available under the Number
Category, there is a way to change a windows setting so that it will be.

Hi Gord
Is this different in XL2000 to XL2003?
On my copy of XL2003, I am set for -1.1 on my Regional settings, not (1.1)
and the format I posted gives me Red (£23.45) (I posted $ for the OP).

Regards

Roger Govier

Mike

If the brackets option is not available
underFormat>Cell>Number................

This is a function of your Regional Settings in Windows OS.

Start>Settings>Control Panel>Regional and Language Settings>Regional
Options>Customize>Currency>Negative Currency Format. Click on drop-down arrow
and select the ($1.1)

Note: this is under the "Currency" tab in Regional Options. Changing under
the "Numbers" tab only does not do the trick.

Apply and OK>Apply your way out.

You will have to close Excel then re-open to see effects.


Gord Dibben Excel MVP

Hi Mike

Try
Format>Cells>Number>Custom
$#,##0.00;[Red]($#,##0.00)

Regards

Roger Govier


Mike wrote:

Hi - I use Excel 2000 and wish to display negative number, accounting and
currency values in parentheses. However in the Format Cell function in Excel
there is no option to use parentheses. The only option is to use the -ve
symbol or to display the value in red. How do I display the value in
parentheses.

 

[Gord Dibben]

Roger

The format you posted will work as a Custom Format, but if that format is not
available from the Format>Cells>Number>Number or Currency "negative numbers"
option dialog as OP stated then the Regional Settings trick will place it
there.

Do you have the format available in your Numbers "negative numbers" dialog in
a New workbook?

A correction to my original post.....you do not have to close and re-open
Excel for changes to be in effect.


Gord

Hi Gord
Is this different in XL2000 to XL2003?
On my copy of XL2003, I am set for -1.1 on my Regional settings, not (1.1)
and the format I posted gives me Red (£23.45) (I posted $ for the OP).

Regards

Roger Govier

Mike

If the brackets option is not available
underFormat>Cell>Number................

This is a function of your Regional Settings in Windows OS.

Start>Settings>Control Panel>Regional and Language Settings>Regional
Options>Customize>Currency>Negative Currency Format. Click on drop-down arrow
and select the ($1.1)

Note: this is under the "Currency" tab in Regional Options. Changing under
the "Numbers" tab only does not do the trick.

Apply and OK>Apply your way out.

You will have to close Excel then re-open to see effects.


Gord Dibben Excel MVP

Hi Mike

Try
Format>Cells>Number>Custom
$#,##0.00;[Red]($#,##0.00)

Regards

Roger Govier


Mike wrote:

Hi - I use Excel 2000 and wish to display negative number, accounting and
currency values in parentheses. However in the Format Cell function in Excel
there is no option to use parentheses. The only option is to use the -ve
symbol or to display the value in red. How do I display the value in
parentheses.

 

[Roger Govier]

Hi Gord

In a new workbook, under Negative format for Numbers all I see is
-1234.10 black
1234.10 red
-1234.10 black
-1234.10 red
Same for currency with the addition of the £ symbol and comma separator for
thousands.

If I change my Regional settings, to (1.1) then I still see exactly the
same, even after closing and restarting Excel.

Windows XP Prof, Excel 2003

Regards

Roger Govier

Roger

The format you posted will work as a Custom Format, but if that format is not
available from the Format>Cells>Number>Number or Currency "negative numbers"
option dialog as OP stated then the Regional Settings trick will place it
there.

Do you have the format available in your Numbers "negative numbers" dialog in
a New workbook?

A correction to my original post.....you do not have to close and re-open
Excel for changes to be in effect.


Gord

Hi Gord
Is this different in XL2000 to XL2003?
On my copy of XL2003, I am set for -1.1 on my Regional settings, not (1.1)
and the format I posted gives me Red (£23.45) (I posted $ for the OP).

Regards

Roger Govier

Mike

If the brackets option is not available
underFormat>Cell>Number................

This is a function of your Regional Settings in Windows OS.

Start>Settings>Control Panel>Regional and Language Settings>Regional
Options>Customize>Currency>Negative Currency Format. Click on drop-down arrow
and select the ($1.1)

Note: this is under the "Currency" tab in Regional Options. Changing under
the "Numbers" tab only does not do the trick.

Apply and OK>Apply your way out.

You will have to close Excel then re-open to see effects.


Gord Dibben Excel MVP

On Tue, 22 Nov 2005 12:28:06 +0000, Roger Govier



Hi Mike

Try
Format>Cells>Number>Custom
$#,##0.00;[Red]($#,##0.00)

Regards

Roger Govier


Mike wrote:


Hi - I use Excel 2000 and wish to display negative number, accounting and
currency values in parentheses. However in the Format Cell function in Excel
there is no option to use parentheses. The only option is to use the -ve
symbol or to display the value in red. How do I display the value in
parentheses.

 

[Gord Dibben]

Roger

The (1.1) is entered under the Negative Currency dialog of the Currency Tab in
Regional Settings>Customize.

NOT under the Numbers Tab in Regional Settings>Customize.

You may have to close and re-open Excel. I don't.


Gord

Hi Gord

In a new workbook, under Negative format for Numbers all I see is
-1234.10 black
1234.10 red
-1234.10 black
-1234.10 red
Same for currency with the addition of the £ symbol and comma separator for
thousands.

If I change my Regional settings, to (1.1) then I still see exactly the
same, even after closing and restarting Excel.

Windows XP Prof, Excel 2003

Regards

Roger Govier

Roger

The format you posted will work as a Custom Format, but if that format is not
available from the Format>Cells>Number>Number or Currency "negative numbers"
option dialog as OP stated then the Regional Settings trick will place it
there.

Do you have the format available in your Numbers "negative numbers" dialog in
a New workbook?

A correction to my original post.....you do not have to close and re-open
Excel for changes to be in effect.


Gord

Hi Gord
Is this different in XL2000 to XL2003?
On my copy of XL2003, I am set for -1.1 on my Regional settings, not (1.1)
and the format I posted gives me Red (£23.45) (I posted $ for the OP).

Regards

Roger Govier


Gord Dibben wrote:

Mike

If the brackets option is not available
underFormat>Cell>Number................

This is a function of your Regional Settings in Windows OS.

Start>Settings>Control Panel>Regional and Language Settings>Regional
Options>Customize>Currency>Negative Currency Format. Click on drop-down arrow
and select the ($1.1)

Note: this is under the "Currency" tab in Regional Options. Changing under
the "Numbers" tab only does not do the trick.

Apply and OK>Apply your way out.

You will have to close Excel then re-open to see effects.


Gord Dibben Excel MVP

On Tue, 22 Nov 2005 12:28:06 +0000, Roger Govier



Hi Mike

Try
Format>Cells>Number>Custom
$#,##0.00;[Red]($#,##0.00)

Regards

Roger Govier


Mike wrote:


Hi - I use Excel 2000 and wish to display negative number, accounting and
currency values in parentheses. However in the Format Cell function in Excel
there is no option to use parentheses. The only option is to use the -ve
symbol or to display the value in red. How do I display the value in
parentheses.

 

[Roger Govier]

Hi Gord

It does help if I read the instructions doesn't it!!!!
You said quite clearly the Currency tab in your first posting.
When I changed that, it behaved exactly as you described (without the need
to close to Excel).
Something new learned - as happens every day.
Many thanks.

Strange that it doesn't work with the Number format though, until I also
change the Currency Tab. In fact I just changed it all back to the original
settings, (no brackets), then set Currency to (£1.1) and both Currency and
Number formats show ( ).
One of Excel's quirks I suppose.

Regards

Roger Govier

Roger

The (1.1) is entered under the Negative Currency dialog of the Currency Tab in
Regional Settings>Customize.

NOT under the Numbers Tab in Regional Settings>Customize.

You may have to close and re-open Excel. I don't.


Gord

Hi Gord

In a new workbook, under Negative format for Numbers all I see is
-1234.10 black
1234.10 red
-1234.10 black
-1234.10 red
Same for currency with the addition of the £ symbol and comma separator for
thousands.

If I change my Regional settings, to (1.1) then I still see exactly the
same, even after closing and restarting Excel.

Windows XP Prof, Excel 2003

Regards

Roger Govier

Roger

The format you posted will work as a Custom Format, but if that format is not
available from the Format>Cells>Number>Number or Currency "negative numbers"
option dialog as OP stated then the Regional Settings trick will place it
there.

Do you have the format available in your Numbers "negative numbers" dialog in
a New workbook?

A correction to my original post.....you do not have to close and re-open
Excel for changes to be in effect.


Gord

On Tue, 22 Nov 2005 18:01:16 +0000, Roger Govier



Hi Gord
Is this different in XL2000 to XL2003?
On my copy of XL2003, I am set for -1.1 on my Regional settings, not (1.1)
and the format I posted gives me Red (£23.45) (I posted $ for the OP).

Regards

Roger Govier


Gord Dibben wrote:


Mike

If the brackets option is not available
underFormat>Cell>Number................

This is a function of your Regional Settings in Windows OS.

Start>Settings>Control Panel>Regional and Language Settings>Regional
Options>Customize>Currency>Negative Currency Format. Click on drop-down arrow
and select the ($1.1)

Note: this is under the "Currency" tab in Regional Options. Changing under
the "Numbers" tab only does not do the trick.

Apply and OK>Apply your way out.

You will have to close Excel then re-open to see effects.


Gord Dibben Excel MVP

On Tue, 22 Nov 2005 12:28:06 +0000, Roger Govier




Hi Mike

Try
Format>Cells>Number>Custom
$#,##0.00;[Red]($#,##0.00)

Regards

Roger Govier


Mike wrote:



Hi - I use Excel 2000 and wish to display negative number, accounting and
currency values in parentheses. However in the Format Cell function in Excel
there is no option to use parentheses. The only option is to use the -ve
symbol or to display the value in red. How do I display the value in
parentheses.