Default Number Format for all WEorksheets
 

I use the Format Cells, Custom option to set a number format = #,##0;(#,##0),
in order to format numbers in a spreadsheet.

The Bracketed option for negative numbers is not a preset option available
within Excel.

How can I set this format as a default option for all worksheets/files opened

Default Number Format for all WEorksheets
 

Hi Bill,

Excel has a built in format #,##0_);(#,##0) which does the same thing as
yours except that it forces positive numbers to align properly to the right
side of the cell relative to negative numbers. In Excel this is the 6th
format under Custom.
--
Cheers,
Shane Devenshire


"BillFitz" wrote:

I use the Format Cells, Custom option to set a number format = #,##0;(#,##0),
in order to format numbers in a spreadsheet.

The Bracketed option for negative numbers is not a preset option available
within Excel.

How can I set this format as a default option for all worksheets/files opened

Default Number Format for all WEorksheets
 

Bill

If negative brackets(parens) are not available in the NumberNegative Numbers
dialog.

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.


Gord Dibben Excel MVP

On Sat, 5 Jan 2008 08:12:02 -0800, BillFitz
wrote:

I use the Format Cells, Custom option to set a number format = #,##0;(#,##0),
in order to format numbers in a spreadsheet.

The Bracketed option for negative numbers is not a preset option available
within Excel.

How can I set this format as a default option for all worksheets/files opened

Default Number Format for all WEorksheets
 

Thanks for your response

I tried this, but it does not appear to set this format as a default.

I used the (1.1)option under Number & Currency and used the Apply option.
I also restarted Windows, but still when I input -123, it shows as -123

Anything further required ?

Bill Fitzgerald


"Gord Dibben" wrote:

Bill

If negative brackets(parens) are not available in the NumberNegative Numbers
dialog.

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.


Gord Dibben Excel MVP

On Sat, 5 Jan 2008 08:12:02 -0800, BillFitz
wrote:

I use the Format Cells, Custom option to set a number format = #,##0;(#,##0),
in order to format numbers in a spreadsheet.

The Bracketed option for negative numbers is not a preset option available
within Excel.

How can I set this format as a default option for all worksheets/files opened

Default Number Format for all WEorksheets
 

Sorry, I thought you had no negative parens option so addressed the wrong
problem.

To address the "default" number format, you would have to use a template as your
default workbook/worksheet.

Open a new workbook. Customize cells as you wish with your number format.

FileSave As Type: scroll down to Excel Template(*.XLT) and select. Name your
workbook "BOOK"(no quotes). Excel will add the .XLT to save as BOOK.XLT.

Store this workbook in the XLSTART folder usually located at........

C:\Documents and Settings\username\Application Data\Microsoft\Excel\XLSTART

This will be the default workbook for FileNew or the Toolbar button FileNew or
CTRL + n

WARNING................Do not use FileNew...Blank Workbook or you will get the
Excel default workbook.

NOTE: Existing workbooks are not affected by these settings.

You can also open a new workbook and delete all but one sheet. Customize as
you wish then save this as SHEET.XLT in XLSTART folder also. It now becomes
the default InsertSheet.

More can be found on this in Help under "templates"(no quotes).


Gord

On Sat, 5 Jan 2008 16:33:00 -0800, BillFitz
wrote:

Thanks for your response

I tried this, but it does not appear to set this format as a default.

I used the (1.1)option under Number & Currency and used the Apply option.
I also restarted Windows, but still when I input -123, it shows as -123

Anything further required ?

Bill Fitzgerald


"Gord Dibben" wrote:

Bill

If negative brackets(parens) are not available in the NumberNegative Numbers
dialog.

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.


Gord Dibben Excel MVP

On Sat, 5 Jan 2008 08:12:02 -0800, BillFitz
wrote:

I use the Format Cells, Custom option to set a number format = #,##0;(#,##0),
in order to format numbers in a spreadsheet.

The Bracketed option for negative numbers is not a preset option available
within Excel.

How can I set this format as a default option for all worksheets/files opened

Default Number Format for all WEorksheets
 

Hi,

I guess I'm not sure what you mean by setting the default number format?
The current default number format is General, in which case Excel excepts
positive numbers in 12345676 format and negative numbers in -12345 format.
I'm not sure how creating a default workbook solves this problem. If I
understand the question you want to type in -1234 and have Excel display it
as (1,234.45) and positive numbers as 1,234.45.

You could write VBA to do this if you don't want to pick the 6th format
option under Custom.

You could add the following code to a default workbook, or just the
workbooks you want to handle this way, or create and add-in. In a workbook
you could add general code such as:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If IsNumeric(Target) Then
Target.NumberFormat = "#,##0;(#,##0)"
End If
End Sub

or to manually run it:

Sub MyFormat()
Selection.NumberFormat = "#,##0;(#,##0)"
End Sub

and asign a shortcut key such as Ctrl+e.


--
Cheers,
Shane Devenshire


"Gord Dibben" wrote:

Sorry, I thought you had no negative parens option so addressed the wrong
problem.

To address the "default" number format, you would have to use a template as your
default workbook/worksheet.

Open a new workbook. Customize cells as you wish with your number format.

FileSave As Type: scroll down to Excel Template(*.XLT) and select. Name your
workbook "BOOK"(no quotes). Excel will add the .XLT to save as BOOK.XLT.

Store this workbook in the XLSTART folder usually located at........

C:\Documents and Settings\username\Application Data\Microsoft\Excel\XLSTART

This will be the default workbook for FileNew or the Toolbar button FileNew or
CTRL + n

WARNING................Do not use FileNew...Blank Workbook or you will get the
Excel default workbook.

NOTE: Existing workbooks are not affected by these settings.

You can also open a new workbook and delete all but one sheet. Customize as
you wish then save this as SHEET.XLT in XLSTART folder also. It now becomes
the default InsertSheet.

More can be found on this in Help under "templates"(no quotes).


Gord

On Sat, 5 Jan 2008 16:33:00 -0800, BillFitz
wrote:

Thanks for your response

I tried this, but it does not appear to set this format as a default.

I used the (1.1)option under Number & Currency and used the Apply option.
I also restarted Windows, but still when I input -123, it shows as -123

Anything further required ?

Bill Fitzgerald


"Gord Dibben" wrote:

Bill

If negative brackets(parens) are not available in the NumberNegative Numbers
dialog.

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.


Gord Dibben Excel MVP

On Sat, 5 Jan 2008 08:12:02 -0800, BillFitz
wrote:

I use the Format Cells, Custom option to set a number format = #,##0;(#,##0),
in order to format numbers in a spreadsheet.

The Bracketed option for negative numbers is not a preset option available
within Excel.

How can I set this format as a default option for all worksheets/files opened

Default Number Format for all Worksheets
 

Thank you for your help


Bill Fitzgerald

"Gord Dibben" wrote:

Sorry, I thought you had no negative parens option so addressed the wrong
problem.

To address the "default" number format, you would have to use a template as your
default workbook/worksheet.

Open a new workbook. Customize cells as you wish with your number format.

FileSave As Type: scroll down to Excel Template(*.XLT) and select. Name your
workbook "BOOK"(no quotes). Excel will add the .XLT to save as BOOK.XLT.

Store this workbook in the XLSTART folder usually located at........

C:\Documents and Settings\username\Application Data\Microsoft\Excel\XLSTART

This will be the default workbook for FileNew or the Toolbar button FileNew or
CTRL + n

WARNING................Do not use FileNew...Blank Workbook or you will get the
Excel default workbook.

NOTE: Existing workbooks are not affected by these settings.

You can also open a new workbook and delete all but one sheet. Customize as
you wish then save this as SHEET.XLT in XLSTART folder also. It now becomes
the default InsertSheet.

More can be found on this in Help under "templates"(no quotes).


Gord

On Sat, 5 Jan 2008 16:33:00 -0800, BillFitz
wrote:

Thanks for your response

I tried this, but it does not appear to set this format as a default.

I used the (1.1)option under Number & Currency and used the Apply option.
I also restarted Windows, but still when I input -123, it shows as -123

Anything further required ?

Bill Fitzgerald


"Gord Dibben" wrote:

Bill

If negative brackets(parens) are not available in the NumberNegative Numbers
dialog.

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.


Gord Dibben Excel MVP

On Sat, 5 Jan 2008 08:12:02 -0800, BillFitz
wrote:

I use the Format Cells, Custom option to set a number format = #,##0;(#,##0),
in order to format numbers in a spreadsheet.

The Bracketed option for negative numbers is not a preset option available
within Excel.

How can I set this format as a default option for all worksheets/files opened