Excel - Possible to change the default cell format?

Asked By Mike C on 18-Feb-08 02:02 AM
Hello - I am constantly changing the format of my cells (when they are
the number format) to include the comma separator and to use zero
decimal places.

Is there a way to change the settings so that by default the values
will not have decimal places (and by default they will have the comma

Thanks for any suggestions!

(FYI, I considered creating a simple macro to do this, but I believe
that it would limit me from being able to use the UNDO command as
often as I would like).

Gord Dibben replied on 17-Feb-08 11:42 PM
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 Dibben  MS Excel MVP
Klemen25 replied on 20-Feb-08 08:40 PM
Great- I was also looking for something like this- just that I needed
it to change the default colors of my charts that need to be in
company defined colors. (Under tools- options-colors)

Thank you a lot!
Dave Peterson replied on 18-Feb-08 07:13 AM
Remember that dates and times are numbers to excel.

You may find that you do not want to do this if you enter lots of dates/times.


Dave Peterson
Mike C replied on 23-Feb-08 03:24 AM

thanks Dave. Good point
phletz phletzinger replied on 07-Apr-09 05:42 AM

thanks for your suggestions but this solution doesnt seem to work on text files.

So, if I open a text file in excel, it still changes cell values, i.e. skips leading zeroes.

Any more workarounds? ;) Or will someone FINALLY make a spreadsheet program where you can turn off all auto-whatever features. OO-Calc seems to be a perfect clone of excel, including all bugs and nuisances, so there is no point in changing, ditto for Gnumeric,....

All the best

Dick Oz replied on 26-Jul-09 07:37 AM
See the following page http://www.bettersolutions.com/excel/EZH159/VL644333532.htm