Templates, book.xlt, sheet.xlt, and beyond

Location: http://www.mvps.org/dmcritchie/excel/template.htm      
Home page: http://www.mvps.org/dmcritchie/excel/excel.htm
[View without Frames]
Warning:  Styles can LOOK like a template is changing colors and formatting.

Templates (.XLT)

Changes made to individual worksheets (gridlines/borders, display/print)

Changes to gridlines and column/row headers for Display

Tools > Options > View
  remove check from Row and Column Headers.

Changes to gridlines and column/row headers for Printing

File, Page Setup, Sheets, print:
  uncheck gridlines
  uncheck Row and column headings

and like the display options your changes apply to just the one worksheet.  Note:  Interior colors (and patterns) will wipe out gridlines, in which case you must use Borders to still see cell boundaries.

Templates for new workbooks and new worksheets

If you want to do new workbooks and new sheets differently you must create a template.

A template file (book.xlt) can be created in your XLSTART folder to set up your default Headers and Footers, Margins, # of sheets, gridline (color) etc. for all new blank workbooks.  For use when adding new sheets to existing workbooks you would create a sheet template (sheet.xlt).
Excel stores templates (.xlt) in  C:\documents and settings\<user>\application data\microsoft\templates
Distributed examples in c:\Program Files\Microsoft Office\Templates\<folder name ending in Templates>

The following by Drew Paterson
  http://google.com/groups?threadm=%23FiwxUt%249GA.118%40uppssnewspub05.moswest.msn.net [404 link]

Customised headers and footers are, I think, stored only in the workbooks where they have been customised.  If you want to create a default header and footer set-up which will be available to all new workbooks, then you need to create this, and any other defaults you want, in a new workbook and save it as Book.xlt in your XLSTART folder [or your alternate startup folder]. When you have done that, open another new workbook based on your new template, delete all sheets except one, and save that as Sheet.xlt [in your XLSTART folder or your alternamte startup folder]. That will ensure that any new sheets you add to a workbook will conform to your desired default settings.
Similar posting for creating book.xlt sheet.xlt templates by Tom Ogilvy:
  http://google.com/groups?threadm=eYf2%23GOqAHA.1456%40tkmsftngp03 [404 link]

Creating a Sheet template for special insertion (#sht)

The following tip is as posted by Norman Jones (as reported in misc, 2005-10-10 [invalid link]) The new template sheet will now be available from the Sheet Insert dialog (right-click a sheet tab | insert).

Things that act like templates (#style)

Accidentally formatting the "Normal" style as Text in that workbook. To fix it, go to the Format menu, Style... option.  Select the Normal style from the drop-down.  Chances are that "Number" format will be shown as "@" instead of General.  Click on the [Modify...] button, and the normal Cell Format dialog will appear. Select General from the Number tab, and you should be fine. Hank Scorpio, 2003-01-10 [new untested link]

In case anyone else is interested... [RonD, misc, 2004-06-04] [404 link]
My "Normal" style had been accidently changed to include the gray shading. To fix it, I went into Format+Style, selected style name "Normal", then Modify+Patterns+NoColor.
Now I can add new sheets without any shading.

Actually probably won't be working on a templates section.  Chip Pearson (see home page for a link) was gathering comments on creating a collection of templates on his site, in addition to his own.  So I would expect that he would probably also create information on on everything there is to know about templates.  (That was years ago he did not do that impossible task, so this web page may be reworked into something more interesting concerning templates in the future).

There are so many templates on the web that a listing of them might be better, except for one thing and that is that a lot of the good stuff would be material to go with published material and the descriptions are probably also copyrighted so you could hardly use your own words to describe something unless you have actually used it.

Some examples:

Working Templates available on other sites (#templates)  ⌘ New Section

Related   (#related)

You are one of many distinguished visitors who have visited my site here or in a previous location  since this page was created on January 18, 2002, by removal from Toolbars page.

Visit [my Excel home page]   [Index page]   [Excel Onsite Search]   [top of this page]

Please send your comments concerning this web page to: David McRitchie send email comments

Copyright © 1997 - 2004,  F. David McRitchie,  All Rights Reserved