New Horizons Toronto - Mississauga are computer training centers located in Toronto and Mississauga, ON. This computer training facility in the Greater Toronto Area (GTA) of Ontario provides computer classes and computer certifications like Microsoft and Cisco certification classes and courses to the Toronto, North York, Richmond Hill, and Markham areas.

Our centers in Toronto and Mississauga, ON are also close to: Hamilton, London, Brampton, Markham, Windsor, Kitchener, Vaughan, Burlington, Oakville, Oshawa, Richmond Hill, St. Catharines, Cambridge, Guelph, Barrie, Whitby, Pickering, Waterloo, Brantford, Niagara Falls, Ajax, Peterborough, Clarington, Newmarket, Caledon, Welland, Halton Hills, Georgina, Innisfil and Orangeville, Ontario Canada..

News > Features

Have it your way: Categorize and store templates according to your preferences

Manage templates effectively, Add a custom sheet to the New Dialog box & Add templates to your custom sheet

August 25th, 2006

 

As your custom template collection continues to grow, it's important to keep it organized; custom templates are only as easy to apply as they are to locate. After all, you probably don't store important paper files in an unorganized heap - why do the same with your workbook templates? One of the simplest and most effective ways to organize your templates is to categorize them on custom sheets in the New dialog box (Templates dialog box in Excel 2002/2003).

Manage templates effectively

To access the Excel templates that are available on your system, choose File | New from the menu bar. (In 2002, choose File | New from the menu bar, and then click on the General Templates link in the New Workbook task pane. In 2003, choose File | New from the menu bar, and then click on the On My Computer link). As you'll quickly notice, Excel's available templates are organized on various property sheets. By default, the General sheet contains the default workbook template, and the Spreadsheet Solutions sheet contains Excel's other built-in templates. Creating and populating your own custom sheets is a piece of cake.

Add a custom sheet to the New dialog box

Before you can add your own custom property sheets to the New dialog box (Templates dialog box in 2002/2003), you need to know where Excel stores its built-in templates. This location, known as the user template directory, varies depending on the version of Excel you are using, as well as the operating system on which you're using it. Table A lists the standard locations of Excel's user template directory for many common system configurations.

Table A: Common user template locations
Version User templates directory
Excel 2000 C:\Windows\Application Data\Microsoft\Templates

or

C:\Windows\Profiles\username\Application Data\Microsoft\Templates
Excel 2002/2003 C:\Documents and Settings\username\Application Data\Microsoft\Templates

Note: To navigate to the user templates directory, you'll need to enable hidden folders. To do so, start My Computer or Windows Explorer, and then choose Tools | Folder Options from the menu bar. Click on the View tab, and then select the Show Hidden Files and Folders option button in the Advanced Settings list box. Click OK when you've finished.

The property sheets that appear in the New dialog box (Templates dialog box in Excel 2002/2003) correlate to the subdirectories that are located within the default user templates directory. To add a new property sheet, all you need to do is add a new subdirectory (i.e., a folder) to the user templates directory. For instance, create a Department Forms tab, we create a folder in the user templates directory. You can accomplish this directly from Excel, or you can use a file manager, such as My Computer or Windows Explorer.

Method 1: Create a new folder using Excel

1. Choose File | Open from Excel's menu bar to access the Open dialog box. Refer to Table A to determine where the user templates directory is located on your system, and then use the Open dialog box to navigate to that location.

2. Click the Create New Folder button on the Open dialog box's Standard toolbar. Type a name for your custom folder in the New Folder dialog box, and then click OK.

3. Click Cancel to dismiss the Open dialog box.

Method 2: Create a new folder using a file manager

1. Start Windows Explorer or open a My Computer window. Using Table A as a reference, navigate to the user templates directory.

2. Choose File | New | Folder from the menu bar to create a new folder. Type a new name for the folder, and then press [Enter].

3. Click Cancel to dismiss the Open dialog box.

Add templates to your custom sheet

After you've created a custom subdirectory in the user templates directory, you're ready to add templates to it. Once you do, they'll appear on a new sheet in the New dialog box (Templates dialog box in 2002/2003). Although you can reorganize your templates using Excel's Open dialog box, you'll find that this task is much more efficient when you use a file manager, such as My Computer or Windows Explorer.

First, open a My Computer window, and then access the user templates directory, using Table A as a guide. Here, you should find any custom templates you've created and saved. The templates you see here also appear on the General sheet when you access the New dialog box (Templates dialog box in 2002/2003). To categorize these templates on other sheets, drag them to the sheets' corresponding subdirectories. When you've finished, close the file manager. Your newly organized template collection should now be reflected in the New dialog box (Templates dialog box in 2002/2003).

Where are the built-in templates?

The user templates directory is designed to store and organize custom templates only. Excel's built-in templates are stored in a separate location on your system so they can't be tampered with.

 

Related courses

Excel 2003 - Level 1
Learn to use Microsoft® Office Excel 2003 to manage, edit, and print data.

Excel 2003 - Level 2
Learn to use Microsoft Office Excel 2003 to streamline and enhance your spreadsheets with templates, charts, graphics, and formulas.

Excel 2003 - Level 3
Learn how to automate common tasks, apply advanced analysis techniques to more complex data sets, collaborate on worksheets with others, and share Excel data with other applications.