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])

Open a new workbook
Delete all sheets except for one
Set the sheet's formats etc to taste
File | Save As
Add a suitably descriptive name
Select ' Template(*.xlt)' in the 'Save as Type' box.
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.

*** PARTS OF THIS SECTION MAY NOT BE CORRECT -- IT IS BEING WORKED ON ****

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:

How can I make headers and footers default to none [invalid] whenever I create a new worksheet? ( Posted November 7, 1996) To do this you need to make two templates and store them in your startup directory. (see article for details).
Changing Excel's Default Settings [privacy violation] similar information covered in more detail in PC Magazine.
download files [invalid] homework examples from a previous semester.
Occurrence Reporting and Processing System (ORPS) analysis software [invalid]
Accounting Forms [invalid] [http://accounting.ucdavis.edu/docs/forms/forms.htm].
Dispersion Pattern Analysis [http://indecol.mtroyal.ab.ca/biol1210/disp.htm].
Academic Affairs Shared Forms Server [404] [http://www.humboldt.edu/~oaa/oaaforms.html]
Internet Software Guide for Engineers [invalid]
Accounts Payable Office -- Instructions for completing the Non-PO Voucher and Invoice Form [invalid] [--http://www.100folhas.pt/software/excel.html--] excel, visual basic, spreadsheet, applications, excel programming, microsoft, scientific, knowledge base, support information, add-ins, download, software, freeware, shareware, demos, tips, newsgroups, excel macros, programming, visual basic for applications, source code, vba, windows, advanced macros, developer, tutorial, advanced, files, archive, professional, code, faq, examples, worksheet, formulas, workbook, cells, polynomials, mathematics, linear, excel, systems, real, matrix, science, statistics, tools, analysis, education, packages, templates, simulation, help, applications, books, sheets, modules, microsoft excel, excel 97, office, visual, language, xls, spreadsheets, excel
Templates [invalid] includes a template for a class roster. (David A Sykes)

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

Microsoft Office templates & themes, or try Office templates & themes
Free Excel Templates » ExcelTemplate.net, appear to be 198 different professional quality templates that are free to download and use. Or 600 free spreadsheets for private and business use, according to other pages on their site -- Excel Tutorial Templates and their About page, which indicates that in 2019 all site templates are now free and can help you in anything from feeding a baby to doing your taxes. Templates are organized in categories and themes, and have pleasing designs and layouts.
You can search the above site for more specific examples by including site:exceltemplate.net in your Google search.
You can search the web using Google: excel templates collections free - Google Search
Another Google search: Internet Software Guide for Engineers excel templates - Google Search

Related (#related)

Toolbars, template information formerly was on the Toolbars page.
Some templates were installed when you installed Excel or Office Windows Start, New Office Document, spreadsheet (tab),..
[actually they may not have been installed even though you see them, so have your Office CD ready].
240150 - HOW TO: Use Excel Startup Folders in Excel 2000 [invalid], provides default names used with different Operating Systems for template, addins, as well as use of and misuse of "Alternate Startup File Location" and "Default File Location" in Tools, Options, General.
Templates [invalid], within Sample Chapter from Microsoft® Office XP Plain & Simple by Carol Brown and Resources Online
create a template in VBA, p2p.wrox.com - vba_excel archive, imports a file longer than the length of one worksheet.
Microsoft Office 97/98 Resource Kit [invalid], this page has lot on it but want to bring specific attention to a Templates [invalid] topic, and AutoTemplates [invalid] (book.xls and sheet.xls). Article also describes differences between Word and Excel templates.
How to install the Excel 2002 Report Manager add-in and Template Wizard add-in in Excel 2003 [invalid], MS KB 873209

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