Passwords can be up to 15 characters long and are case sensitive. Be careful with this option because it is impossible to open the workbook (using normal methods) if you forget the pass- word. Also, remember that Excel passwords can be cracked, so it’s not a perfect security measure.
For additional security, you may prefer to encrypt your workbook. See “Encrypting a docu- ment,” later in this chapter.
The General Options dialog box has a few other features.
n Always Create Backup:If this option is checked, the existing version of the workbook is renamed before the workbook is saved. The new filename will be named “Backup of xxx.xlk,”
where xxxrepresents the original filename. Creating a backup enables you to go back to the previ- ously saved version of your workbook.
n Password To Modify:This option enables you to specify a password that will be required before changes to the workbook can be saved under the same filename. Use this option if you want to
CROSS-REF CROSS-REF CAUTION CAUTION
Filenaming Rules
The Excel workbook files are subject to the same rules that apply to other Windows files. A filename can be up to 255 characters, including spaces. This length enables you to give meaningful names to your files.
You can’t, however, use any of the following characters in your filenames:
\ (backslash)
? (question mark) : (colon)
* (asterisk)
“ (quote)
< (less than)
> (greater than)
| (vertical bar)
You can use uppercase and lowercase letters in your names to improve readability. The filenames aren’t case sensitive — My 2007 Budget.xlsx and MY 2007 BUDGET.xlsx are equivalent names.
n Read-Only Recommended:If this option is checked, Excel presents a dialog box suggesting that the file be opened as read-only. This is just a gentle suggestion. The person opening the file can override this suggestion if he or she likes.
FIGURE 8.4
This difficult-to-find dialog box is where you specify a password for your workbook.
Other workbook options
Choose Office➪Prepare, and you’ll find still more file-related options, as shown in Figure 8.5. These options, described in the following sections, may be useful if you plan to distribute your workbook to others.
FIGURE 8.5
Choose Office ➪Prepare for some additional options.
Setting workbook properties
The Office➪Prepare➪Properties command adds a new panel directly below the Ribbon. As you can see in Figure 8.6, Excel is able to store some additional “metadata” about the file. This information includes such items as the author, title, subject, and so on.
FIGURE 8.6
Use the Properties panel to store additional information about your workbook.
This file information can be useful if you remember to enter the data. For example, when you use the Open dialog box, you can specify Properties as your Views option. Then, the dialog box displays the metadata when you select a file (see Figure 8.7).
If you click the drop-down in the Properties title bar, you can choose Advanced, which displays the Properties dialog box. This dialog box has five tabs:
n General:Displays general information about the file — its name, size, location, date created, and so on. You can’t change any of the information in this panel.
n Summary:Contains nine fields of information that you can enter and modify. Some of these fields are the same as those in the Properties panel.
n Statistics:Shows additional information about the file and can’t be changed.
n Contents:Displays the names of the sheets in the workbook, as well as the named ranges.
n Custom:This tab enables you to store, in a sort of database, a variety of information about the file. For example, if the workbook deals with a client named Smith and Jones Corp., you can keep track of this bit of information and use it to help locate the file later.
FIGURE 8.7
You can view workbook properties in the Open dialog box.
Inspecting a document
The Office➪Prepare➪Inspect Document command displays the Document Inspector dialog box. This feature can alert you to some potentially private information that may be contained in your workbook — perhaps information that’s contained in hidden rows or columns or hidden worksheets.
If you plan on making a workbook available to a large audience, it’s an excellent idea to use the Document Inspector for a final check.
Encrypting a document
Earlier in this chapter, in “Specifying a password,” I described how to set a password for a workbook. To provide a higher level of security, you may prefer to encrypt your workbook using Office➪Prepare➪ Encrypt Document. When you choose this command, you’ll be prompted for a password, which you must enter twice.
Restricting permissions
Excel supports Information Rights Management (IRM), which allows you to restrict access to workbooks in order to prevent sensitive information from being printed, forwarded, or copied.
This feature, accessed by using Office➪Prepare➪Restrict Permission, is fairly complex and requires addi- tional software. Consult the Help system for additional details.
Adding a digital signature
The Office➪Prepare➪Add A Digital Signature command allows you to “sign” a workbook.
Refer to Chapter 31 for more information about digital signatures.
Marking a document final
Choose Office➪Prepare➪Mark As Final to mark a workbook as finalized. A finalized document is marked read-only, and you can’t edit or modify it. When you open a finalized document, the status bar dis- plays an additional icon, and all editing command are disabled.
Checking compatibility
When you save a workbook using an older file format, Excel displays its very helpful Compatibility Checker dialog box, shown in Figure 8.8. You can also display this dialog box at any time by choosing Office➪ Prepare➪Run Compatibility Checker.
It’s important to understand the limitations regarding version compatibility. Even though your colleague is able to open your file, there is no guarantee that everything will function correctly or look the same. You can’t expect features that are new to Excel 2007 to work in earlier versions. For example, you’ll find that a SmartArt diagram is converted to a picture, table formatting is lost, and charts may look different. In addi- tion, formulas that use any of the new worksheet functions will return an error.
The Compatibility Checker identifies the elements of your workbook that will result in loss of functionality or fidelity (cosmetics). To display the Compatibility Checker results in a more readable format, click Copy To New Sheet.
For more information about file compatibility, see “Excel File Compatibility,” later in this chapter.
CROSS-REF CROSS-REF
FIGURE 8.8
The Compatibility Checker dialog box informs you of potential incompatibilities.
Closing workbooks
After you’re finished with a workbook, you should close it to free the memory that it uses. You can close a workbook by using any of the following methods:
n Choose Office➪Close.
n Click the Close button (the X) in the workbook’s title bar.
n Double-click the Excel icon on the left side of the workbook’s title bar.
n Press the Ctrl+F4 shortcut key.
n Press the Ctrl+W shortcut key.
If you’ve made any changes to your workbook since it was last saved, Excel asks whether you want to save the changes to the workbook before closing it.
Safeguarding your work
Nothing is worse than spending hours creating a complicated Excel workbook only to have it destroyed by a power failure, a hard-drive crash, or even human error. Fortunately, protecting yourself from these disas- ters is not a difficult task.
Earlier in the chapter, I discussed the AutoRecover feature that makes Excel save a backup copy of your workbook at regular intervals (see “Using AutoRecover”). I also mentioned the Always Create Backup option in the General Options dialog box. These are good ideas, but they certainly aren’t the only backup protection you should use. If a file is truly important, you need to take extra steps to ensure its safety. The following backup options help ensure the safety of individual files:
n Keep a backup copy of the file on the same drive. Essentially what happens when you select the Always Create A Backup option when you save a workbook file. Although this option offers some protection if you make a mess of the worksheet, it won’t do you any good if the entire hard drive crashes.
n Keep a backup copy on a different hard drive.Assumes, of course, that your system has more than one hard drive. This option offers more protection than the preceding method, because the likelihood that both hard drives will fail is remote. If the entire system is destroyed or stolen, however, you’re out of luck.
n Keep a backup copy on a network server.Assumes that your system is connected to a server on which you can write files. This method is fairly safe. If the network server is located in the same building, however, you’re at risk if the entire building burns down or is otherwise destroyed.
n Keep a backup copy on a removable medium.The safest method. Using a removable medium, such as a CD-ROM, enables you to physically take the backup to another location. So, if your sys- tem (or the entire building) is damaged, your backup copy remains intact.
Excel File Compatibility
Perhaps one of the most confusing aspects of Excel is the nearly overwhelming number of files formats that it can read and write. With the introduction of Excel 2007, things got even more confusing because it has quite a few new files formats.
Excel 2007 can open all files created with earlier versions of Excel.
Recognizing the Excel 2007 file formats
Excel’s new file formats are
n XLSX: A workbook file that does not contain macros n XLSM: A workbook file that contains macros
n XLTX: A workbook template file that does not contain macros n XLTM: A workbook template file that contains macros n XLSA: An add-in file
n XLSB: A binary file similar to the old XLS format but able to accommodate the new features n XLSK: A backup file
With the exception of XLSB, these are all “open” XLM files, which means that other applications are able to read and write these types of files.
The XML files are actually zip-compressed text files. If you rename one of these files to have a ZIP extension, then you’ll be able to examine the contents using any of several zip file utilities — including the zip file support built into Windows.
TIP TIP
NOTE NOTE
The Office 2007 Compatibility Pack
Normally, those who use an earlier version of Excel can’t open workbooks saved in the new Excel 2007 file formats. But, fortunately, Microsoft has released a free Compatibility Pack for Office 2003 and Office XP.
If an Office 2003 or Office XP user installs the Compatibility Pack, they will be able to open files created in Office 2007 and also save files in Office 2007 format. The Office programs that are affected are Excel, Word, and PowerPoint.
To download the Compatibility Pak, search the Web for Office 2007 Compatibility Pack.
Saving a file for use with an older version of Excel
To save a file for use with an older version of Excel, choose Office➪Save As and select one of the following from the Save As Type drop-down:
n Excel 97-2003 Workbook (*.xls):If the file will be used by someone who has Excel 97, Excel 2000, Excel 2002, or Excel 2003.
n Microsoft Excel 5.0/95 Workbook (*.xls):If the file will be used by someone who has Excel 5 or Excel 95.
If the workbook will be used by someone who has installed the Office 2007 Compatibility Pack, you don’t need to save it using an earlier file format (see “The Office 2007 Compatibility Pack” sidebar for details).
NOTE NOTE
Atemplateis essentially a model that serves as the basis for something else.
An Excel template is a workbook that’s used to create other workbooks.
This chapter discusses some of the templates included with Excel and also describes how to create your own template files. Creating a template takes some time, but in the long run, doing so may save you a lot of work.
Exploring Excel Templates
The best way to become familiar with Excel template files is to jump in and try a few. Most versions of Excel include template files, but Excel 2007 gives you quick access to hundreds of such files.
Viewing templates
To explore the Excel templates, choose Office➪New to display the New Workbook dialog box. The templates listed in the New Workbook dialog box are in three categories:
n Those installed on your hard drive (Template Categories➪Installed Templates)
n Those that you can download from Microsoft Office Online n Those that you’ve created (Template Categories➪Featuring➪My
Templates)
In addition, the right side of the dialog box displays a list of templates that you’ve used recently.
The Microsoft Office Online section contains a number of categories, and some categories have subcategories. Click a category (or subcategory), and you’ll see the available templates. Use the View button to change how the templates are
IN THIS CHAPTER
Understanding Excel’s templates Working with the default templates
Creating custom templates
Using and Creating Templates
displayed (either thumbnails or details). Either view shows a preview of them template in the right panel.
Figure 9.1 shows templates available in the Invoices category.
FIGURE 9.1
Templates that you can use for invoices.
Microsoft Office Online has a wide variety of templates, and some are better than others. If you download a few duds, don’t give up. Even though a template may not be perfect, you can often modify a template to meet your needs. Modifying an existing template is often easier than creating a workbook from scratch.
Creating a workbook from a template
To create a workbook based on a template, just select the template and click Download. Depending on the size of the file and the speed of your Internet connection, it can take anywhere from a few seconds to a minute or more. What you do next depends on the template. Every template is different, but most are self- explanatory. Some workbooks require customization. Just replace the generic information with your own information.
NOTE NOTE
Figure 9.2 shows a workbook that’s based on an invoice template.
It’s important to understand that you’re not working with the template file. Rather, you’re working with a workbook that was created from the template file. If you make any changes, you’re not changing the template — you’re changing the workbook that’s based on the template. After you download a template from Microsoft Office Online, that template is available in the My Templates category in the New Workbook dialog box, so you don’t need to redownload it if you want to re-use the template.
This particular workbook has a few simple formulas that perform calculations using the data that you enter.
Figure 9.3 shows the workbook after you enter data. Notice that formulas calculate the subtotal, sales tax, and total.
FIGURE 9.2
A workbook created from an invoice template downloaded from Microsoft Office Online.
If you want to save the workbook, click the Save button. Excel proposes a named based on the template’s name, but you can use any name you like.
NOTE NOTE
FIGURE 9.3
The workbook, after entering some information.
Modifying a template
A template file is just like a workbook file. As such, you can open a template file, make changes to it, and then resave the template file. To open a template, choose Office➪Open (not File➪New) and locate the template file (it will probably have an XLTX or XLT extension). Template files, by default, are stored in:
C:\Documents and Settings\<user name>\Application Data\Microsoft\
Templates
When you open a XLST (or *.XLT) template file, you are opening the actual file — you are notcreating a workbook from the template file.
Looking at the invoice template shown earlier in this chapter, you may want to modify it so that it shows your company information and uses your actual sales tax rate. Then, when you use that template in the future, the workbook created from it will already be customized.
To create a workbook from a modified template, you must select the template from the My Templates section in the New Workbook dialog box. Clicking My Templates displays the New dialog box shown in Figure 9.4. Just select the template and click OK.
FIGURE 9.4
The New dialog box displays downloaded templates stored on your hard drive.
Understanding Custom Excel Templates
So far, this chapter has focused on templates that were created by others. The remainder of the chapter deals with custom templates— templates that you create.
Why create custom templates? The main reason is to make your job easier. For example, you may always like to use a particular header or footer on your printouts. Consequently, the first time that you print a worksheet, you need to spend time entering the header and footer information. Although it isn’t a lot of work, wouldn’t it be easier if Excel simply remembered your favorite page settings and used them automatically?
The solution is to modify the template that Excel uses to create new workbooks. In this case, you modify the template file by inserting your header into the template. Save the template file using a special name, and then every new workbook that you create has your customized page settings.
Excel supports three types of templates, which I discuss in the following sections:
n The default workbook template:Used as the basis for new workbooks.
n The default worksheet template:Used as the basis for new worksheets inserted into a workbook.
n Custom workbook templates:Usually, these ready-to-run workbooks include formulas, but they can be as simple or as complex as you like. Typically, these templates are set up so that a user can simply plug in values and get immediate results. The Microsoft Office Online templates (discussed earlier in this chapter) are examples of this type of template.
Working with the default templates
The term default templatemay be a little misleading. If you haven’t created your own template files to con- trol the default settings, Excel uses its own internal settings — not an actual template file. In other words, Excel uses your template files to set the defaults for new workbooks or worksheets, if these files exist. But if you haven’t created these files, Excel is perfectly happy to use its own settings.
Using the workbook template to change workbook defaults
Every new workbook that you create starts out with some default settings. For example, the workbook has three worksheets, the worksheets have gridlines, text appears in the fonts specified by the default document template. columns are 8.43 units wide, and so on. If you’re not happy with any of the default workbook settings, you can change them.
Making changes to Excel’s default workbook is fairly easy to do, and it can save you lots of time in the long run. Here’s how you change Excel’s workbook defaults: