Default Number Format for all WEorksheets

[BillFitz]

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

 

[ShaneDevenshire]

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.

 

[Gord Dibben]

Bill

If negative brackets(parens) are not available in the Number>Negative Numbers
dialog.

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.


Gord Dibben Excel MVP

 

[BillFitz]

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]

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.

File>Save 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 File>New or the Toolbar button File>New or
CTRL + n

WARNING................Do not use File>New...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 Insert>Sheet.

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


Gord

 

[ShaneDevenshire]

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.

 

[BillFitz]

Thank you for your help


Bill Fitzgerald

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.

File>Save 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 File>New or the Toolbar button File>New or
CTRL + n

WARNING................Do not use File>New...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 Insert>Sheet.

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


Gord