IN THIS CHAPTER
Opening an existing workbook
Saving and closing workbooks
Sharing workbooks with those who use an older version of Excel
Excel, of course, uses files to store its workbooks. This chapter describes the operations that you perform with workbook files: opening, saving, closing, deleting, and so on. It discusses how Excel uses files and provides an overview of the various types of files. Most of the file operations discussed here occur in the new Backstage view, the screen that you see when you click File on the Excel Ribbon.
This chapter also discusses the Excel 2007 and Excel 2010 file formats and describes how to determine what (if anything) will be lost if you save your workbook in an earlier file format.
As you read through this chapter, remember that you can have any number of workbooks open simultaneously, and that only one workbook is the active workbook at any given time. The workbook's name appears in its title bar (or in the Excel title bar if the workbook is maximized).
When you start Excel normally, it automatically creates a new (empty) workbook called Book1. This workbook exists only in memory and has not been saved to disk. By default, this workbook consists of three worksheets named Sheet1, Sheet2, and Sheet3. If you're starting a project from scratch, you can use this blank workbook.
While you're working in Excel, you can create a new (empty) workbook at any time. Excel provides two ways to create a new workbook:
Choose File New, which displays a screen like the one shown see Figure 7.1. This screen lets you create a blank workbook, a workbook based on a template, or a workbook based on an existing workbook. To create a new blank workbook, select Blank Workbook and then click the Create button.
Press Ctrl+N. This shortcut is the fastest way to start a new workbook if you're not using a template.
Following are some of the ways to open a workbook saved on your hard drive:
Choose File Recent and then select the file you want from the Recent Workbooks list. Only the most recently used files are listed. You can specify the number of files to display (maximum of 50) in the Advanced section of the Excel Options dialog box.
Locate the Excel workbook file via a Windows Explorer file list. Just double-click the filename (or icon), and the workbook opens in Excel. If Excel is not running, Windows automatically starts Excel and loads the workbook file.
Use either of the following methods to display the Open dialog box:
From this dialog box, you can browse your computer for workbooks.
Also, notice that you can right-click a workbook in the list and choose Remove from List. Or, choose Clear Unpinned Items to clear the list and start fresh.
The Open dialog box, shown in Figure 7.2, is resizable. To make it larger or smaller, click the lower-right corner and drag. Also, notice that it's virtually identical to a Windows Explorer window. The only difference is that it includes additional controls at the bottom.
NOTE
The appearance of the Open dialog box varies, depending on the version of Windows you use. The dialog box in Figure 7.2 shows the Open dialog box when running Windows Vista.
To open a workbook from the Open dialog box, use the folder tree display on the left to locate the folder that contains the file, and then select the workbook file from the list on the right. After you locate and select the file, click Open, and the file opens. Or, just double-click the filename to open it.
About Protected ViewOne of the new features in Excel 2010 is Protected View. Although it might seem like Excel is trying to keep you from opening your own files, protected view is all about protecting you from malware. Malware refers to something that can harm your system. Hackers have figured out several way to manipulate Excel files in a way that harmful code can be executed. Protected View essentially prevents these types of attacks by opening a file in a protected environment (a "sandbox"). If you use Excel 2010 to open an Excel workbook that you downloaded from the Web, you'll see a colorful message above the Formula bar. In addition, the Excel title bar displays [Protected View]. If you are certain that the file is safe, click Enable Editing. If you don't enable editing, you will be able to view the contents of the workbook, but you won't be able to make any changes to it. If the workbook contains macros, you'll see another message after you enable editing: Security Warning. Macros have been disabled. If you are sure that the macros are harmless, click Enable Content. Protected View kicks in for the following:
In some situations, you don't care about working with the document. You just want to print it. In that case, choose File Print, and then click the Enable Printing button. It would be nice if copying were allowed in Protected View — but it's not. The only way to copy information from a workbook in Protected View is to enable editing. You have some control over how Protected View works. To change the settings, choose File Options, and click Trust Center. Then click the Trust Center Settings button and click the Protected View tab in the Trust Center dialog box. |
Notice that the Open button is actually a drop-down list. Click the arrow, and you see the additional options:
Open: Opens the file normally.
Open Read-Only: Opens the selected file in read-only mode. When a file is opened in this mode, you can't save changes with the original filename.
Open as Copy: Opens a copy of the selected file. If the file is named budget.xlsx, the workbook that opens is named Copy(1)budget.xlsx.
Open in Browser: Opens the file in your default Web browser. If the file can't be opened in a browser, this option is disabled.
Open in Protected View: Opens the file in a special mode that does not allow editing. In this view, most of the Excel Ribbon commands are disabled. Read more about this new feature in the sidebar, "About Protected View."
Open and Repair: Attempts to open a file that may be damaged and recover information contained in it.
|
Right-clicking a filename in the Open dialog box displays a shortcut menu with many extra Windows commands. For example, you can copy, delete, or rename the file, modify its properties, and so on.
Your Favorite Links list appears at the top of the left panel of the Open dialog box. It contains a list of shortcuts to folders on your system. Initially, it's just a partial list of folders, but you can click More to expand the list.
To add a new folder to the Favorite Links section, click the folder in the Open dialog box and drag it into the Favorite Links area. Customizing the Favorite Links section by adding shortcuts to folders that you use frequently can save you lots of clicking.
At the bottom of the Open dialog box is a button with a drop-down list. When the Open dialog box is displayed, this button shows All Excel Files (and a long list of file extensions). The Open dialog box displays only those files that match the extensions. In other words, you see only standard Excel files.
If you want to open a file of a different type, click the arrow in the drop-down list and select the file type that you want to open. This changes the filtering and displays only files of the type that you specify.
You can also type a filter directly in the File Name box. For example, typing the following will display only files that have an .xlsx extension (press Enter after typing the filter): *.xlsx.
Opening Workbooks AutomaticallyMany people work on the same workbooks each day. If this describes you, you'll be happy to know that Excel can open specific workbook files automatically whenever you start Excel. Any workbooks placed in the XLStart folder open automatically. The location of the XLStart folder varies, depending on your Windows version. To determine the location of the XLStart folder on your system
C:Users<username>AppDataRoamingMicrosoftExcelXLSTART Another XLStart folder may be located here: C:Program FilesMicrosoft OfficeOffice14XLStart Any workbook files (excluding template files) stored in either of these XLStart folders open automatically when Excel starts. If one or more files open automatically from an XLStart folder, Excel won't start with a blank workbook. You can specify an alternate startup folder in addition to the XLStart folder. Choose File Options and select the Advanced tab. Scroll down to the General section and enter a new folder name in the At Startup, Open All Files In field. Then, when you start Excel, it automatically opens all workbook files in both the XLStart folders and the alternate folder that you specified. |
The Open dialog box can display your workbook filenames in several different styles: as a list, with complete details, as icons, and so on. You control the style by clicking the Views icon and then selecting from the drop-down list. The style that you choose is entirely up to you.
When you're working in Excel, your workbook is vulnerable to day-ruining events such as power failures and system crashes. Therefore, you should save your work often. Saving a file takes only a few seconds, but re-creating hours of lost work takes many hours.
Excel provides four ways to save your workbook:
Click the Save icon on the Quick Access toolbar.
Press Ctrl+S.
Press Shift+F12.
Choose File Save.
|
If your workbook has already been saved, it's saved again using the same filename. If you want to save the workbook to a new file, choose File Save As (or press F12).
If your workbook has never been saved, its title bar displays a default name, such as Book1 or Book2. Although Excel allows you to use these generic workbook names for filenames, you'll be better off using more descriptive filenames. Therefore, the first time that you save a new workbook, Excel displays the Save As dialog box to let you provide a more meaningful name.
The Save As dialog box is similar to the Open dialog box. Select the desired folder in the folder list on the left. After you select the folder, enter the filename in the File Name field. You don't need to specify a file extension — Excel adds it automatically, based on the file type specified in the Save as Type field. By default, files are saved in the standard Excel file format, which uses an .xlsx file extension.
|
|
If a file with the same name already exists in the folder that you specify, Excel asks whether you want to overwrite that file with the new file. Be careful: You can't recover the previous file after you overwrite it.
If you've used computers for any length of time, you've probably lost some work. You forgot to save a file, or maybe the power went out and your unsaved work was lost. Or maybe you were working on something and didn't think it was important, so you closed it without saving — and later realized that it was important. A new feature in Excel 2010 called Versions might make these types of "D'oh!" moments less frequent.
As you work in Excel, your work is periodically saved, automatically. It happens in the background so you don't even know that it's happening. What's new in Excel 2010 is that you can access these autosaved versions of your work. And this even applies to workbooks that you never explicitly saved.
The Versions feature consists of two components:
Versions of a workbook are saved automatically, and you can view them.
Workbooks that you closed without saving are saved as draft versions
To see whether any previous versions of the current workbook are available, choose File Info. The Versions section Versions lists the available old versions (if any) of the current workbook. In some cases, more than one autosaved version will be listed. In other cases, no autosaved versions will be available.
You can open an autosaved version by clicking its name. Remember that opening an autosaved version won't automatically replace the current version of your workbook. Therefore, you can decide whether the autosaved version is preferable to the current version. Or, you can just copy some information that may have been accidentally deleted, and paste it to your current workbook.
When you close the workbook, the autosaved versions are deleted.
When you close a workbook without saving your changes, Excel asks whether you're sure. If that unsaved workbook has an autosaved version, the "Are you sure?" dialog box informs you of that fact.
To recover a workbook that you closed without saving, choose File Info Versions, and choose Recover Draft Versions. You'll see a list of all draft versions of your workbooks. You can open them and (hopefully) recover something that you needed. These drafts are also listed in the recent file list, displayed when you choose File Recent.
Draft versions are deleted after four days, or until you edit the file.
Filenaming RulesExcel 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:
You can use uppercase and lowercase letters in your names to improve readability. The filenames aren't case sensitive. For example, My 2011 Budget.xlsx and MY 2011 BUDGET.xlsx are equivalent names. |
NOTE
You can adjust the Versions feature in the Save tab of the Excel Options dialog box. For example, you can change the autosave time interval (the default is 10 minutes), turn off autosave for a particular workbook, or disable this feature for all workbooks.
In some cases, you may want to specify a password for your workbook. When a user attempts to open a password-protected workbook, a password must be entered before the file is opened.
To set a password for a workbook, choose File Info, and click the Protect Workbook button. This button displays some additional options. Choose Encrypt With Password. Excel displays the Encrypt Document dialog box, shown in Figure 7.3. Enter the password, and then enter it again. Click OK, and save the workbook.
When you re-open the workbook, you will be prompted for a password.
|
If you have hundreds of Excel files, you might have a problem locating the workbook that you need. Using descriptive filenames can help, and using folders and subfolders (with descriptive names) also makes it easier to find the particular file you need. In some cases, though, that's not enough.
Fortunately, Excel lets you assign a variety of descriptive information (sometimes known as metadata) to a workbook. These are known as document properties. This information includes such items as the author, title, subject, and so on.
When you choose File Info you can view (or modify) the document properties for the active workbook. This information is shown on the right side of the screen (see Figure 7.4).
|
When you use the Open dialog box, you can specify additional columns to display. Start by clicking Views (at the top of the Open dialog box) and then choose Details. Right-click the column header to see a list of other properties to include. Click the More option for a longer list of properties (see Figure 7.5).
You can sort the file list in the Open dialog box by a particular column by clicking the column heading. Also, notice that each heading is actually a drop-down list. Click the drop-down arrow, and you can filter the list to show only files that match the selected properties.
Using document properties lets you work with files as if they were in a database. The key, of course, is taking the time to ensure that the document properties are actually assigned, and are accurate.
|
Using Workspace FilesIf you have a project that uses multiple workbooks, you probably get tired of opening the same files every time you work on the project. The solution? Create a workspace file:
After creating a workspace file, you can open it by choosing File Open. In the Open dialog box, specify Workspaces (*.xlw) from the Files of Type drop-down list. Better yet, pin it to the top of the recent files list so it will always be easily accessible. NOTE
A workspace file contains only the filenames and window position information — not the workbooks. Make sure that you save the workbooks that comprise the workspace. |
NOTE
This discussion of document properties just barely scratches the surface. For example, you can display additional properties and even specify a custom document information panel template that contains information specific to your needs. A complete discussion is beyond the scope of this book.
The Info pane of Backstage View displays more file-related options, as shown in Figure 7.6. To display this pane, choose File Info. These options, described in the following sections, may be useful if you plan to distribute your workbook to others. Note that not all workbooks display all the options shown in Figure 7.6. Only the relevant options are shown.
If the active workbook displayed a security warning when you opened it, the Info pane will display a Security Warning section, with an Enable Content button. Use this panel to find out more about why Excel triggered the security warning, and to enable the content if you're sure that it's safe to do so.
If the active workbook is an old workbook opened in compatibility mode, you'll see the Compatibility Mode section in the Info pane. To convert the workbook to the Excel 2007/2010 format, click the Convert button.
|
In the Permissions section of the Info pane, click the Protect Workbook button to display the following options:
Use this option to designate the workbook as "final." The document is saved as a read-only file to prevent changes. This isn't a security feature. Rather, the Mark as Final command is useful to let others know that you are sharing a completed version of a workbook.
Encrypt with Password
Use this command to specify a password that is required to open the workbook. See "Specifying a password," earlier in this chapter.
Protect Current Sheet
This command lets you protect various elements of a worksheet. It displays the same dialog box as the Review Changes Protect Sheet command. See Chapter 31 for more information about protecting worksheets.
Protect Workbook Structure
This command lets you protect the structure of a workbook. It displays the same dialog box as Review Changes Protect Workbook. See Chapter 31 for more information for more information about protecting the structure of a workbook.
Restrict Permission by People
Use this option to specify those who may open the document. This feature requires a fee-based service called Information Rights Management.
Add a Digital Signature
This command allows you to "sign" a workbook digitally. See Chapter 31 for more information about digital signatures.
The Prepare for Sharing section of the Info pane contains a Check for Issues button. When clicked, the button displays three options:
Inspect Document
This 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.
Check Accessibility
This command checks the workbook for potential problems that might occur for people with disabilities. The results of the check are displayed in a task pane in the workbook. This feature works only with Excel 2007/2010 workbooks.
This command is useful if you need to save your workbook in an older file format. It displays a very helpful Compatibility Checker dialog box that lists potential compatibility problems. This dialog box also appears when you save a workbook using an older file format. For more information, see "Excel File Compatibility," later in this chapter.
Clicking the Manage Versions button of the Versions section of the Info pane displays two options:
Recover Draft Versions
This command displays the Open dialog box, pointed to the folder where Excel displays AutoRecover versions of workbooks that were not saved. If you accidentally forget to save a file, this command may help you out.
Delete All Draft Versions
This command simply deletes all unsaved files on your drive. Alternatively, you can delete the files manually.
After you're finished with a workbook, you can close it to free the memory that it uses. You can close a workbook by using any of the following methods:
Click the Close button (the X) in the workbook's title bar.
Double-click the Excel icon on the left side of the workbook's title bar. This icon is visible only if the workbook window is not maximized.
Press Ctrl+F4.
Press Ctrl+W.
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.
|
Nothing is more frustrating 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 disasters is not a difficult task.
Earlier in the chapter, I discuss the AutoRecover feature that makes Excel save a backup copy of your workbook at regular intervals (see "Using AutoRecover"). AutoRecover is a good idea, but it certainly isn't the only backup protection you should use. If a workbook is important, you need to take extra steps to ensure its safety. The following backup options help ensure the safety of individual files:
Keep a backup copy of the file on the same drive. Although this option offers some protection if you make a mess of the workbook, it won't do you any good if the entire hard drive crashes.
Keep a backup copy on a different hard drive. This method 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.
Keep a backup copy on a network server. This method 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.
Keep a backup copy on an Internet backup site. Several Web sites specialize in storing backup files. This is a safe method, as long as the company doesn't go out of business.
Keep a backup copy on a removable medium. This is the safest method. Using a removable medium, such as a USB drive enables you to physically take the backup to another location. So, if your system (or the entire building) is damaged, your backup copy remains intact.
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.
If you save your workbook to an older file format (such as XLS, for versions prior to Excel 2007), Excel automatically runs the Compatibility Checker. The Compatibility Checker identifies the elements of your workbook that will result in loss of functionality or fidelity (cosmetics).
Figure 7.7 shows the Compatibility Checker dialog box. Use the Select Versions to Show button to limit the compatibility checking to a specific version of Excel.
The bottom part of the Compatibility Checker lists the potential compatibility problems. To display the results in a more readable format, click the Copy to New Sheet button.
Keep in mind that compatibility problems can also occur with Excel 2007 even though that version uses the same file format as Excel 2010. You can't expect features that are new to Excel 2010 to work in earlier versions. For example, if you create a workbook with Sparkline charts (a new feature in Excel 2010) and send it to a colleague who uses Excel 2007, the cells that contain the Sparklines will be empty. In addition, formulas that use any of the new worksheet functions will return an error. The Compatibility Checker identifies these types of problems.
Perhaps one of the most confusing aspects of Excel is the nearly overwhelming number of file 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 file formats.
NOTE
Excel 2010 can open all files created with earlier versions of Excel.
Excel file formats (which were introduced in Excel 2007) are
XLSX: A workbook file that does not contain macros
XLSM: A workbook file that contains macros
XLTX: A workbook template file that does not contain macros
XLTM: A workbook template file that contains macros
XLSA: An add-in file
XLSB: A binary file similar to the old XLS format but able to accommodate the new features
XLSK: A backup file
With the exception of XLSB, these are all "open" XML files, which means that other applications are able to read and write these types of files.
|
To save a file for use with an older version of Excel, choose File Save As. In the Save As dialog box, select one of the following from the Save as Type drop-down:
Excel 97-2003 Workbook (*.xls): If the file will be used by someone who has Excel 97, Excel 2000, Excel 2002, or Excel 2003.
Microsoft Excel 5.0/95 Workbook (*.xls): If the file will be used by someone who has Excel 5 or Excel 95.
The Office Compatibility PackNormally, those who use an earlier version of Excel can't open workbooks saved in the new Excel 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 can open files created in Office 2007 or Office 2010 and also save files in the new format. The Office programs that are affected are Excel, Word, and PowerPoint. This software doesn't endow the older versions with any new features: It just gives them the capability to open and save files in the new format. To download the Compatibility Pack, search the Web for Office Compatibility Pack. |