Section 15.2. Starting from a Template

15.2. Starting from a Template

So far, every example 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 timesaving options for creating a new workbookand the good news is that some of these options will 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 15-1). This pane provides five choices (the names listed in Excel 2002 differ slightly). In the following list, the first two options don't 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 example 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.

Figure 15-1. The New Workbook task pane lets you create a blank Excel workbook or create one based on an existing workbook or template. You can use templates that are already on your computer, or go online for some of the freely downloadable gems on Microsoft's Web site.

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 won't have to bother removing old data.
  • Templates on Office Online . This unusual one opens an external Web browser window where you can browse the vast treasure trove of freely downloadable 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 entering a search word.

  • Templates on my computer . This option lets you choose from one of the templates that live on your computer. When you do, Excel uses that template to create a new workbook. In Excel 2002, this option is called General Templates.

  • Templates on my Web sites . This one 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.

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 15-2). All you need to do is choose your template and click OK to insert it as a new worksheet in your workbook.

15.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, these templates provide good examples that can help you learn how a professional template works if you aren't interested in using these templates, or if you need more flexibility to create your own custom versions.

To use one of the Spreadsheet Solutions templates, follow these steps:

  1. Select File New.

    The New Workbook pane appears on the right side of your Excel window.

  2. 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 15-2).

    Figure 15-2. Top : Rather than struggle through Excel's financial formulas on your own, why not pick the Loan Amortization template to build a new worksheet?
    Bottom : When you do, Excel automatically creates a complete worksheet with all the calculations you need, showing each payment you'll need to make over the life of the loan. The only information that you need to enter is the loan amount, duration, and payment frequency (cells D6 to D11); Excel calculates the rest automatically. (You might assume that this template uses the financial functions you learned about in Chapter 9, 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.

  3. 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.

  4. 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 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).

15.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 15-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 15-3. Here are a few examples of actions you can and can't perform with spreadsheets derived from the Loan Amortization template. Overall, this 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 importantly, the template provides powerful features that newcomers might 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:

  • Prevent people from changing the structure of a worksheet (inserting or deleting cells, columns , or rows).

  • Prevent people from changing the formatting of a worksheet (including the number format or other formatting details like column width and cell color).

  • Prevent people from editing certain cells.

  • Prevent people from entering data in a cell unless it meets certain criteria.

  • Provide additional information about a cell in a pop-up tip box.

  • Prevent people from editingor even seeingthe spreadsheet's formulas.

  • Prevent people from moving to cells they don't need to edit or inspect.

You'll learn how to apply all of these restrictions in this chapter.

Keeping a Template for the Long Term

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 Calculator1," 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 15.3.2.

15.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:

  1. Select File New.

    The New Workbook pane appears on the right side of your Excel window.

  2. Under the Templates category, click the "Templates on Office Online" link.

    In Excel 2002 this link is called "Templates on" A new Web browser window opens and takes you to the templates section of the Microsoft Office Online Web site (Figure 15-4). You can also jump directly to this sitejust go to

    Figure 15-4. Top : To find a template that interests you, you can use the search box at the top right of the Office Online Web page (see the next section for more about searching for templates), or you can browse by category. For example, under Finance and Accounting, you'll find subcategories like Personal Finance.
    Bottom : If you click the Personal Finance link, you'll see a new Web page that lists template files, like 401K Planner and College Costs Calculator. Click any of these files to see more information about the template and instructions on how to download it to your computer.

  3. 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.

  4. Depending on the category you've chosen , you might 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.
  5. Choose a template file from the list by clicking its name.

    A page appears (Figure 15-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 15-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 itif you connect to the Internet using a telephone line and a meager 56K modem. You'll also see an image that shows a surprisingly detailed preview of the template, complete with sample data.

  6. If you're still happy with the template, click the Download Now button.

    You'll first need to click the Accept button, indicating that you agree 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.

  7. Once the template downloads, a new Excel window opens showing you a new worksheet based on the template (Figure 15-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 enter data into the allowed cells, and save the workbook on your computer. See the box on Sidebar 15.2 for information on how to save the template itself on your computerin case you'd like to use it again.

Figure 15-6. Excel shows the Template Help task for templates downloaded from Office Online. (If you're using Excel 2002, you won't see the Help pane.) This window lets you search for help related to the template or rate the template by clicking on the stars. Excel sends your rating back to the Microsoft site for the benefit of other Excel fans.

15.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:

  1. Select File New.

    The New Workbook pane appears on the right side of your Excel window.

  2. In the "Search online for" text box (Figure 15-7), type your search words.

    You can enter as many words as you want, although Excel finds only templates that match all of them. Try using a word that you think might appear in the template name (like "calendar") or a word contained in one of Microsoft's online categories (like "personal finance").

    Figure 15-7. Top : 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."
    Bottom : Excel digs up several matches, each of which you can quickly preview and download.

  3. 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.

  4. 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.

  5. 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.

Excel. The Missing Manual
Excel 2010: The Missing Manual
ISBN: 1449382355
EAN: 2147483647
Year: 2003
Pages: 185

Similar book on Amazon © 2008-2017.
If you may any questions please contact us: