8.2. Starting from a Template So far, every sample worksheet in this book has started from scratch, with nothing more than Excel's empty grid of cells. This approach is a great way to learn the nuts and bolts of how Excel works, but it's not always necessary. In fact, Excel provides several time-saving options for creating a new workbookand the good news is that some of these options give you your first taste of how templates work. To try out these options, begin by selecting File  New. Excel displays the New Workbook task in the Task Pane (Figure 8-1). This pane provides five choices (the names listed in Excel 2002 differ slightly). In the following list, the first two options dont use templates, while the last three use templates stored in various locations. Here's the full list of choices: Blank workbook. This option creates a new, blank workbook with three worksheets, none of which have any preset formatting or data. This is the option used to create all the sample spreadsheets you've seen so far. From existing workbook. This choice displays a dialog box that lets you choose a spreadsheet file. When you click OK, Excel opens a duplicate version of the selected file, giving it a new name. When you save the spreadsheet, Excel prompts you to supply a new name or to use the one it generated (a variation based on the name of the original workbook). Either way, you end up saving a new copy of the original file.
Note: The "From existing workbook" scheme is conceptually similar to using a template, in that it creates a new workbook based on an existing workbook file. However, it's a better idea to create a workbook using a template for several reasons. First of all, because templates are stored in a central place, you don't need to hunt for the file you want to use. Additionally, if the template is properly fine-tuned, you don't have to bother removing old data.
Figure 8-1. The New Workbook task pane lets you create a blank Excel workbook or create one based on an existing workbook or template. 
Templates on Office Online. This unusual option opens an external Web browser window where you can browse the vast treasure trove of freely down-loadable templates that Microsoft provides on its Web site. Just above this option (in Excel 2003 only) is a text box that lets you find an online template just by typing in a search word. Templates on my computer. This option lets you choose from one of the templates that live on your computer. When you choose a template, Excel uses that template to create a new workbook. In Excel 2002, this option is called General Templates. Templates on my Web sites. This option is similar to the "Templates on my computer" option, except it's designed to download template files from a Web server. In order to use this option, you must have configured the Web server you want to use in the Web Folders section of your My Network Places folder (or have had someone else do it for you). Once the setup is done, you'll be able to browse the folders on that Web server and use any templates on it to create new documents.
POWER USERS' CLINIC
Inserting a Template into an Existing Workbook | You can also use templates to add to an existing workbook. This trick is handy if you need to create a workbook with several loan tables, or if you want to combine your own existing worksheets with a template. Either way, Excel inserts the content from the template into a new worksheet, which it adds to your workbook. (For more about managing worksheets in a workbook, including how to move, rename, delete, and add them, see Chapter 5.) To insert a template into an existing workbook, start by opening the workbook. Then, right-click one of the worksheet tabs at the bottom of Excel's grid and choose Insert from the pop-up menu. An Insert dialog box appears, showing you the same Spreadsheet Solutions tab as in the Templates dialog box (Figure 8-2). All you need to do is choose your template and click OK to insert that template as a new worksheet in your workbook. |
8.2.1. Spreadsheet Solutions Templates Excel comes with a small set of basic templates provided by a company called Village Software. These templates are interesting for two reasons. First of all, they may be genuinely useful if you need to create one of the included types of spreadsheets (like a balance sheet, expense statement, loan amortization table, sales invoice, or timecard). Second, even if you aren't interested in using these templatesmaybe you need a template with more flexibility or one that's more geared to a specific project, like tracking the shows your champion Irish Setters participate inthese templates provide good examples that can help you learn how a professional template works. Once you understand how these basic templates work, you can use them as models when you're ready to create your own custom templates.
Note: You might assume that templates use the functions described in Chapter 7, but you can't be sure. Because the template uses protection (which you'll learn more about later in this chapter), you can't navigate to the calculated cells to inspect the formulas they use.
To use one of the Spreadsheet Solutions templates, follow these steps: Select File New. Under the Templates category, click the "On my computer" link. In Excel 2002, this link is labeled General Templates. A Templates dialog box appears (Figure 8-2). Click the Spreadsheet Solutions tab, and then select one of the five ready-to-use templates. When you select a template, Excel shows a small preview on the right side of the dialog box. This preview is usually too small to be of much use, unless you already know what the template looks like and just need to make sure you have the right file. Click OK. Excel opens the template as a new spreadsheet. You can fill out the template with your data, and, when you save it, the program prompts you to choose a new file name. Excel suggests a name based on the name of the template. For example, if you select the Timecard template, Excel suggests the name Timecard1 (you can, of course, change this to any name you want).
Figure 8-2. Top: The Templates dialog box.
Bottom: The Loan Calculator template creates a worksheet complete with all the calculations you need. Just type in the loan amount, duration, and payment frequency (cells D6 to D11); Excel does the rest. 
8.2.2. Discovering the Finer Points of Templates When you create a spreadsheet with one of the Spreadsheet Solutions templates, you'll notice that it doesn't quite behave like an ordinary spreadsheet. For example, as shown in Figure 8-3, there are certain cells you can't edit and certain changes you can't make. These restrictions are designed to make the template impervious to error. Figure 8-3. A template is like a form that lets you fill out only certain areas. Most professional templates use color and outlining to direct your attention to those cells that you can edit. 
The ideal template is foolproof. It prevents people from making mistakes or accidentally removing important information. Most important, the template provides powerful features that newcomers may not know how to apply on their own. For example, even if you don't know how to calculate a loan amortization schedule, you should be able to use the Loan Amortization template to calculate your payments. Here are some of the restrictions that a template can apply: Preventing people from changing the structure of a worksheet (inserting or deleting cells, columns, or rows). Preventing people from changing the formatting of a worksheet (including the number format or other formatting details like column width and cell color). Preventing people from editing certain cells. Preventing people from typing data in a cell unless it meets certain criteria. Providing additional information about a cell in a pop-up tip box. Preventing people from editingor even seeingthe spreadsheet's formulas. Preventing people from moving to cells they don't need to edit or inspect.
You'll learn how to apply all of these restrictions to your own templates later in this chapter. 8.2.3. Office Online Templates Out of the box, Excel provides a fairly limited set of templates. However, Microsoft offers hundreds more templates on the Web and, in the true spirit of the Internet, they're all free. Before you can use any of the Office Online templates, you'll have to download them to your PC. However, assuming you have a working Internet connection, this process only takes a few mouse clicks. Here's how it works: Select File New. Under the Templates category, click the "Templates on Office Online" link. In Excel 2002, this link is called "Templates on Microsoft.com." A new Web browser window opens and takes you to the templates section of the Microsoft Office Online Web site (Figure 8-4). You can also jump directly to this sitejust go to http://office.microsoft.com/templates. Figure 8-4. To find a template that interests you, you can browse by category. For example, under "Finance and Accounting," you'll find subcategories like Personal Finance.  Scroll down the Web page to the Browse Templates section. Click a category link that interests you. The top portion of the page is reserved for new templates, some of which Microsoft created in response to requests (send in your request by clicking on the "Suggest a template" link on the left side of the Web page); other templates vary by season. For example, in the beginning of May, you'll find quick links to Mother's Day card and gift-label templates. At the bottom of the Web page is a section that lets you browse for any template by category. Depending on the category you've chosen, you may see another list of subcategories. If you do, just click the appropriate subcategory link. You'll end up at a list that shows all the matching templates. The list shows the name, creator, and rating of each template. The template rating plays the same role as customer reviews on Amazon.comother folks who have downloaded the template can give it a score of up to five stars (although they can't write a description).
Note: The Templates Web page provides templates for all Office applications, including Word, Excel, Access, and InfoPath (a program that lets companies create souped-up data-entry forms). Of course, you can't use a Word template in Excel, and vice versa. To tell which program a given template requires, look at the icon immediately to the left of the name. If you see the familiar Excel application icon (a green square with an "X" symbol), you can use this template to create new workbooks. Some templates (like greeting cards) are likely to be for Word, while others (like financial forms and worksheets) are usually for Excel.
Choose a template file from the list by clicking its name. A page appears (Figure 8-5) with detailed template information. This information includes the size of the template, the required software version (many templates work equally well in Excel 2003, Excel 2002, and Excel 2000), and a preview graphic that shows what the template looks like. Figure 8-5. Once you've chosen a template, you'll see a detailed page with information about the size of the file and the time it takes to download it. You'll also see an image that shows a surprisingly detailed preview of the template, complete with sample data.  If you're still happy with the template, click the Download Now button. You first need to click the Accept button, indicating that you assent to Microsoft's license agreement. While the template is downloading, a progress indicator appears in a pop-up browser window. Because templates are quite small, it rarely takes more than a minute to download one. If you have a high-speed cable or DSL connection, you'll probably have the template in two or three seconds. If you haven't found the right template, you can click your browser's back button to navigate back to the template list. Alternatively, you can click the arrows on either side of the word "Next" in the top-right corner of the preview. These arrows let you browse to the other templates in the current category. After the template downloads, a new Excel window opens showing you a new worksheet based on the template (Figure 8-6). From this point on, life is exactly the same as if you'd used one of the Spreadsheet Solutions templates on your computer. You can type data into the allowed cells, and save the workbook on your computer. See the box on Section 8.2.4 for information on how to save the template itself on your computerin case you'd like to use it again.
Figure 8-6. Excel 2003 automatically shows the Template Help task when you use a template downloaded from Office Online. This window lets you search for help related to the template or rate the template by clicking on the stars. 
POWER USERS' CLINIC
Keeping an Online Template | When you use a template from Office Online, Excel doesn't store the template on your computer. Instead, the program simply uses it to create a new worksheet. If you want to use the template later to create another new worksheet, you'll need to browse for it online all over again. This situation is far from convenient if you've discovered a fantastic template that you want to reuse to create dozens of new workbooks. The solution is to save a copy of the template as soon as you've downloaded it, but before you've added any new data. (Technically, you're converting the new workbook into a template, but there's nothing wrong with that because templates and workbook files are really the same thing, as explained earlier.) To save your newly created workbook as a template, select File Save As, and make sure you choose the "Template (*.xlt)" option from the "Save as type drop-down list at the bottom of the Save As dialog box. Change the name to whatever is most appropriate (for example, if Excel created a new workbook with the name "Annuity Investment Calculator," you'll probably want to name the template "Annuity Investment Calculator." Don't change the location of the template, which Excel selects automatically when you choose the Template (*.xlt) file type. Once you've carried out this procedure, you can create a new workbook using the template. For more tips on creating custom templates and some additional information about where template files are located on your computer and who can use them, see Section 8.3. |
8.2.4. Searching for Office Online Templates The seven-step process described in the last section lets you find a useful template by digging through various categories. Microsoft also gives you another choiceyou can search for a template by keyword. This approach is great if you want to quickly locate a template for a specific function or industry (for example, you could search for "invoice" or "real estate"). It's not as useful if you just want to browse what's available. Of course, it's up to you which approach you use. In fact, many Excel gurus use both, depending on the task at hand. The neat thing about searching by keyword is that you don't need to leave Excel to do it. Instead, you can perform your Web search straight from the New Workbook task. (This shortcut is only available in Excel 2003.) Here's how it works: Select File New. In the "Search online for" text box (Figure 8-7), type your search words. You can type in as many words as you want, although Excel finds only templates that match all of them. Try using a word that you think may appear in the template name (like calendar) or words contained in one of Microsoft's online categories (like personal finance). Click the Go button next to the text box. After a short delay, the Search Results task appears in the Task Pane, with a list of all the matching documents. This list includes the template name and the category it's listed under. Click the template you want to use. A Template Preview dialog box appears showing a variety of information, including the template's star ranking, its file size, and a preview of what it looks like. If you like the template, click Download to create a new worksheet based on it. After a short delay, Excel creates the new workbook. If you don't want to use the template, click Cancel to close the Template Preview dialog box. You can then type a new search in the text box at the bottom of the Search Results task pane.
Figure 8-7. Left: Excel lets you search for templates and download them without ever leaving the comfort of your favorite spreadsheet program. In this example, the search term is "calendar."
Right: Excel digs up matches. 
|
