OzGrid

Create Excel Workbook & Worksheet Templates

< Back to Search results

 Category: [Excel]  Demo Available 

Create Excel Workbook & Worksheet Templates

 

A template is like a dress pattern -  it is a model that is used over and over again containing specified styles.  If you have a need in Excel to constantly change the default settings of a workbook or worksheet, then you can use a template to save your personalized specific settings and tell Excel to load your settings at your command or as a default.

Normally, when you Start Excel, a new workbook is automatically presented to you.  This is the book.xlt template (xlt is the file extension for a template).   The default book.xlt template contains the standard settings for a new Excel workbook, such as an Arial, 20pt black font, grey gridlines showing, white background etc.  Excel uses its default Book.xlt template when Starting Excel, or from within Excel when you create a new workbook without specifying a template.

You can create templates in Excel for both worksheets and workbooks and save these templates to either your Start folder , or assign them to the Templates dialog under their own tab for selection.  A template assigned to the Start folder will automatically load when Excel is opened and Excel will use this template to create a blank workbook when you Start Excel, or create a new workbook without specifying a template.  Any sheets inserted into the workbook will also use the template.

WORKBOOK TEMPLATES

Workbook templates can contain things such as specific formatting styles, column/row labels, headers and footers, formulas and even macros.  Workbook templates are used for settings you want in all new workbooks based on the template.

To create a workbook template:

Create a new Excel workbook containing all your specified settings.
Go to File>Save As
In the Save as type box, select Template
In the Save in box, select the folder you wish to store your template in.

Note: If you wish the first page of your template to be viewed as a picture in the Preview box of the Templates dialog, go to File>Properties, click the Summary tab and select Save preview picture before saving.

WORKSHEET TEMPLATES

Worksheet templates are used to store settings you want to appear on all new sheets of the same type.  The default worksheet template is stored as sheet.xlt.  When you create a template for a worksheet, Excel uses the template to create blank worksheets when you insert a new sheet to a workbook.

To create a worksheet template:

Create a new Excel workbook containing one worksheet
One the worksheet specify all required settings
Go to File>Save As
In the Save as type box, select Template
In the Save in box, select the folder you wish to store your template in

SAVING TO THE XLStart FOLDER

If you want your template to be available as soon as you Start Excel, you need to save directly to the XLStart folder, usually located at:

C:\Program Files\Microsoft Office\Office11\XLStart

SAVING TO THE TEMPLATES FOLDER

If you wish your template to be available in the Templates dialog, try the following:

Create a new Excel workbook or worksheet
Specify all required settings
Go to File>Save As
Locate the Templates Folder, usually C:\Documents and Settings\user_name\Application Data\Microsoft\Templates
In the Save as type box, select Template
Give your template a name.

This will force your template to appear in the Templates dialog under the General tab.

Note: Sometimes, due to network restrictions, the Templates folder cannot be seen.  If this happens to you, you need to contact your network administrator.

You can go one step further and create a new folder within the Templates folder C:\Documents and Settings\user_name|Application Data\Microsoft\Templates and move your template to that folder. call girl dubaixdate  This will create a new tab on the templates dialog

To remove a template, you need to locate the folder that it is saved in and delete it.

 

See also: Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions and Index to new resources and reference sheets

 

See also:

Count Lines in a Cell
Count of Each Item in a List
Excel VBA Macro - Count Or Sum By Fill Color
Count Words in a Cell

 

Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.

 

Microsoft ® and Microsoft Excel ® are registered trademarks of Microsoft Corporation. OzGrid is in no way associated with Microsoft.


Gallery



stars (0 Reviews)