This chapter describes the operations that you perform with workbook files: opening, saving, closing, 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 Backstage view, the screen that you see when you click the File button above the Excel Ribbon. It also discusses templates, a special kind of workbook file.

Creating a New Workbook

When you start Excel, it displays a Start screen that lists recently used files and shows templates that you can use as the basis for a new workbook. One of the template options is Blank Workbook, which gives you an empty workbook. Figure 6.1 shows a portion of the Start screen.

After you start Excel and create a blank workbook, the empty workbook is called Book1. This workbook exists only in memory, and it hasn't been saved to disk. By default, this workbook contains one worksheet named Sheet1. If you're starting a project from scratch, you can use this blank workbook. By the way, you can change the default number of sheets in a new workbook by using the General tab of the Excel Options dialog box.

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 that lets you create a blank workbook or a workbook based on a template. To create a new blank workbook, click Blank workbook.
• Press Ctrl+N. This shortcut is the fastest way to start a new workbook if you're not using a template.

Opening an Existing Workbook

Here are some of the ways to open a workbook that's already been saved:

• Choose File ➪ Open ➪ Recent and then select the file that you want from the list on the right. 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.
• Choose File ➪ Open and choose a location from the list on the left. The locations will vary depending on which “places” you've set up. You may see cloud-based options. One of the options is always This PC. You can navigate to your files directly using the list, or you can click Browse to display the Open dialog box, which gives you many more options.
• Locate the Excel workbook file via a File Explorer file list. Just double-click the filename (or icon), and the workbook opens in Excel. If Excel isn't running, Windows automatically starts Excel and loads the workbook file.

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. You can resize the Open dialog box by using the control in the lower-right corner. After you locate and select the file, click Open, and the file opens. Or just double-click the filename to open it.

Notice that the Open button is actually a drop-down list. Click the arrow, and you will see these 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 doesn't allow editing. In this view, most of the Excel Ribbon commands are disabled. Read more about this feature in the nearby sidebar “About Protected View.”
• Open and Repair  Attempts to open a file that may be damaged and recover information contained in it.
• Show Previous Versions  Applies to documents stored where version histories are maintained, as on OneDrive or SharePoint Online.

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 lost work can take many hours.

Excel provides four ways to save your workbook:

• Click the Save icon on the Quick Access Toolbar. (It looks like an old-fashioned floppy disk.)
• Press Ctrl+S.
• Press Shift+F12.
• Choose File ➪ Save.

If your workbook has already been saved, it's saved again using the same filename in the same location. If you want to save the workbook to a new file or to a different location, choose File ➪ Save As (or press F12).

If your workbook has never been saved, Excel displays a Save This File dialog box, as shown in Figure 6.2. You can enter a filename and choose a location, such as on your PC or in your OneDrive account—if you have one. A new (unsaved) workbook has a default name, such as Book1 or Book2. Although Excel allows you to use these generic workbook names for filenames, you'll almost always want to specify a more descriptive filename. This dialog box has a More Options link that takes you to the Save As portion of Backstage view.

The Save As portion of Backstage view has most of the same information as the Save This File dialog box, just in a different layout. There are locations on the left side and folders on the right side. The Browse option on the left opens the classic Save As dialog box. 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 filename 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 filename extension. If the file contains macros, Excel defaults to the .xlsm filename extension.

If a file with the same name already exists in the location 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.

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 the file without saving. Of course, later you realized that it was indeed important. Excel's AutoRecover feature 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. If necessary, you can access these autosaved versions of your work. This even applies to workbooks that you never explicitly saved.

The AutoRecover 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 active workbook are available, choose File ➪ Info. The Manage Workbook section 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. Figure 6.3 shows a workbook with two recovery points.

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.

Password-Protecting a Workbook

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, follow these steps:

1. Choose File ➪ Info and click the Protect Workbook button. This button displays some additional options in a drop-down list.
2. Choose Encrypt with Password from the list. Excel displays the Encrypt Document dialog box, as shown in Figure 6.4.

   

3. Enter the password, click OK, and then enter it again.
4. Click OK and save the workbook.

When you reopen the workbook, you'll be prompted for a password.

Organizing Your Files

If you have hundreds of Excel files, you might have a problem locating the workbook that you need. Using descriptive filenames can help. 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, tags, and categories.

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. Click the link at the bottom to toggle between showing more or fewer properties.

Other Workbook Info Options

The Info pane of Backstage view displays more file-related options. 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 described in the following sections. Only the relevant options are shown.

Closing Workbooks

After you're finished with a workbook, you can close it to free the memory that it uses. Other workbooks will remain open. When you close the last open workbook, you also close Excel.

You can close a workbook by using any of the following methods:

• Choose File ➪ Close.
• Click the Close button (the X) in the right corner of the window's title bar.
• Press Ctrl+F4.
• Press Ctrl+W.

If you've made any changes to your workbook since it was last saved, Excel asks if you want to save the changes to the workbook before closing it.

Safeguarding Your Work

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, we discussed 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 websites specialize in storing backup files.
• Keep a backup copy on a removable medium. This is probably 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.

Working with Templates

A template is essentially a model that serves as the basis for something else. An Excel template is a special type of workbook that's used as the basis to create other workbooks. The following sections discuss some of the templates available from Microsoft, and it 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. Excel gives you quick access to hundreds of template files.

Viewing templates

To explore the Excel templates, choose File ➪ New. The template thumbnails displayed on the screen that appears are just a small sampling of those that are available. Click one of the suggested search terms, or enter a descriptive word and search for more.

For example, enter invoice and click the Search button. Excel displays many thumbnails.

Figure 6.5 shows the results of a template search for invoice.

Creating a workbook from a template

To create a workbook based on a template, just locate a template that looks like it might do the job and click the thumbnail. Excel displays a box with a larger image, the source for the template, and some additional information. If it still looks good, click the Create button. Otherwise, click one of the arrows to view details for the next (or previous) template in the list.

When you click the Create button, Excel downloads the template and then creates a new workbook based on that template.

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.

Figure 6.6 shows a workbook created from a template. This workbook needs to be customized in several areas. But if this template will be used again, it's more efficient to customize the template rather than every workbook created from the template.

If you want to save the newly created workbook, click the Save button. Excel proposes a filename based on the template's name, but you can use any filename you like.

Modifying a template

A template file that you download is just like a workbook file. You can open a template file, make changes to it, and then resave it. For example, with the invoice template shown in Figure 6.6, you may want to modify it so that it shows your company information and logo 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 open a template for editing, choose File ➪ Open (not File ➪ New) and locate the template file (it will have an .xltx, .xltm, or .xlt filename extension). When you open a template file by choosing File ➪ Open, you're opening the actual template file—you are not creating a workbook from the template file.

One way to find the location of your downloaded template files is to look at your trusted locations list:

1. Choose File ➪ Options. The Excel Options dialog box appears.
2. Choose Trust Center and click the Trust Center Settings button. The Trust Center dialog box appears.
3. In the Trust Center dialog box, choose Trusted Locations. You'll see a list of trusted locations. Downloaded templates are stored in the location described as User Templates. If you want to modify (or delete) a downloaded template, this is where you'll find it.

On our systems, downloaded templates are stored here:

C:Users<username>AppDataRoamingMicrosoftTemplates

After you've made changes to the template, use File ➪ Save to save the template file. Future workbooks that you create from this template will use the modified version of the template.