Chapter 7: Understanding Excel Files

Opening Workbooks Automatically

Many 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

1.Choose File Options and click Trust Center.

2.Click the Trust Center Settings button.

3.In the Trust Center dialog box, click the Trusted Locations tab. You’ll see a list of trusted locations.

4.Look for the path for the location described as User Startup. The path might look something like this:

C:\Users\<username>\AppData\Roaming\Microsoft\Excel\XLSTART\

Another XLStart folder may be located here:

C:\Program Files\Microsoft Office\Office14\XLStart\

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.

Choosing your file display preferences

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.

Saving a Workbook

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.

147

Part I: Getting Started with Excel

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.

Caution

Saving a file overwrites the previous version of the file on your hard drive. If you open a workbook and then completely mess it up, don’t save the file! Instead, close the workbook without saving it and then reopen the good copy on your hard drive. n

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.

Tip

To change the default file format for saving files, access the Excel Options dialog box. Click the Save tab and change the setting for the Save Files in This Format option. For example, if your workbooks must be compatible with older versions of Excel, you can change the default format to Excel 97-2003 Workbook (*.xls). Doing so eliminates the need to select the older file type every time you save a new workbook. n

Caution

If your workbook contains VBA macros, saving it with an .xlsx file extension will erase all the macros. It must be saved with an .xlsm extension (or saved in the XLS or XLSB format)). If your workbook has macros,

Excel will still propose to save it as an XLSX file. It other words, Excel suggests a file format that will destroy your macros! It will, however, warn you that the macros will be lost. n

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.

148

Chapter 7: Understanding Excel Files

Using AutoRecover

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.

Recovering versions of the current workbook

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.

Recovering unsaved work

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.

149

Part I: Getting Started with Excel

Filenaming Rules

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:

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

Specifying a Password

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.

FIGURE 7.3

The Encrypt Document dialog box is where you specify a password for your workbook.

150