Section 1.5. Opening Files | Excel 2010: The Missing Manual

1.5. Opening Files

Opening existing files in Excel works much the same as it does in any Windows program. The only difference is that Excel gives you two different ways to get to the standard Open dialog box. Here are your options:

Select File Open.
Use the Task Pane (Section 1.3.2). In Excel 2003, click the Open link at the bottom of the Getting Started task. Or, in Excel 2002, look under the "Open a workbook" heading in the New Workbook task, and click the "More workbooks" link.

Note: If the Task Pane is not currently visible, you can always choose View

Task Pane from the menu.

Figure 1-20. Top : Every time Excel starts up, it looks for AutoRecover backups . If it finds a backup, that means a document was not properly saved the last time you exited from Excel. Excel then opens a bar at the side of your window with all the backup files it finds, and lists their statuses. [Recovered] means the file backup is ready and error-free, while [Original] indicates the file was saved by you, and Excel didn't make a backup after your last save. (The only time you'll see the original file in the Document Recovery window is when Excel (or your computer) crashes, but there isn't any unsaved data or backup file. In this situation, you don't need to worry since you haven't lost any data.)
Bottom : You can save or open an AutoRecover backup like an ordinary Excel file; simply click the item in the list. Once you've dealt with all of the backup files, close the Document Recovery window by clicking the Close button.

Note: Depending on your computer settings, Windows might hide file extensions. That means that instead of seeing the Excel spreadsheet file MyCoalMiningFortune.xls , you'll just see the name MyCoalMiningFortune (without the .xls part on the end). In this case, you can still tell what the file type is by looking at the icon. If you see a small Excel icon next to the file name, that means Windows recognizes that the file is an Excel spreadsheet. If you see something else (like a tiny paint palette, for example), you need to make a logical guess about what type of file it is.

When you open a file or save a file for the first time, Excel starts you off in the My Documents folder. This is a Windows-specific folder that many programs assume you use for all your files. If you don't use My Documents, you can tell Excel to look elsewhere when saving and opening files. To do so, select Tools Options. In the Options dialog box, click the General tab. You can modify the "Default file location" text box so that it points to the folder where you usually store files (as in c:\John Smith\MyExcel Files ). Sadly, you can't browse and pick the path from a dialog boxinstead, you need to type it in by hand.

GEM IN THE ROUGH
Opening Fileswith a Twist

The Open dialog box harbors a few tricks. To see these hidden secrets, first select the file that you want to use (by clicking it once, not twice), and then click the drop-down arrow on the right-side of the Open button. A menu with several additional options appears, as shown here.

Here's what these different choices do:

Open opens the file in the normal way.
Open Read-Only opens the file, but won't allow you to save changes. This option is great if you want to make sure you don't accidentally overwrite an existing file. (For example, if you're using last month's sales invoice as a starting point for this month's sales invoice, you might use Open Read-Only to make sure you can't accidentally wipe out the existing file.) If you open a document in read-only mode, you can still make changesyou just have to save the file with a new file name (choose File Save As).
Open as Copy creates a copy of the spreadsheet file in the same folder. If your file is named Book1.xls, the copy will be named Copy of Book1.xls. This feature comes in handy if you're about to start editing a spreadsheet and want to be able to look at the last version you saved. Excel won't let you open the same file twice. However, you can load the previous version by selecting the same file and using Open as Copy. (Of course, this technique only works when you have changes you haven't saved yet. Once you save the current version of a file, the older version is overwritten and lost forever.)
Open in Browser is only available when you select an HTML file. This option allows you to open a spreadsheet for viewing in your computer's Web browser (usually Internet Explorer), which is something you'll only want to attempt when trying to convert Excel files to Web pages (Section 24.1).
Open and Repair is useful if you need to open a file that is corrupted. If you try to open a corrupted file by just clicking Open, Excel warns you that the file has problems and refuses to open it. To get around this problem, you can open the file using the Open and Repair option, which prompts Excel to make the necessary corrections, display them for you in a list, and then open the document. Depending on the type of problem, you might not lose any information at all.

There's one other interesting option in the General tab. You can use the "At startup, open all files in" text box to specify a folder where you put all the Excel files you're currently working with. Then, the next time you start Excel, it automatically opens every .xls file it finds in a separate Excel window. Of course, if you decide to use this option, make sure you don't clutter your in-progress folder with too many files, or Excel will open a dizzying number of windows when it starts.Opening Multiple Spreadsheets at Once

As you open multiple spreadsheets, Excel creates a new window for each one. You can easily jump from one spreadsheet to another by clicking the appropriate spreadsheet button in the Windows taskbar at the bottom of your screen (see Figure 1-21, top).

If you're using Windows XP, you'll find that your computer has an odd habit of spontaneously bunching together taskbar buttons . For example, shortly after you open three Excel files, you might find them in one task bar button (see Figure 1-21, bottom).

Figure 1-21. Top : When you have multiple spreadsheets open at the same time, you can easily move from one to the other using the taskbar.
Bottom : In Windows XP, similar taskbar buttons sometimes get bunched into groups. You can tell that a button contains a group of files when a drop-down arrow appears on the right side of the button, and a number appears on the left side. The number indicates how many buttons Windows has grouped together.

Automatic taskbar bunching does save screen space, but it also makes it a little more awkward to get to the Excel spreadsheet you want. You now need two mouse clicks instead of onethe first to click the taskbar button, and the second to choose the window you want from the group.

Tip: If the taskbar bunching in Windows XP seems like more trouble than it's worth, you can switch off this behavior. Just right-click on an empty space in the taskbar and choose Properties. In the Taskbar and Start Menu Properties dialog box that appears, clear the checkmark next to the "Group similar taskbar buttons" option.

The taskbar, though convenient , isn't perfect. One problem is that long file names don't fit on the taskbar buttons, which can make it hard to spot the files you need. And the struggle to find an open file becomes dire if your taskbar is also cluttered with other applications and their multiple windows.

Fortunately, Excel provides a couple of shortcuts that are indispensable when dealing with several spreadsheets at a time:

To jump from one spreadsheet to another, pick the spreadsheet from Excel's Window menu, which lists the full file name of all the currently open spreadsheets (Figure 1-22).
To move to the next spreadsheet, use the keyboard shortcut Ctrl+Tab or Ctrl+F6.
To move to the previous spreadsheet, use the shortcut key Ctrl+Shift+Tab or Ctrl+Shift+F6.

Figure 1-22. When you have multiple spreadsheets open at the same time, you can easily move from one to the other using the Window menu in Excel. The Window menu has the advantage of always showing the full file name.

When you have multiple spreadsheets open at the same time, you need to take a little more care when closing a window so you don't accidentally close the entire Excel applicationunless you want to. Here are your choices:

You can close all the spreadsheets at once . To do so, you need to close the Excel window. Select File Exit from the menu in any active spreadsheet.
You can close a single spreadsheet . To do so, right-click the spreadsheet on the taskbar, and click Close. Or, switch to the spreadsheet you want to close (by clicking the matching taskbar button) and then choose File Close from the Excel menu.

Note: One of the weirdest limitations in Excel occurs if you try to open more than one file with the same name. No matter what steps you take, you can't coax Excel to open them both. It doesn't matter if the files have different content or if they're in different folders or even different drives . When you try to open a file that has the same name as a file that's already open, Excel displays an error message and doesn't do anything. Sadly, the only solution is to open the files one at a time, or rename one of them.

1.5.1. Searching for Files

Modern hard drives hold dozens of gigabytes, layers and layers of subfoldersand files that wind up strewn everywhere. Misplacing a file in a subfolder is easier than spilling coffee on your keyboard and can lead to a mad panic the next time you try to find the document.

Windows includes tools for searching your hard drive, but they don't always work with all types of content. Excel goes one step further by including its own tool that is fine- tuned for searching Office files. Using it, you can hunt for spreadsheet files in specific locations, containing specific text.

To use Excel's file search feature, follow these steps:

If you're using Excel 2003, Select File File Search. In Excel 2002, click the drop-down arrow in the Task Pane, and choose the Search task.

The Basic File Search task appears in the Task Pane.
Enter the words you want to search for in the "Search text" box.

You can enter one or more words to search for. For example, you could try airline or silverware or airline silverware . Bear in mind that the more words you enter, the more specific your search and the more likely you are to find a relevant match. In addition, you can use the ? and * characters as wildcards , which are symbols that stand in for unknown text and can really enhance a search. In Excel's search, the asterisk (*) represents a group of one or more characters. For example, a search for s*nd matches documents that contain sand, sound, send, or even the bizarre series of characters sgrthdnd. The question mark (?) represents any single character. For example, f?nd matches documents that contain find or fund but not friend.

Note: Excel automatically searches for all grammatical forms of the word you type in, if it recognizes the word. For example, if you try catch , Excel matches words like "catching" and "caught" even though they don't match exactly.

From the "Search in" drop-down list, choose the locations where you want to search.

When you expand the "Search in" list, Excel shows you a tree of drives and folders on your computer (Figure 1-23, top), which is similar (though not identical) to the tree in Windows Explorer. Expand the appropriate drives where you want to search (click them or the plus signs next to them), and choose the folders that might have the file you're looking for by clicking them. In general, a good place to search is the My Documents folder (under My Computer), which is a standard place to store documents and which tends to junk up and become Land of the Lost.

Figure 1-23. Left : In all searches, you need to tell Excel where you want to search, and what type of files you want to find. In this example, the file search will examine the root directory of drive C: (note the ordinary checkmark), but it won't look in any subdirectories. On the other hand, it will search the My Document folder (note the checkmark with multiple boxes) and all contained subdirectories.
Right : The file type list is more straightforward. This search will only find Excel files.

Excel gives you two ways to select a folder. Click once to place a checkmark next to the folder. This sign indicates that the search will include the selected folder, but it won't branch out to cover subfolders . Click twice to place a checkmark with multiple boxes underneath it. This icon indicates that the search will include the selected folder and all the subfolders it contains. If you expand the folder, you can see that all the subfolders now have a checkmark icon to indicate they are also included.

From the "Results should be" drop-down list, choose the types of files you want to search for.

As shown in Figure 1-23 (bottom), Excel distinguishes between three main categories of files: Office documents, Web pages, and Outlook items (like email messages). Usually, Excel automatically includes Web pages and common Office document formats (Word documents, spreadsheets, PowerPoint presentations, and so on) in a search. In most cases, however, you're only interested in Excel spreadsheets. Clear all the other checkboxes to speed up your search and reduce false matches.

Click Go to start the search.

The Task Pane switches to the Search Results task, which shows the current search progress and the list of results that Excel has found so far (Figure 1-24). If Excel finds no results, the search ends by displaying the message "No Results Found."

Figure 1-24. Left : In this example, Excel is performing a search for all spreadsheets that contain the word "price" somewhere in the worksheet.
Right : When you click Go, the Search Results pane appears. In this example, Excel has found two documents so far, and the search is still in progress.

Note: You can click Stop at any time to abort the current search. To change the search, click Modify to go back to the Basic File Search task.

If Excel finds files, select one from the search results, and open it.

If your search has turned up some results, you can open them directly from the Search Results task. Simply click the file once. If your results contain non-Excel files, when you click one, the appropriate program opens automatically. For example, if you click a Word document, a new Word window opens with the file.

If your search turns up a large number of results, Excel doesn't show them all at once. Instead, it includes a link at the bottom of the result list indicating how many results remain to be viewed (for example, "Next 17 results"). Click this link to show the next page of results.

Tip: To get more information about a file in the Search Results task window, just hover over it with the mouse. A tooltip appears, detailing exactly where the file is on your hard drive.

POWER USERS' CLINIC
Advanced Searching

Excel provides an advanced search feature that gives you more control over search details, allowing you to narrow down the search results based on additional criteriasuch as who created a file and when. This technique is helpful if you're an extremely prolific Excel guru and you might need to search through hundreds of files to find a match.

To use it, open the Basic Files Search task window (choose File Search), and at the bottom, click the Advanced File Search link. A similar, but slightly more complex task window appears.

To specify detailed search conditions, choose a Property (the piece of information you want to match), select the type of Condition (the type of comparison you're performing), and then enter the Value (the piece of information that's being compared to the spreadsheet data). For example, to find spreadsheet files created by Arthur Dent, you would choose Author for the Property, is for the Condition, and Arthur Dent for the Value. Finally, click Add to include your condition in the list of search conditions. You can repeat this process to add as many different conditions as you want. When you're finished, click Go to launch the search, which switches you to the familiar Search Results task window.

You can also increase the speed of searches by indexing your files. An index is a catalog of all the files on your computer, which includes various pieces of information about each file (like who created it and when it was created). When you search indexed files, Excel can scan the index to find a match instead of chewing through your entire hard drive. This speeds up the whole search process.

In order to make sure files are indexed, you need to use a special utility called the Indexing Service. This service can be set to run quietly and permanently in the background, creating and updating its catalog every time you create or change a file. To turn on the Indexing Service, open the Basic File Search task window, click the Search Options link, and then choose "Yes, enable Indexing Service". Of course, all good things come with a price, and the Indexing Service can conceivably slow down the performance of your computer (but not a lot).

Instead of opening the file, you can choose to copy the file path to the clipboard (which is useful if you want to open it in another program), or open the Properties window that shows information about the filesuch as its size and author. To open a menu with these options, hover over the file in the search results list, and then click the drop-down arrow that appears to the right of the file.