| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel 2000
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. -- Mike from Australia |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Excel 2000
Hi Mike
Try FormatCellsNumberCustom $#,##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. |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Excel 2000
Mike
If the brackets option is not available underFormatCellNumber................ This is a function of your Regional Settings in Windows OS. StartSettingsControl PanelRegional and Language SettingsRegional OptionsCustomizeCurrencyNegative 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 OKApply 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 wrote: Hi Mike Try FormatCellsNumberCustom $#,##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. |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Excel 2000
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 underFormatCellNumber................ This is a function of your Regional Settings in Windows OS. StartSettingsControl PanelRegional and Language SettingsRegional OptionsCustomizeCurrencyNegative 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 OKApply 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 wrote: Hi Mike Try FormatCellsNumberCustom $#,##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. |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Excel 2000
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. Roger Govier wrote: 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 underFormatCellNumber................ This is a function of your Regional Settings in Windows OS. StartSettingsControl PanelRegional and Language SettingsRegional OptionsCustomizeCurrencyNegative 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 OKApply 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 wrote: Hi Mike Try FormatCellsNumberCustom $#,##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. -- Dave Peterson |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Excel 2000
Roger
The format you posted will work as a Custom Format, but if that format is not available from the FormatCellsNumberNumber 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 wrote: 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 underFormatCellNumber................ This is a function of your Regional Settings in Windows OS. StartSettingsControl PanelRegional and Language SettingsRegional OptionsCustomizeCurrencyNegative 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 OKApply 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 wrote: Hi Mike Try FormatCellsNumberCustom $#,##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. |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Excel 2000
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 Gord Dibben wrote: Roger The format you posted will work as a Custom Format, but if that format is not available from the FormatCellsNumberNumber 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 wrote: 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 underFormatCellNumber................ This is a function of your Regional Settings in Windows OS. StartSettingsControl PanelRegional and Language SettingsRegional OptionsCustomizeCurrencyNegative 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 OKApply 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 wrote: Hi Mike Try FormatCellsNumberCustom $#,##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. |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Excel 2000
Roger
The (1.1) is entered under the Negative Currency dialog of the Currency Tab in Regional SettingsCustomize. NOT under the Numbers Tab in Regional SettingsCustomize. You may have to close and re-open Excel. I don't. Gord On Tue, 22 Nov 2005 22:54:36 +0000, Roger Govier wrote: 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 Gord Dibben wrote: Roger The format you posted will work as a Custom Format, but if that format is not available from the FormatCellsNumberNumber 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 wrote: 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 underFormatCellNumber................ This is a function of your Regional Settings in Windows OS. StartSettingsControl PanelRegional and Language SettingsRegional OptionsCustomizeCurrencyNegative 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 OKApply 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 wrote: Hi Mike Try FormatCellsNumberCustom $#,##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. |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Excel 2000
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 Gord Dibben wrote: Roger The (1.1) is entered under the Negative Currency dialog of the Currency Tab in Regional SettingsCustomize. NOT under the Numbers Tab in Regional SettingsCustomize. You may have to close and re-open Excel. I don't. Gord On Tue, 22 Nov 2005 22:54:36 +0000, Roger Govier wrote: 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 Gord Dibben wrote: Roger The format you posted will work as a Custom Format, but if that format is not available from the FormatCellsNumberNumber 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 wrote: 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 underFormatCellNumber................ This is a function of your Regional Settings in Windows OS. StartSettingsControl PanelRegional and Language SettingsRegional OptionsCustomizeCurrencyNegative 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 OKApply 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 wrote: Hi Mike Try FormatCellsNumberCustom $#,##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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Excel 2003 FAILS, but Excel 2000 SUCCEEDS ??? | Excel Discussion (Misc queries) | |||
| Can I lock info in Excel 2003 and users in Excel 2000 enter info? | Excel Discussion (Misc queries) | |||
| Hyperlink in Excel 2000 can't open bookmarked Word 2000 file | Excel Discussion (Misc queries) | |||
| Excel XP 2002 Vs. Excel 2000 | Excel Discussion (Misc queries) | |||
| Excel 2000 file when opened in Excel 2003 generates errors? | Excel Discussion (Misc queries) | |||
Linear Mode
