Templates in Microsoft Excel 2003 Introduction Microsoft Excel lets you create your own templates for both new workbooks and new worksheets. These templates can store various characteristics including: • Data and Graphics - eg data, formulae, charts, page headers/footers • Formatting - eg cell/sheet formats, cell styles, page formats, the number of sheets in a workbook, protected or hidden areas of a worksheet • Toolbars and Option settings - eg custom toolbars, macros, calculation and display options This document shows you how to create your own workbook and worksheet templates. It also shows you how (on a non-public PC) to make these templates the defaults, so that every new workbook and/or worksheet automatically has the characteristics you have set up. Workbook Templates To create a workbook template you simply create a new file with all the characteristics required and then save it as a template. 1. Start up Excel as usual or open a [New] workbook 2. Set up the new workbook exactly as you require it - either make your own changes or follow steps 3 to 5 below 3. Insert an extra worksheet (right click on any sheet tab, choose Insert..., check that Worksheet is selected then press <Enter> for [OK]) 4. Rename the worksheets to Spring, Summer, Autumn and Winter (again right click on the tabs in turn, choose Rename, type in each name and press <Enter>) 5. Colour each worksheet (eg green, yellow, brown and white) - press <Ctrl a> to select the cells then click on the list arrow attached to the [Fill Color] button and select a colour Once you have finished setting up your workbook: 6. Open the File menu and choose Save As... 7. Change Save as type: to Template (*.xlt) 8. Supply a File name: (eg Seasons) then press <Enter> for [Save] 9. [Close] the template file window - but leave Excel running Creating a Workbook using a Template It's now time to try out your template. The instructions which follow show you how to create an ordinary workbook and one using the new template. 1. Click on the [New] button - a normal workbook opens 2. Open the File menu and choose New... - the Task Pane appears on the right 3. Under the Templates heading select On my computer... 4. Now select the new template (Seasons.xlt) then press <Enter> for [OK] This time a new workbook is opened, with four sheets suitably named and coloured. 5. [Close] the new workbook (there's no need to save it) Worksheet Templates Creating a worksheet template is done much in the same way except that you must ensure you only have a single sheet in the file used for the template. 1. Using the empty (normal) workbook, right click on any sheet tab and Delete it 2. Repeat step 1 to leave a single sheet in the workbook 3. Right click on the last sheet tab and Rename it - eg Week (press <Enter>) You now set up the worksheet with the required format. You can choose your own or follow the ones here. 4. Starting in cell B1 type Mon then drag the cell handle across to cell H1 - you should get all the days of the week 5. With cells B1 to H1 still selected, click on [Bold] and [Center] 6. Finally, click on the [Borders] list arrow and choose [All Borders] (bottom row, second icon) Your worksheet is now ready to save as a template: 7. Open the File menu and choose Save As... 8. Change Save as type: to Template (*.xlt) 9. Supply a Filename: (eg Week) then press <Enter> for [Save] 10. [Close] the template file - but leave Excel running Creating a Worksheet using a Template It's now time to try out your template. 1. Click on the [New] button for a new workbook (with 3 empty worksheets) 2. Right click on any sheet tab and choose Insert... 3. Select the new template (Week.xlt) then press <Enter> for [OK] A new worksheet matching the template you have just created is added to the workbook. 4. [Close] the workbook (there's no need to save it) Note: You can insert several worksheets at a time by saving them in the same template file. For example, if you had inserted the Seasons template, instead of Week, four new sheets would have been added to your workbook. Changing the Default Templates The default templates used by Excel are called Book.xls and Sheet.xls. If you create these files then you define your own default workbook and worksheet, which will appear each time you open Excel or add a new worksheet. The files must reside in the XLStart folder, which is in ...\user_name\Application Data\Microsoft\Excel\XLStart. On the public PCs you do not have the privileges to create these files but, if you have Excel on your own PC, you can do this if you so choose.