Changing Number Format Defaults

tvkrys

Board Regular
Joined
Jan 23, 2006
Messages
135
Hi all -
Is there any way to change the default number format screen? Out of the box, it offers no comma and -black negative numbers; I rarely use anthing other than separating commas and neg numbers in (red). It's annoying to keep changing it everytime I want to format a number. I can always create/assign a custom macro to a button but am running out of screen real estate. So, I'm wondering if there's anyway to just change the default offering.
TIA :), Krys
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

xapie128

Board Regular
Joined
Aug 26, 2006
Messages
82
Is there any way to change the default number format screen?
Yes:
  • Start Excel with a blank workbook
    Click the Select All square (above the 1 for row 1, and left of the A for column A)
    Format the cells like you want
    Select only cell A1 (otherwise, all cells will be selected each time you open a new workbook)
    Do a SaveAs
    In the "Save as type" pull-down, select Template (*.xlt)
    In the "Save in" box at the top, navigate to C:\Program Files\Microsoft Office\Office10\XLStart
    (note: I'm using Excel 2002, your path may differ, but you are looking for the XLStart directory)
    Save the template with file name of Book.xlt
    The next time you start Excel it will use the formatting you want in all cells.
Tip: If one already exists, rename the original Book.xlt to something else so you can return to the default if desired.
 

tvkrys

Board Regular
Joined
Jan 23, 2006
Messages
135
Thanks for the suggestion, xapie128... Unfortunately, that's not quite what I wanted. I found the subdir you indicated and saved the altered file there. However, it does not automtically make that change in the workbook that opens when Excel fires up. I had to open a "New" workbook and pick "Excel.xlt" rather than "Workbook". This solution requires a conscious effort to create new files and would only work on those files that were created that way in the future.
I was hoping for a solution that would effect Excel's choice of defaults so it would work on any file. Does anyone have any other suggestions??? If I can't change the defaults in the Format Window, does anyone know 2000's path/filename of the "Workbook" template that Excel uses as a default so I can apply xapie128's suggestion there???

Thanks, Krys
 

xapie128

Board Regular
Joined
Aug 26, 2006
Messages
82
I had to open a "New" workbook and pick "Excel.xlt" rather than "Workbook".
That part is confusing, did you save it as Book.xlt or as Excel.xlt ?

Book.xlt will contain the defaults, and you shouldn't have to manually open it.
 

tvkrys

Board Regular
Joined
Jan 23, 2006
Messages
135

ADVERTISEMENT

Brain cramp :LOL: Excel 97's default was something like excel8.xlt if I remember correctly and I must have been thinking that when I typed the file name... sorry!!! Does this file also contain my toolbar information or is that kept elsewhere? In otherwords, is this the only file I need to set up my way on another 2000 computer??

Thanks, K-
 

xapie128

Board Regular
Joined
Aug 26, 2006
Messages
82

ADVERTISEMENT

Does this file also contain my toolbar information . . . ?
No, but it contains anything that you change in Tools > Options (as an example if you turn off Row & Column headers, or Gridlines).

Toolbar info is saved in a file named Excel10.xlb and you can copy this from computer to computer.
You will probably find it in \Documents and Settings\XYZ\Application Data\Microsoft\Excel
(where XYZ = the user name)
But it's sometimes easier to just search for "Excel10.xlb"

In your next post you mentioned Personal.xls, note that its "home" is one folder deeper than that above (...\XLSTART which is a different XLSTART directory than in my first post above).

Also note: Personal.xls contains macros that can be accessed from any spreadsheets opened on that computer, but if a speadsheet is copied to another computer none of the macros are copied. But macros can also be associated with an individual .xls file, can be invoked only when that spreadsheet is open, and transfers when the .xls file is copied to another computer. These macros are not part of Personal.xls.
 

tvkrys

Board Regular
Joined
Jan 23, 2006
Messages
135
xapie128

Yes, I was already aware of the where and the how of PERSONAL.XLS... Just wanted you to know that I already knew that if I wanted those macros that that file had to be copied as well.

I've got Win and Excel 2000 and the only excel*.xlb file on my computer is on same level as the XLSTART folder and it has NO number associated with it at all. If I temporarily rename it and then open Excel... my toolbars are gone so that must be it. Is there anything else that is stored in that file besides toolbars? Do you know if I can use it on any other up versions of Excel and/or with XP?

Thanks for your help!!! :biggrin:
K-
 

xapie128

Board Regular
Joined
Aug 26, 2006
Messages
82
I've got Win and Excel 2000 and the only excel*.xlb file on my computer is on same level as the XLSTART folder and it has NO number associated with it at all. If I temporarily rename it and then open Excel... my toolbars are gone so that must be it. Is there anything else that is stored in that file besides toolbars? Do you know if I can use it on any other up versions of Excel and/or with XP?
Yes, that would be the file for Excel 2000. I should have asked what version you have, or just had you search for *.xlb

I don't think anything else is in that file (MS probably used "b" for "bar").

I'm surprised that your toolbars were gone during your test. I thought that if it didn't exist that it would re-create the .xlb file with a default toolbar. But that may be later versions only. Or when you say that your toolbars were gone, do you just mean that the way you had them configured was gone?

To use the .xlb file with other versions, go to the File menu, Open, then in the File Type pull-down select Toolbars (.xlb), then navigate to wherever you put the file. It will import it and do any conversions necessary.

 

tvkrys

Board Regular
Joined
Jan 23, 2006
Messages
135
Yes, there were the standard toolbars were there... just not "my" toolbars. Thanks for all your help! K-
 

Forum statistics

Threads
1,136,909
Messages
5,678,516
Members
419,768
Latest member
eguechi09x

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top