Section 1.4. Saving Files | Excel 2007[c] The Missing Manual

1.4. Saving Files

As everyone who's been alive for at least three days knows , you should save your work early and often. Excel is no exception. You have two choices for saving a spreadsheet file:

Save As . This choice allows you to save your spreadsheet file with a new name. You can use Save As the first time you save a new spreadsheet, or you can use it to save a copy of your current spreadsheet with a new name , in a new folder, or as a different file type. (Alternate file formats are discussed in Section 1.4.2.1.) To use Save As, select Office button Save As, or press F12. Figure 1-16 shows you the Save As dialog box as it appears on a Windows XP computer. (The Windows Vista version of the Save As dialog box has all the same features, but way more style.)

Figure 1-16. The Save As dialog box lets you jump to common folders using the big buttons on the left, or you can browse a folder tree using the drop-down "Save in" menu. Once you've found the folder you want, type the file name at the bottom of the window, and then pick the file type. Finally, click Save to finish the job.

Save . This option updates the spreadsheet file with your most recent changes. If you use Save on a new file that hasn't been saved before, it has the same effect as Save As: Excel prompts you to choose a folder and file name. To use Save, select Office button Save, or press Ctrl+S. Or, look up at the top of the Excel window in the Quick Access toolbar for the tiny Save button, which looks like an old-style diskette.

Tip: Resaving a spreadsheet is an almost instantaneous operation, and you should get used to doing it all the time. After you've made any significant change, just hit Ctrl+S to make sure you've stored the latest version of your data.

1.4.1. The Excel 2007 File Format

Since time immemorial, Excel fans have been saving their lovingly crafted spreadsheets in . xls files (as in AirlineSilverware.xls). Excel 2007 changes all that. In fact, it introduces a completely new file format, with the extension . xlsx (as in AirlineSilverware.xlsx).

At first glance, this seems a tad over the top. But the new file format has some real advantages:

It's compact . The new Excel file format uses Zip file compression, so spreadsheet files are smallerway smaller (as much as 75 percent smaller than their original sizes). And even though the average hard drive is already large enough to swallow thousands of old-fashioned Excel files, the new compact format is easier to email around.
It's less error-prone . The new file format carefully separates ordinary content, pictures, and macro code into separate sections. Microsoft claims that this change makes for tougher files. Now, if a part of your Excel file is damaged (for example, due to a faulty hard drive), there's a much better chance that you can still retrieve the rest of the information. (You'll learn about Excel disaster recovery in Section 1.4.5.)
It's extensible . The new file format uses XML (the eXtensible Markup Language), which is a standardized way to store information. (You'll learn more about XML in Chapter 25). XML storage doesn't benefit the average person, but it's sure to earn a lot of love from companies that plan to build custom software that uses Excel documents. As long as Excel documents are stored in XML, these companies can create automated programs that pull the information they need straight out of a spreadsheet, without going through Excel. These programs can also generate made-to-measure Excel documents all on their own.

For all these reasons, .xlsx is the format of choice for Excel 2007. However, Microsoft prefers to give people all the choices they could ever need (rather than make life really simple), and Excel file formats are no exception. The new Excel file format actually has three related flavors. Along with the standard .xlsx, there's the closely related .xls m cousin, which adds the ability to store macro code. If you've added any macros to your spreadsheet, Excel prompts you to use this file type when you save your spreadsheet. (You'll learn about macro code in Chapter 27.)

Finally, your third choice is .xls b , which is a specialized option that just might be faster when you're opening and saving gargantuan spreadsheets. The .xlsb format has the same automatic compression and error-resistance as .xlsx, but it doesn't use XML. Instead, it stores information in raw binary form (good ol' ones and zeroes), which is speedier in some situations. To use the .xlsb format, choose Office button Save As, and then, from the "Save as type list, choose Excel Binary Workbook (.xlsb).

POWER USERS' CLINIC
Under the Hood with the New File Format

Here's a shocking secret: The new .xlsx file format is actually a Zip file in disguise. It's composed of several files that are compressed and then packaged together as a single unit. With a little know-how, you can take a look at these hidden files-within-a-file, which makes for a great Excel party trick. Here's how:

Save your Excel spreadsheet in .xlsx format.
Browse to the file (using My Computer, Windows Explorer, or your favorite file management tool). If you're lazy, you can save the file on the desktop so you can manipulate it right there.
Right-click the file, and then choose Rename.
Change the file extension to .zip. So if you start with BlackMarketDinnerware.xlsx, change it to BlackMarketDinnerware.zip.
Now, open the Zip file by double-clicking the file.
You can now see the files that are hidden inside your Excel file. They're organized into several folders (Figure 1-17). To find the actual content from your spreadsheet, head to xl worksheets sheet1.xml. Double-click it to open it up and take a look at whats inside.
When you're finished, rename the file using its .xlsx extension so you can open it in Excel.

To learn way more about the technical details of this new type of file storage, you can read the Microsoft white paper at http://msdn2.microsoft.com/en-us/library/ms406049.aspx.

Figure 1-17. Inside every .xlsx file lurks a number of compressed files, each with different information. For example, separate files store printer settings, styles, the name of the person who created the document, the composition of your workbook, and each individual worksheet.

Note: Don't use the .xlsb format unless you've tried it out and find it really does give better performance for one of your spreadsheets. Usually, .xlsx and .xlsb are just as fast. And remember, the only time you'll see any improvement is when you're loading or saving a file. Once your spreadsheet is open in Excel, everything else (like scrolling around and performing calculations) happens at the same speed.

1.4.2. Saving Your Spreadsheet in Older Formats

Most of the time, you don't need to think about Excel's file formatyou can just create your spreadsheets, save them, and let Excel take care of the rest. The only time you need to stop and think twice is when you need to share your work with other, less fortunate people who have older versions of Excel.

When you find yourself in this situation, you have two choices:

Save your spreadsheet in the old format . You can save a copy of your spreadsheet in the traditional .xls Excel standard that's been supported since Excel 97. To do so, choose Office button Save As Excel 97-2003 Format.
Use a free add-in for older versions of Excel . People who are stuck with Excel 2000, Excel 2002, or Excel 2003 can read your Excel 2007 filesthey just need a free add-in that's provided by Microsoft. This is a good solution because it's doesn't require any work on your part. People with past-its-prime versions of Excel can find the add-in they need by surfing to www.microsoft.com/downloads and searching for "compatibility pack file formats" (or use the secret shortcut URL http://tinyurl.com/y5w78r).

Often, the best thing you can do is keep your spreadsheet in the newer format and save a copy in the older format (using Office button Save As Excel 97-2003 Format). You can then hand that copy out to your backward friends .

Some eccentric individuals have even older or stranger spreadsheet software on their computers. If you want to save a copy of your spreadsheet in a more exotic file type, you can choose Office button Save As, and then find the desired format in the "Save as type drop-down list (Figure 1-18). Excel lets you save your spreadsheet using a variety of different formats, including the classic Excel 95 format from a decade ago. If you're looking to view your spreadsheet using a mystery program, use the CSV file type, which produces a comma-delimited text file that almost all spreadsheet applications on any operating system can read (commadelimited means the information has commas separating each cell ).

Figure 1-18. Excel offers a few useful file type options in the "Save as type" list. CSV format is the best choice for compatibility with truly old software (or when nothing else seems to work). If you're a longtime Excel fan, you'll notice that the list has been slimmed down a bitfor example, there's no option to use the old dBase and Lotus formats from the DOS world.

Tip: When you save your Excel spreadsheet in another format, make sure you keep a copy in the standard .xlsx format. Why bother? Because other formats aren't guaranteed to retain all your information, particularly if you choose a format that doesn't support some of Excel's newer features.

1.4.2.1. Compatibility mode

There's one stumbling block that you can't avoid when dealing with older Excel versions. Each version of Excel introduces a small set of new features. Older versions of Excel don't support these features. For example, Excel 2007 introduces a few new formula functions like SUMIFS (Section 13.1.4). If you use this function to create a calculation, it won't work on older versions of Excel.

Excel tries to help you out by spotting and preventing potential problems. If you save a spreadsheet in the old .xls file format and that spreadsheet uses Excel 2007only features, Excel switches into compatibility mode . Excel also switches to compatibility mode when you open an .xls file.

Tip: You'll know you're in compatibility mode by looking at the title bar at the top of the Excel window. Instead of seeing something like CateringList.xlsx, you'll see CateringList.xls [Compatibility Mode].

In compatibility mode, Excel tries to stop you from using features that aren't supported on older Excel versions. For example:

It lets you use only a smaller grid of cells (65,536 rows instead of 1,048,576).
It prevents you from using really long or deeply nested formulas.
It doesn't let you use some pivot table features.

In compatibility mode these missing features aren't anywhere to be found. In fact, compatibility mode is so seamless that you might not even notice you're being limited.

1.4.2.2. The Compatibility Checker

Compatibility mode can't catch everything. For example, it doesn't stop you from using a function that's new to Excel 2007, like SUMIFS( ), a handy tool for calculating conditional sums (Section 13.1.4). And it's no help if you use Excel 2007-only features while editing a normal .xlsx file, and then save an .xls copy later on. In this situation, you don't enter compatibility mode until after the damage is done.

To catch problems like these, Excel has another tool, called the Compatibility Checker. Whenever you save your spreadsheet file to the .xls format, the Compatibility Checker runs first, looking for signs of trouble. It then reports any problems back to you (Figure 1-19).

Figure 1-19. When you save a spreadsheet in Excel 97-2003 format, the Compatibility Checker shows a list of problems that will affect users of Excel 2003, and the number of times each problem occurs. See the box in Section 1.4.2.2 for how the Compatibility Checker deals with even older versions of Excel.

You can choose to ignore the Compatibility Checker issues, click Find to hunt each one down, or click Help to figure out the exact problem. You can also click Copy to New Sheet to insert a full compatibility report into your spreadsheet as a separate worksheet (Section 4.1). This way, you can print it up and review it in the comfort of your cubicle . (To get back to the worksheet with your data, click the Sheet1 tab at the bottom of the window. Chapter 4 has more about how to use and manage multiple worksheets.)

Note: The problems that the Compatibility Checker finds won't cause serious errors, like crashing your computer or corrupting your data. That's because Excel is designed to degrade gracefully . That means you can still open a spreadsheet that uses newer, unsupported features in an old version of Excel. However, you may receive a warning message and part of the spreadsheet may seem brokenthat is, it doesn't work as you intended.

The Compatibility Checker is a great way to get an early warning about potential problems in sharing your spreadsheets. However, it works only if you choose to save your spreadsheet in the old .xls format. As you learned a bit earlier, there's another optionpeople who are using an older version of Excel can install a free add-in (Section 1.4.2) that allows them to open .xlsx files. Of course, this doesn't help you avoid the headaches caused by new features. If you use, say, Excel 2003 to open a .xlsx file with Excel 2007-only features, it's just the same as opening a .xls file with unsupported featurespart of your worksheet might not work the way it should. And these problems can creep up on you because when you save .xlsx files, you don't get any advance warning if you're using features that could cause a problem with older versions of Excel.

Fortunately, there's an easy solution. Even if you're saving your spreadsheets using the new and shiny .xlsx file format, you can still run the Compatibility Checker to see if your spreadsheet could cause a problem for people with an older version of Excel. To run the compatibility checker at will, choose Office button Prepare Run Compatibility Checker.

TROUBLESHOOTING MOMENT
Sharing with Excel 97, 2000, and 2002

The Compatibility Checker has a dirty secret. It's designed to catch problems that result from using Excel 2007 features that aren't supported on Excel 2003. However, the Compatibility Checker doesn't warn you about problems that may result if you use features that aren't supported in even older versions of Excel.

For example, if you create a spreadsheet that uses pivot charts (which are supported in Excel 2003), save it to the .xls format, and then open it in Excel 97, you'll notice something isn't quite right. You can see your pivot charts , but you can't manipulate them. Similar problems abound with other hot new features that made their appearance in Excel 2000, 2002, or 2003. These problems don't corrupt your files or cause your computer to fail, but they do cause your spread-sheets to lose some features in truly Paleolithic versions of Excel.

Tip: If you find yourself using the Compatibility Checker often, you can set it to run automatically when you save the current spreadsheet file. Just fire up the Compatibility Checker (using Office button

Prepare

Run Compatibility Checker), and then turn on the "Check compatibility when saving this workbook checkbox. Now, the Compatibility Checker will run each time you save your spreadsheet, before the file is updated, just as it does when you're saving an old-school .xls file.

1.4.3. Saving Your Spreadsheet As a PDF

Sometimes you want to save a copy of your spreadsheet so that people can read it even if they don't have Excel (and even if they're running a different operating system, like Linux or Apple's OS X). In this situation, you have several choices:

Use the Excel Viewer . Even if you don't have Excel, you can install a separate tool called the Excel Viewer, which is available from Microsoft's Web site (search for "Excel Viewer" at www.microsoft.com/downloads). However, few people have the viewer, and even though it's free, few want to bother installing it. And it doesn't work on non-Windows computers.
Save your workbook as an HTML Web page . That way, all you need to view the workbook is a Web browser (and who doesn't have one of those?). The only disadvantage is that you could lose complex formatting. Some worksheets may make the transition to HTML gracefully, while others don't look very good when they're squashed into a browser window. And if you're planning to let other people print the exported worksheet, the results might be unsatisfactory. Chapter 26 has more about saving your worksheet as a Web page.
Save your workbook as a PDF file . This gets you the best of both worldsyou keep all the rich formatting (so your workbook can be printed), and you let people who don't have Excel (and possibly don't even have Windows) view your workbook. The only disadvantage is that this feature isn't included in the basic Excel package. Instead, you need to install a free add-in to get it.

To get the Save As PDF add-in, surf to www.microsoft.com/downloads and search for "PDF." The links lead you to a page where you can download the add-in and install it with just a couple of clicks.

Note: There's a variation of the Save As PDF add-in, which is named "Save As PDF or XPS." This variation gives you the ability to save spreadsheets as PDF files or XPS files. (XPS is Microsoft's new electronic paper standard, as described in the "Learning to Love PDFs" box in Section 1.4.3.)

Once you install the Save As PDF add-in, all your Office applications have the ability to save their documents in PDF format. In Excel, you work this magic by choosing Office button Save As PDF, which brings up the "Publish as PDF dialog box (Figure 1-20).

UP TO SPEED
Learning to Love PDFs

You've probably heard about PDFs, Adobe's popular format for sharing formatted, print-ready documents. People use PDFs to pass around product manuals, brochures , and all sorts of electronic documents. Unlike a document format like .xlsx, PDF files are designed to be viewed and printed, but not edited.

The best part about PDFs is that you can view them on just about any type of computer and operating system using the free Adobe Reader. You can download Adobe Reader at www.adobe.com/products/acrobat/readstep2.html, but you probably don't need to. Most computers already have Adobe Reader installed, because it comes bundled with so many different programs (usually so you can view their electronic documentation). It's also widely used on the Web.

Incidentally, PDF isn't the only kid on the block. Microsoft's newest operating system, Windows Vista, includes its own electronic paper format called XPS (XML Paper Specification). In time, as XPS is integrated into more and more products, it might become a true PDF competitor. But for now, PDF is dramatically more popular and widespread, so it's the one to stick with. (If you're interested in saving an Excel document as a XPS file, you can do that toojust point your Web browser to www.microsoft.com/downloads and search for "XPS" to find the right add-in.)

Figure 1-20. The "Publish as PDF" dialog box looks a lot like the Save As dialog box, except it has a Publish button instead of a Save button. You can switch on the "Open file after publishing" setting to tell Excel to open the PDF file in Adobe Reader ( assuming you have it installed) after the publishing process is complete, so you can check the result.

When you save a PDF file, you get a few extra options in the Save As dialog box. PDF files can be saved with different resolution and quality settings (which mostly affect any graphical objects that you've placed in your workbook, like pictures and charts). Normally, you use higher quality settings if you're planning to print your PDF file, because printers use higher resolutions than computer monitors .

The "Publish as PDF" dialog box gives you some control over the quality settings with the "Optimize for" options. If you're just saving a PDF copy so other people can view the information in your workbook, choose "Minimum size (publishing online)" to save some space. On the other hand, if there's a possibility that the people reading your PDF might want to print it out, choose "Standard (publishing online and printing)" to save a slightly larger PDF that makes for a better printout.

Finally, if you want to publish only a portion of your spreadsheet as a PDF file, click the Options button to open a dialog box with even more settings. You can choose to publish just a fixed number of pages, just the selected cells, and so on. These options mirror the choices you get when sending a spreadsheet to the printer (Section 7.2.1). You also see a few more cryptic options, most of which you can safely ignore. (They're intended for PDF nerds.) One exception is the "Document properties" optionturn this off if you don't want the PDF to keep track of certain information that identifies you, like your name. (Excel document properties are discussed in more detail in Section 23.1.1.)

Tip: Getting the Save As PDF add-in is a bit of a hassle, but it's well worth the effort. In previous versions of Excel, people who wanted to create PDFs file had to get another add-in or buy the expensive full version of the Adobe Acrobat software. The Save As PDF feature was originally slated for inclusion in Excel (with no add-in required), but anti-trust concerns caused ultra -cautious Microsoft to leave it out.

1.4.4. Saving Your Spreadsheet with a Password

Occasionally, you might want to add confidential information to a spreadsheetfor example, a list of the airlines from which you've stolen spoons. If your computer is on a network, the solution may be as simple as storing your file in the correct, protected location. But if you're afraid that you might inadvertently email the spreadsheet to the wrong people (say, executives at American Airlines), or if you're about to expose systematic accounting irregularities in your company's year-end statements, you'll be happy to know that Excel provides a tighter degree of security. It allows you to password-protect your spreadsheets, which means anyone who wants to open them has to know the password you've set.

Excel actually has two layers of password protection that you can apply to a spreadsheet:

You can prevent others from opening your spreadsheet unless they know the correct password. This level of security, which scrambles your data for anyone without the password (a process known as encryption ), is the strongest.
You can let others read a spreadsheet, but you can prevent them from modifying it unless they know the correct password.

You can apply one or both of these restrictions to a spreadsheet. Applying them is easy. Just follow these steps:

Select Office button Save As .

The Save As dialog box appears.
Click the Tools button, and then, from the pop-up menu, choose General Options .

If you're using a Windows XP computer, you'll find the Tools button in the bottom-left corner of the Save As dialog box. But if you're running Windows Vista, it's at the bottom right, just next to the Save button.

The General Options dialog box appears.

Type a password next to the security level you want to turn on (as shown in Figure 1-21). Then click OK .

The General Options dialog box also gives you a couple of other unrelated options:

Turn on the "Always create backup" checkbox if you want an extra copy of your file, just in case something goes wrong. (Think of it as insurance.) Excel creates a backup that has the file extension . xlk . For example, if you're saving a workbook named SimpleExpenses.xlsx and you use the "Always create backup" option, Excel creates a file named "Backup of SimpleExpenses.xlk " every time you save your spreadsheet. You can open the .xlk file in Excel just like an ordinary Excel file. When you do, you see that it has an exact copy of your work.
Turn on the "Read-only recommended" checkbox to prevent other people from accidentally making changes to your spreadsheet. When you use this option, Excel shows a message every time you (or anyone else) opens the file. This message politely suggests that you open the spreadsheet in read-only mode , in which case Excel won't allow any changes. Of course, it's entirely up to the person opening the file whether to accept this recommendation.

Figure 1-21. You can use any sequence of letters and numbers as a password. Passwords are case-sensitive (which means that PanAm is different from panam), and they are masked (which means that all that appears in the window as you type is a series of asterisks ).

Click Save to store the file .

If you use a password to restrict people from opening the spreadsheet, Excel prompts you to supply the "password to open" the next time you open the file (Figure 1-22, top).

Figure 1-22. Top: You can give a spreadsheet two layers of protection: assign a "password to open" and you'll see this window when you open the file.
Bottom: If you assign a "password to modify," you'll see the choices in this window. If you use both passwords, you'll see both windows, one after the other.

If you use a password to restrict people from modifying the spreadsheet, the next time you open this file you'll be given the choiceshown in Figure 1-22, bottomto open it in read-only mode (which requires no password) or to open it in full edit mode (in which case you'll need to supply the "password to modify").

1.4.5. Disaster Recovery

The corollary to the edict "Save your data early and often" is the truism "Sometimes things fall apart quickly before you've even had a chance to back up." Fortunately, Excel includes an invaluable safety net called AutoRecover.

AutoRecover periodically saves backup copies of your spreadsheet while you work. If you suffer a system crash, you can retrieve the last AutoRecover backup even if you never managed to save the file yourself. Of course, even the AutoRecover backup won't necessarily have all the information you entered in your spreadsheet before the problem occurred. But if AutoRecover saves a backup every 10 minutes (the standard), at most you'll lose 10 minutes of work.

AutoRecover comes switched on when you install Excel, but you can tweak its settings. Select Office Excel Options, and then choose the Save section. Under the "Save workbooks section, make sure that "Save AutoRecover information" is turned on. You can also make a few other changes to AutoRecover settings:

You can also adjust the backup frequency in minutes. (See Figure 1-23 for tips on timing.)

You can choose the folder where you'd like Excel to save backup files. (The standard folder works fine for most people, but feel free to pick some other place.) Unfortunately, there's no handy Browse button to help you find the folder, so you need to find the folder you want in advance (using a tool like Windows Explorer), write it down somewhere, and then copy the full folder path into this dialog box.

Figure 1-23. You can configure how often AutoRecover saves backups . There's really no danger in being too frequent. Unless you work with extremely complex or large spreadsheetswhich might suck up a lot of computing power and take a long time to saveyou can set Excel to save the document every five minutes with no appreciable slowdown .

Under the "AutoRecover exceptions" heading, you can tell Excel not to bother saving a backup of a specific spreadsheet. Pick the spreadsheet name from the list (which shows all the currently open spreadsheet files), and then turn on the "Disable AutoRecover for this workbook only" setting. This setting is exceedingly uncommon, but you might use it if you have a gargantuan spreadsheet full of data that doesn't need to be backed up. For example, this spreadsheet might hold records that you've pulled out of a central database so you can take a closer look. In this case, there's no need to create a backup because your spreadsheet just has a copy of the data that's in the database. (If you're interested in learning more about this scenario, check out Chapter 25.)

If your computer does crash, when you get it running again, you can easily retrieve your last AutoRecover backup. In fact, the next time you launch Excel, it automatically checks the backup folder, and, if it finds a backup, it opens a Document Recovery panel on the left of the Excel window.

If your computer crashes in mid-edit, the next time you open Excel you'll probably see the same file listed twice in the Document Recovery window, as shown in Figure 1-24. The difference is the status. The status [AutoSaved] indicates the most recent backup created by Excel. The status [Original] indicates the last version of the file that you saved (which is safely stored on your hard drive, right where you expect it).

Figure 1-24. You can save or open an AutoRecover backup just as you would an ordinary Excel file; simply click the item in the list. Once you've dealt with all the backup files, close the Document Recovery window by clicking the Close button. If you haven't saved your backup, Excel asks you at this point whether you want to save it permanently or delete the backup.

To open a file that's in the Document Recovery window, just click it. You can also use a drop-down menu with additional options (Figure 1-24). Make sure you save the file before you leave Excel. After all, it's just a temporary backup.

If you attempt to open a backup file that's somehow been scrambled (technically known as corrupted ), Excel automatically attempts to repair it. You can choose Show Repairs to display a list of any changes Excel had to make to recover the file.