Custom formatting in Excel

When you create a custom format, those formats are only available in the workbook in which they were created. There are several ways you can make a custom format available to other workbooks.

In the workbook that contains the custom formats (the source), you can copy a value that has the custom format applied to it to another workbook (the destination). The custom format is added to the destination workbook's list of custom formats. This is one of the easiest ways to share a custom format with an existing workbook. Another option is to create a style for the custom formats and then share the style with another workbook. The disadvantage to this approach is that every time you want to use the custom formats in a new workbook, you have to open a file that contains the formats (the source workbook) and copy the styles to the destination workbook. This is inconvenient to say the least.

Likewise, the first time you store a macro or VBA procedure in the Personal.xls file, the file is created and opened each time you open Excel. You could conceivably open the Personal.xls file and create the custom number formats there--thus making them available to all workbooks you open. The difficulty here is that in order for the Personal.xls file to exist, you have to create and store a macro/VBA procedure in it.

Another alternative is to use Autotemplates. What is an autotemplate? Glad you asked! When you create a new workbook or insert a new worksheet, the settings for the book or sheet come from the autotemplates. So if you want to adjust some of Excel's settings (like the default print header or custom number formats), you can do so in the autotemplates. Every time you create a new book or insert a new sheet, the custom settings you defined are there. In the case of custom numbers, since they are associated with the book and not independently customized per sheet, you only have to create a book autotemplate.

To create a book autotemplate for custom number formats, start a new workbook and create the custom formats. Then save the book as BOOK.XLS in the XLSTART folder. In Windows 98, this folder is located at C:\Program Files\Microsoft Office\Office\XLStart. The drawback to this option is that it only shares the formats with new workbooks. You will want to use the copy/paste method to share custom formats with existing workbooks.