Excel 2003 for Starters: The Missing Manual

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 isn't currently visible, you can always choose View → Task Pane from the menu.

Both of these methods bring up the Open dialog box. Using this dialog box, you can browse to find the spreadsheet file you want and then click Open to load it into Excel.

Excel can open many file types other than its native .xls format. To learn the other formats it supports, pull up the Open dialog box, and, at the bottom, open the "Files of type" menu, which shows you the whole list. If you want to open a file but you don't know what format it's in, try using the first option on the menu, "All Files." Once you choose a file, Excel scans the beginning of the file and informs you about the type of conversion it will attempt to perform (based on what type of file Excel thinks it is).

Note

Depending on your computer settings, Windows may hide file extensions. That means that instead of seeing the Excel spreadsheet file MyCoalMiningFortune.xls, you 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.

GEM IN THE ROUGH: Opening Files—with 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 household budget as a starting point for this month's budget, 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 changes—you 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 is 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.

* 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 want to attempt only when trying to convert Excel files to Web pages.

* 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 may not lose any information at all.

When you open a file or save a file for the first time, Excel starts you off in the My Documents folder. This folder is Windows-specific, as many programs assume you use Windows 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 box—instead, you need to type it in by hand.

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-20, top).

If you're using Windows XP, you may find that your computer has an odd habit of spontaneously bunching together taskbar buttons. For example, shortly after you open three Excel files, you may find them in one taskbar button cleverly named 3 Microsoft Office Excel (see Figure 1-20, bottom).

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 one: the first to click the taskbar button, and the second to choose the window you want from the group.

Figure 1-20. Top: When you have multiple spreadsheets open at the same time, you can easily move from one to the other by clicking their file names in the taskbar (at the bottom of the screen). Bottom: In Windows XP, similar taskbar buttons sometimes get bunched into groups.

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 an empty space in the taskbar and choose Properties. In the "Taskbar and Start Menu Properties" dialog box that appears, turn off the checkbox 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-21).

• To move to the next spreadsheet, use the keyboard shortcut Ctrl+Tab or Ctrl+F6.

• To move to the previous spreadsheet, use the shortcut keys Ctrl+Shift+Tab or Ctrl+Shift+F6.

Figure 1-21. When you have multiple spreadsheets open at the same time (even ones with long names), you can easily move from one to the other using the Window menu in Excel.

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 application—unless 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 on 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.

Searching for Files

Modern hard drives hold dozens of gigabytes, layers and layers of subfolders— and 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.

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 criteria, such as who created a file and when. This technique is helpful if you 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, such as "Author," "Creation date," or "File name"); select the type of Condition (the type of comparison you're performing, such as "is not," "includes," or "equals"); and then type in the Value (the word or number you're hoping to find). For example, to find spreadsheet files created by Samuel Dent, you would choose Author for the Property, is for the Condition, and Samuel 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. Indexing 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. 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).

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

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

2. Type the words you want to search for in the "Search text" box.

   You can type in 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 type in, 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.

3. 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-22, left), 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 may 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-22. 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, Excel will examine the My Documents folder. Right: The file type list lets you specify what kinds of files you're looking for. 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're also included.

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

   As shown in Figure 1-22 (right), 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.

5. 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-23). If Excel finds no results, the search ends by displaying the message "No Results Found."

   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.

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

Figure 1-23. 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.

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.

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 file, such 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.