Using Data Lists as Templates for Other Lists

After you decide on the type of data you want to store in a workbook and what that workbook should look like, you probably want to be able to create similar workbooks without adding all the formatting and formulas again. For example, you might have settled on a design for your monthly sales tracking workbook.

When you have settled on a design for your workbooks, you can save one of the workbooks as a template, or pattern, for similar workbooks you create in the future. You can leave any labels to aid data entry, but you should remove any existing data from a workbook that you save as a template, both to avoid data entry errors and to remove any confusion as to whether the workbook is a template. You can also remove any worksheets you and your colleagues won't need by right-clicking the tab of an unneeded worksheet and, from the shortcut menu that appears, clicking Delete.

If you want your template workbook to have more than the standard number of worksheets (such as 12 worksheets to track shipments for a year, by month), you can add worksheets by clicking the Insert Worksheet button on the tab bar at the bottom-left corner of the Excel 2007 window.

To create a template from an existing workbook, save the model workbook under the Office Excel Template file type (a file with an .xltx extension), which you can choose from the Save as type drop-down list in the Save As dialog box. If you ever want to change the template, you can open it like a standard workbook (that is, an Excel 2007 file with the .xlsx extension) and make your changes. When you have completed your work, resave the file normallyit will still be a template.

See Also

You can also save your Excel 2007 workbook either as an Excel 972003 template (.xlt) or as a macro-enabled Excel 2007 workbook template (.xltm). For more information on using macros in Excel 2007 workbooks, see "Introducing Macros" in Chapter 13.

After you save a workbook as a template, you can use it as a model for new workbooks. To create a workbook from a template in Excel 2007, click the Microsoft Office Button and then click New to display the New Workbook dialog box.

The leftmost pane of the New Workbook dialog box displays featured templates, which appear by default when you open the New Workbook dialog box, templates that are installed on your computer, and a list of template categories available through the Microsoft Office Online Web site. The middle pane of the dialog box displays the Recently Used Templates section, which contains a list of any previously used templates.

Tip

You can also find templates and other tools related to your job functions at the Microsoft Work Essentials Web site, which you can find online by visiting http://office.microsoft.com and clicking Work Essentials in the left pane.

From the Templates dialog box, you can double-click the template you want to use as the model for your workbook. Excel 2007 creates a new workbook with the template's formatting and contents in place.

Tip

The default file type for files created with a template is workbook (.xlsx), not template (.xltx).

In addition to creating a workbook template, it's possible to create a template you can add as a worksheet within an existing workbook. To create a worksheet template, design the worksheet you want to use as a template, delete all the other worksheets in that workbook, and save the single-sheet workbook as a template. You can then add a worksheet based on that template to your workbook by right-clicking a sheet tab and then clicking Insert to display the Insert dialog box.

The Insert dialog box splits its contents into two panes. The General pane contains buttons you can click to insert a blank worksheet (the equivalent of clicking the Insert Worksheet button on the tab bar), a chart sheet, and any worksheet templates you created. The Spreadsheet Solutions tab contains a set of useful templates for a variety of financial and personal tasks.

To add a spreadsheet from the Insert dialog box to your workbook, click the desired template and then click OK.

Note

The other two options on the General tab, MS Excel 4.0 Macro and MS Excel 5.0 Dialog are there to help users integrate older (much older) Office Excel spreadsheet solutions into Excel 2007. You'll know if you need to use them; odds are, you won't.

In this exercise, you'll create a workbook from an existing template, save a template to track hourly call volumes to each regional center, save another version of the file as a worksheet template, and insert a worksheet based on that template into a new workbook.

USE the Daily Call Summary workbook from the My Documents\Microsoft Press\Excel SBS\MultipleFiles folder.

BE SURE TO start Excel 2007 before starting this exercise.

OPEN the Daily Call Summary workbook.

1.	Click the Microsoft Office Button and then click Save As. The Save As dialog box appears.
2.	Click the Save as type field down arrow and then click Excel Template. Excel 2007 displays the 2007 Microsoft Office system default template folder.
3.	Click Save. Excel 2007 saves the workbook as a template and closes the Save As dialog box.
4.	Click the Microsoft Office Button and then click Close. Excel 2007 closes the Daily Call Summary workbook.
5.	Click the Microsoft Office Button and then click New. The New Workbook dialog box appears.
6.	In the Template Categories list, click Installed Templates. The Installed Templates list appears.
7.	Click Project To Do List and then click Create. Excel 2007 creates a workbook based on the selected template.
8.	On the Quick Access Toolbar, click the Save button. The Save dialog box appears.
9.	In the File name field, type ToDoList, use the dialog box controls to browse to the My Documents\Microsoft Press\Excel SBS\MultipleFiles folder, and then click Save. Excel 2007 saves your workbook.
10.	Click the Microsoft Office Button and then, in the Recent Files list, click the Daily Call Summary.xlsx file. The Daily Call Summary file appears.
11.	Right-click the Sheet2 sheet tab and then click Delete. Excel 2007 deletes the worksheet, leaving one worksheet in the workbook.
12.	Click the Microsoft Office Button and then click Save As. The Save As dialog box appears.
13.	In the File name field, type Daily Call Worksheet.
14.	If necessary, click the Save as type field down arrow and then click Excel Template.
15.	Click Save. Excel 2007 saves your template.
16.	Click the Microsoft Office Button and then click Close. Excel 2007 closes the workbook.
17.	Click the Microsoft Office Button and then click New. The New Workbook dialog box appears.
18.	Click Blank Workbook and then click Create. A blank workbook appears.
19.	Right-click any sheet tab and then click Insert. The Insert dialog box appears.
20.	On the General tab, click Daily Call Worksheet and then click OK. Excel 2007 creates a new worksheet based on the template.
21.	On the Quick Access Toolbar, click the Save button. The Save As dialog box appears.
22.	In the File name field, type Current Call Summary, use the dialog box controls to browse to the My Documents\Microsoft Press\Excel SBS\MultipleFiles folder, and then click Save. Excel 2007 saves your workbook.

CLOSE the Current Call Summary workbook.