Section 15.3. Creating Templates
15.3. Creating Templates
Ready-to-use templates are a fantastic innovation because they provide fine- tuned worksheets without forcing you to write a single formula. Of course, these templates also have a major drawback: no matter how crafty Microsoft programmers are, or how much money Bill Gates spends, they can't predict your every need.
For example, the Travel Services Invoice provides a generic worksheet that a travel agency might use to bill its customers. But what if you need to group different types of expenses separately, apply different discount rates to various groups, include a late fee, and tack on a few miscellaneous charges to pad your company's bottom line? If that's the case, you'd need to add your own formulas, restrictions, and formatting to the template. In that case, starting over from scratch and creating a template with the exact features you need is probably easier.
Fortunately, creating custom templates isn't difficult at all.
15.3.1. Understanding Custom Templates
Earlier in this chapter, you learned how to create a new workbook by choosing a template from the Templates dialog box. In order to understand how to create your own templates, you need to know how the Templates dialog box worksnamely, how does Excel find the templates that are installed on your computer?
The answer is a little tricky because there are actually three types of templates, all of which can appear in the Templates dialog box. (To open the Templates dialog box, select File 
New and, in the New Workbook pane, click the "On my computer" link.)
The different types of templates include:
-
Built-in templates . These templates are a part of the Office package and are included automatically when you install Excel. You can find them (depending on where you installed Office) in a folder like C:\Program Files\Microsoft Office\Templates\1033 . (1033 is the language ID for U.S. English.) You'll see the Spreadsheet Solutions templates in this folder, along with a slew of templates for other Office programs.
Note: This list includes only the templates on your computer. It doesn't include the templates from Office Online because these are actually on Microsoft's Web servers.
-
Local templates . These are the custom templates you create. They are stored in a special folder on your computer (more on that later). When you first install Excel, this category is emptysince you haven't made any custom templates yet.
| UNDER THE HOOD Tracking Down Templates |
| It's a good idea to take note of where your Templates folder is. This information helps when you want to share your templates with other people (or take their templates and copy them to your computer). Even if you aren't planning on sharing templates, it still makes sense to pay attention to their location, so you can back them up for safekeeping. The Templates folder location varies depending on the version of Windows you're using. Some common locations include:
For example, if you're using Windows XP and you've logged yourself in under the user account billjones, you'll probably find the templates in C:\Documents and Settings\billjones\Application Data\Microsoft\Templates . (Incidentally, this folder includes user-defined templates for all installed Office programs, including Word. (So don't panic if you see extra files here that don't appear in the Excel Templates dialog box.) You might already be familiar with this location, because it's closely related to the special My Documents folder where Windows applications encourage you to store files (like Excel worksheets and Word documents). If your My Documents folder is located at C:\Documents and Settings\billjones\My Documents , you'll find the Templates folder down a nearby branch at C:\Documents and Settings\billjones\Application Data\Microsoft\Templates . Essentially, C:\Documents and Settings\billjones is a user-specific folder where Windows stores temporary data, documents, desktop settings, your list of favorite Internet shortcuts, and even custom templates. The easiest way to find out where your templates folder is located is to use Excel to save a new template. Just choose File |
-
Workgroup templates . These are also custom templates. The only difference is, they're stored in a shared location where more than one person can access them. This way, other people using the same computer (or connected via a network) can make use of your hard work. When you first install Excel, the workgroup template folder doesn't exist yet. If you want to use this feature, you'll need to set it up yourself.
The list of built-in templates is completely unchangeable. Even if you save your own custom files to the same folder, Excel steadfastly ignores them. For that reason, you should never try to add or remove a built-in template.
On the other hand, you can freely add custom templates to the local and workgroup folders. Best of all, Excel always checks these folders before it displays the Templates dialog box. If Excel finds two custom templates in the local template folder, and three more in the workgroup template folder, it shows all five in the Templates dialog box. These templates appear in the General tab (not the Spreadsheet Solutions tab), and you can use them to create new spreadsheets in exactly the same way as you use a built-in template.
So, all you need to know to integrate your custom templates with Excel's is where to save your files. As it turns out, Excel can take you there automatically, as you'll see in the next section.
15.3.2. Building a Custom Template
To create a custom template, you simply need to build a new workbook, add any headings, formatting, and formulas you desire , and then save it as a template. You can start this process from scratch by opening a new, blank workbook, or you can refine an existing built-in template. Either way, you should follow the same process of perfecting your workbook until it's ready for template status. Here are some tips:
-
Clear out the junk . Your template should be a blank form politely waiting for input. Clear away all the data on your template, unless it's generic content. For example, you can leave your company name or the worksheet title, but it probably doesn't make sense to have sample numbers .
-
Assume formulas won't change . The ideal template is one anyone can use, even Excel novices who are too timid to edit a formula. If you have a formula that contains some data that might need to change (for example, the sales commission, interest rate, late fee, and so on), don't type it directly into your formulas. Instead, put it in a separate cell, and use a cell reference within the formula. That way, the person using the template can easily modify the calculation just by editing the cell .
-
Don't be afraid to use lists and outlining . These features are too complicated for many mere mortals (those who haven't read this book, for example), but they make spreadsheets easier to use and more powerful. By putting these advanced frills into the template, you ensure that people can use them in their spreadsheets without having to learn how to apply them on their own. Charts and pictures, which you'll learn more about in Chapter 18, are also good template additions.
-
Turn off worksheet gridlines . Most templates don't use Excel's gridlines. That way, it's easier to see custom borders and shading, which you can use to draw attention to the important cells . To turn off gridlines, select Tools
Options, and choose the View tab. Turn off the Gridlines checkbox (under the Window Options section at the bottom of the tab), and then click OK. -
Add the finishing touches . Once you have the basicstitles, captions, formulas, and so onit's time to create a distinct look. You can add borders, change fonts, and inject color . (Just remember not to go overboard with cell shading, or the output may be impossible to read on a black and white printer.)
-
Delete extra worksheets, and name those that remain . Every workbook starts with three worksheets, named Sheet1, Sheet2, and Sheet3. The typical template has only one worksheet, and it's named appropriately (such as Expense Form). For information about deleting and renaming worksheets, refer to Chapter 5.
-
Add a custom toolbar to make a really slick spreadsheet . For a real treat, you can build a toolbar with custom buttons and attach it to your template. You can add buttons that provide quick access to useful Excel features or trigger custom macros. For more information about how to build a custom toolbar, see Appendix B. Once you've created the toolbar, follow the steps for attaching it to the current workbook before you save the template.
Once you've perfected your template, you're ready to save it. Follow these steps:
-
Select File
Save As. The Save As dialog box appears.
-
In the "Save as type" drop-down list box at the bottom of the window, Choose "Template (*.xlt)".
Excel automatically browses to the Templates folder, which is where the templates you create are stored on your computer. Typically, this is a folder like C:\Documents and Settings\ UserName \Application Data\Microsoft\Templates (where UserName is the name of the Windows account you used to log in).
-
Type the template name, and then click Save.
The saved template file automatically appears in the General tab of the Templates dialog box, as shown in Figure 15-8. That means you can use it to build new spreadsheets. Just select File
New (at which point the New Workbook task appears), and click the "On my computer" link.
Note: Of course, your templates don't need to appear in the Templates dialog box. You can save a template file anywhere on your computer, and open it later. However, when you open a template file directly, Excel won't create a new workbook. That means it's easy to overwrite the template by accident . Also, you might have trouble tracking down a template you created earlier if it doesn't appear in the Templates dialog box.
|
15.3.3. Sharing Templates with Others
As you've already seen, once you place a template in the Templates folder, it appears in the Templates dialog box where you can use it to create new workbooks. However, you might be disappointed to find out that you're the only one who can benefit from all your hard work. Co-workers using other computers won't be able to access your templates. In fact, your template won't even be available to other people who use the same computer if they log in with a different user name and password. That's because the Templates folder is a user-specific setting. You might use the folder C:\Documents and Settings\billjones\Application Data\Microsoft\Templates , but if Sarah Cheng logs into the same computer, Excel only bothers to check C:\Documents and Settings\sarahcheng\Application Data\Microsoft\Templates .
There are several strategies you can use to get around this problem.
-
You can give a copy of your template to everyone who wants to use it. Choose any way you like to transfer the template (by email, on a floppy disk or CD, over a network drive, and so on), but make sure you tell folks to put it in their own local Templates folder, as explained earlier.
-
You can create a workgroup templates folder. In this case, multiple people will use the same workgroup template folder, so any template files you put there are automatically shown in everybody's Templates dialog box.
You can use the second option to share files between more than one user account on the same computer or, for even better results, on a network drive that a whole team of people can use.
Unfortunately, Excel nearly throws a wrench into the whole process because it doesn't let you specify what folder to use for workgroup templates!
However, there's a back door that you can get at through Microsoft Word that gives you the answer. Because Excel and Word both use the same template system, if you configure the workgroup templates folder in Word, it also takes effect in Excel. (Don't bother asking why Word provides this service and Excel doesn'tit's just one of those enduring Microsoft mysteries.)
Tip: If you're unlucky enough to not have Word installed, you still have one more painful option. You can write a custom Excel macro that sets the location of the workgroup templates folder. The line of code you need is Application.NetworkTemplatesPath = "[ path goes here ] ", but you'll have to learn a fair bit about the intricacies of VBA (Visual Basic for Applications) programming before you can actually use it. For more information, see Chapter 26.
Once you accept the fact that you need to rely on Word, the process actually becomes quite easy. Here's what you need to do:
-
Decide which folder you want to use as a shared folder.
You may want to create a new folder now. If you do, the easiest approach is to use a tool like Windows Explorer.
-
Start Word.
You'll find Word in the Start menu. In Office 2003, you need to select Programs
Microsoft Office Microsoft Word 2003. -
Choose Tools
Options. The Options dialog box appears. The Options dialog box in Word looks fairly similar to Excel's Options dialog box.
-
Select the File Locations tab.
The File Locations tab (Figure 15-9) is where you tell Office applications where they should look for certain types of files. There are actually three interesting changes you can make here.
If you modify the Documents directory, Excel and Word start off in that directory the first time you choose to save or load a file after you launch the application.
If you change the "User templates" directory, you can specify where Excel and Office look for user-created templates stored on your computer.
Finally, if you set the "Workgroup templates" folder, you can tell Word and Excel where to look for shared templates. This is the option you're interested in.
Note: Remember, when Excel looks for templates, it searches both the user templates and the workgroup templates folders. Then it shows all the templates in the General tab of the Templates dialog box.
Figure 15-9. If you haven't configured the workgroup templates folder before, you'll notice that it starts off blank. That's because Office has no idea where to put shared templates until you give it the right instructions.
-
Select the "Workgroup templates" entry in the list, and then click Modify.
A Modify Location dialog box appears. This dialog box looks more or less the same as the standard dialog boxes you use to open and save files.
-
Browse to the folder you want to use, and click OK.
Remember, you aren't limited to your local computer. Feel free to jump to a network drive, or even browse your network places to find a specific server.
-
Click OK again to close the Options dialog box.
Now, the change has been made. Remember, in order for template sharing to work, everyone needs to perform this same set of steps to configure their copies of Excel to look in the same shared folder.
Template sharing is a simple idea that can become incredibly useful in a company environment. Instead of sending template files whizzing back and forth in emails, trying to keep a group of overworked employees in synch each time the template changes, you simply need to modify the templates in the shared location. That way, everybody always has the latest versions available, and there are no distribution headaches .
EAN: 2147483647
Pages: 185