O'Reilly logo

Excel 2003: The Missing Manual by Matthew MacDonald

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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.

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

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.

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

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.

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 one—the 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.

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.

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

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

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

    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.

    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.

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

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

    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.

    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.

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

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required