O'Reilly logo

Excel 2003 for Starters: 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

Chapter 1. Creating and Navigating Worksheets

The best way to avoid potential headaches is to take a quick tour of Excel as you start creating a spreadsheet. That’s what you do in this chapter. Along the way, you learn how to enter information in the Excel window and how to open and save spreadsheet files.

Creating a Basic Worksheet

When you first launch Excel, it starts you off with a new, blank worksheet called Sheet1. A worksheet is the grid of cells where you type your information and formulas, and it takes up most of the window, as shown in Figure 1-1. This grid is the most important part of the Excel window. It’s where you perform all your work, such as entering data, writing formulas, and reviewing the results. (A collection of one or more worksheets is called a workbook, which is also sometimes called a spreadsheet file).

Here are a few basics about Excel’s grid:

  • The grid divides your worksheet into rows and columns. Excel identifies columns with letters (A, B, C …), and rows with numbers (1, 2, 3 …).

  • The smallest unit in your worksheet is the cell . Cells are the rectangular boxes that store your text or numbers. Excel identifies each cell using a shorthand name derived from the column and row it’s sitting in. For example, C6 is the address of a cell in column C (the third column), and row 6 (the sixth row). Figure 1-2 shows this cell, which looks like a rectangular box. Incidentally, an Excel cell can hold up to 32,767 characters.

  • A worksheet can span up to 256 columns and 65,536 rows (giving you a grand total of 16,777,216 cells). In the unlikely case that you want to go beyond those limits—say you’re naming each one of your brain cells—you need to create a new worksheet. Every spreadsheet file can hold multiple worksheets, as you’ll see in Chapter 5.

  • When you enter information, you type it in one cell at a time. However, you don’t have to follow any set order. For example, you can start by typing information into cell A40, without worrying about filling any data in the cells that appear in the earlier rows.

The Excel window has several parts, most notably the worksheet grid where you type in your information.
Figure 1-1. The Excel window has several parts, most notably the worksheet grid where you type in your information.

Note

There is definitely, absolutely no way around the 256-column and 65,536-row limits. It’s encouraging to note that if you fill in one cell per second, you’ll need about 200 days to fill up your worksheet, assuming you don’t break for sleep, food, or coffee.

The best way to get a feel for Excel is to dive right in and start putting together a worksheet. The following sections cover each step that goes into assembling a simple worksheet. This worksheet will track household expenses, but you can use the same approach to create any basic worksheet.

In this figure, the current cell is C6. You can recognize the current (or active) cell by its heavy black border.
Figure 1-2. In this figure, the current cell is C6. You can recognize the current (or active) cell by its heavy black border.

Starting a New Worksheet

When you fire up Excel, it opens a fresh workbook. If you’ve already got Excel open and you want to create another workbook, just select File → New. Contrary to what you’d expect, this step doesn’t actually create the new file. Instead, it pops up the New Workbook task in the Task Pane (if that isn’t already visible). To finish the job, you need to click the “Blank workbook” link.

Note

Creating new workbooks doesn’t disturb what you’ve already done. Whatever workbook you were currently using remains open in another window. (Check the Windows taskbar at the bottom of your screen to move from one workbook to the other.)

Adding the Column Titles

The most straightforward way to create a worksheet is to design it as a table with headings for each column. Even for the simplest worksheet, the decisions you make about what’s going to go in each column can have a big effect on how easy it is to manipulate your information.

For example, in a worksheet that stores a mailing list, you could have only two columns: one for names and another for addresses. But if you create more than two columns, your life will probably be easier, since you can separate first names from street addresses from Zip codes, and so on. Having multiple columns means that you can sort (reorganize) your spreadsheet information in lots of ways: by last name, by state, or however you like. Figure 1-3 shows the difference.

Top: Two columns limits your sorting options.Bottom: More columns gives you more flexibility.
Figure 1-3. Top: Two columns limits your sorting options. Bottom: More columns gives you more flexibility.

You can, of course, always add or remove columns later. But you can avoid a lot of tedious cutting and pasting by starting a worksheet with all the columns you think you’ll need.

The first step in creating your worksheet is to add your headings in the row of cells at the top of the worksheet (row 1). Technically, you don’t need to start right in the first row, but unless you want to add more information above your headings—like a title for the chart or the date you’re creating it—there’s no point in wasting the blank space.

For a simple expense worksheet designed to keep a record of your most prudent and extravagant purchases, try the following three headings:

  • Date Purchased stores the date when you spent the money.

  • Item stores the name of the product that you bought.

  • Price records how much it cost.

Right away, you face your first glitch: awkwardly crowded text. (The standard width of an Excel column is a paltry 8.43 characters.) To widen columns, as shown in Figure 1-4:

  1. Position your mouse on the right border of the column header you want to expand.

    The mouse pointer changes to the resize icon (it looks like a double-headed arrow).

  2. Drag the column border to the right as far as you want.

    As you drag, a tooltip appears, telling you the character size and pixel width of the column. Both of these pieces of information play the same role—telling you how wide the column is. Only the unit of measurement changes.

Top: To widen a column, position your mouse on the right border of the column header you want to expand, and then drag right.Bottom: When you release the mouse, Excel resizes the entire column of cells to the new size.
Figure 1-4. Top: To widen a column, position your mouse on the right border of the column header you want to expand, and then drag right. Bottom: When you release the mouse, Excel resizes the entire column of cells to the new size.

Note

A column’s character width doesn’t really reflect how many characters (or letters) fit in a cell. Modern versions of Excel (including Excel 2002 and 2003) use proportional fonts, in which different letters take up different amounts of room. For example the letter W is typically much wider than the letter I. All this means is that the character width Excel shows you isn’t a real indication of how many letters can fit in the column, but it’s still a useful measurement that you can use to compare different columns.

Adding Data

You can now begin adding your data: simply fill in the rows under the column titles. To do so, just click in any cell you want to add data to, and then start typing. When you’re finished with that cell, move onto the next cell by clicking in it. Keep repeating this enter-info-then-click tango till you’re done adding all your data.

Note

You can also move from cell to cell in a row by pressing the Tab key.

Each row in the expense worksheet represents a separate purchase that you’ve made. (If you’re familiar with databases, you can think of each row as a separate record.) As Figure 1-5 shows, the first column is for dates, the second column is for text, and the third column holds numbers. Keep in mind that Excel doesn’t impose any rules on what you type, so you’re free to put text in the Price column. But if you don’t keep a consistent kind of data in each column, you won’t be able to easily analyze (or understand) your information later.

This rudimentary expense list has three items (in rows 2, 3, and 4).
Figure 1-5. This rudimentary expense list has three items (in rows 2, 3, and 4).

Note

As you may notice in Figure 1-5, the alignment of each column reflects the data type: numbers and dates are right-aligned, while text is left-aligned. Section 4.2.1 shows you how to change this alignment if you don’t like the standard alignment settings that Excel uses.

That’s it. You’ve created a living, breathing worksheet. The next two sections explain how to edit data and move around the grid.

Editing Data

Every time you start typing in a cell, Excel erases any existing content in that cell. (You can also quickly remove the contents of a cell by just moving to it and pressing Delete.)

If you want to edit cell data instead of just replacing it, you need to put the cell in edit mode, like this:

  1. Move to the cell you want to edit.

    Use the mouse or the arrow keys to get to the correct cell.

  2. Put the cell in edit mode by pressing F2.

    Edit mode looks almost the same as ordinary text-entry mode. The only difference is that you can use the arrow keys to move through the text you’re typing and make changes. (When you aren’t in edit mode, pressing these keys just moves you to another cell.)

    If you don’t want to use F2, you can also place a cell in edit mode by double-clicking it.

  3. Complete your edit.

    Once you’ve modified whatever’s in the cell, you can press Enter to commit your change or Esc to cancel your edit and leave the old value in the cell. Alternatively, you can turn off edit mode (press F2 again) and then move to a new cell to commit your change. As long as you stay in edit mode, Excel won’t let you move to another cell.

    Tip

    If you start typing new information into a cell and you decide you want to move to an earlier position in your entry (to make an alteration, for instance), just press F2. The cell box still looks the same, but you’re now in edit mode, which means that you can use the arrow keys to move within the cell (instead of moving from cell to cell). You can press F2 again to return to the normal data-entry mode.

    As you enter data, you may discover the Bigtime Excel Display Problem (known to aficionados as BEDP): cells in adjacent columns can overlap one another. Figure 1-6 shows the problem. One way to fix the BEDP is to manually resize the column, as shown in Figure 1-4. Chapter 2 tackles editing in more detail. Chapter 4 shows you how to wrap multiple lines of text in a single cell.

If you type a large amount of text into A1 and then type some text into B1, you only see part of the data in A1 on your worksheet (as shown here). Resizing column A fixes this problem.
Figure 1-6. If you type a large amount of text into A1 and then type some text into B1, you only see part of the data in A1 on your worksheet (as shown here). Resizing column A fixes this problem.

Navigating in Excel

Learning how to move around the Excel grid quickly and confidently is an indispensable skill. To move from cell to cell, you have two basic choices:

  • Use the arrow keys on the keyboard. Keystrokes move you one cell at a time in any direction.

  • Click the cell with the mouse. A mouse click jumps you directly to the cell you’ve clicked.

As you move from cell to cell, you see the black focus box move to highlight the currently active cell.

In some cases, you may want to cover ground a little more quickly. You can use any of the shortcut keys listed in Table 1-1. The most useful shortcut keys include the Home key combinations, which bring you back to the beginning of a row or the top of your worksheet.

Note

Shortcut key combinations that use the + sign must be entered together. For example, "Ctrl+Home” means hold down the Ctrl key and press the Home key at the same time. Other key combinations work in sequence. For example, the key combination “End, Home” means press End first, take your finger off it, and then press Home.

Table 1-1. Shortcut Keys for Moving Around a Worksheet

KEY COMBINATION

RESULT

→ (or Tab)

Moves one cell to the right.

← (or Shift+Tab)

Moves one cell to the left.

Moves one cell up.

↓ (or Enter)

Moves one cell down.

Page Up

Moves up one screenful. Thus, if the grid shows 10 cells at a time, this key moves to a cell in the same column, 10 rows up (unless you’re already at the top of the worksheet).

Page Down

Moves down one screenful. Thus, if the grid shows 10 cells at a time, this key moves to a cell in the same column, 10 rows down.

Home

Moves to the first cell (column A) of the current row.

Ctrl+Home

Moves to the first cell in the top row, which is A1.

Ctrl+End (or End, Home)

Moves to the last column of the last occupied row. This cell is at the bottom-right edge of your data.

Excel also lets you cross great distances in a single bound using a Ctrl+arrow-key combination. These key combinations jump to the edges of your data. Edge cells include cells that are next to other blank cells. For example, if you press Ctrl+ → while you’re inside a group of cells with information in them, you skip to the right, over all filled cells, and stop just before the next blank cell. If you press Ctrl+ → again, you skip over all the nearby blank cells and land in the next cell to the right that has information in it. If there aren’t any more cells with data on the right, you wind up on the very edge of your worksheet.

The Ctrl+arrow-key combinations are useful if you have more than one table of data in the same worksheet. For example, imagine you have two tables of data, one at the top of a worksheet and one at the bottom. If you’re at the top of the first table, you can use Ctrl+↓ to jump to the bottom of the first table, skipping all the rows in between. Press Ctrl+↓ again, and you leap over all the blank rows, winding up at the beginning of the second table.

Tip

You can also scroll anywhere you want in your spreadsheet with the help of the scroll bars at the bottom and on the right side of the worksheet.

Finding your way around a worksheet is a fundamental part of mastering Excel. Knowing your way around the larger program window is no less important. The next few sections help you get oriented, pointing out the important stuff and letting you know what you can ignore altogether.

The Menus

You probably think that the classic pull-down menu is the simplest element on your screen. But in Excel 2003, the menus force you to take part in an awkward game of hide-and-seek—unless you take a few steps to tame them.

Figure 1-7 shows two different versions of the same Tools menu. The first is the “simplified” version Excel shows you automatically. The idea behind the simplified menu is that by hiding some of the more advanced choices, Excel helps you find what you want more quickly. To its credit, Excel goes one step further and actually stores information about what menu options you use. That means that when you select a menu like Tools, you see a simplified version that includes all the choices you typically use. Excel calls this a personalized menu, and the options change as you use the menu.

If you can’t find an item you’re all but certain was in a particular menu, you can temporarily expand the menu to show every option. Simply click the expand icon at the bottom of the personalized menu (it looks like two arrows pointing down). Alternatively, you can just hover over the expand icon with the mouse.

Top: Personalized menus attempt to make life easier by hiding the options you don’t use much. This innovation is often more trouble than it’s worth, however, because it’s hard to remember how a menu is organized when it’s constantly changing.Bottom: This version lists all available options.
Figure 1-7. Top: Personalized menus attempt to make life easier by hiding the options you don’t use much. This innovation is often more trouble than it’s worth, however, because it’s hard to remember how a menu is organized when it’s constantly changing. Bottom: This version lists all available options.

The problem with hide-and-seek menus is that when you switch from the personalized menu to the full menu, Excel rearranges the entire menu, inserting new items above, below, and between the existing items. If you like to find menu items by remembering their approximate location (as most people do), Excel’s personalized menus can send you right over the edge. Fortunately, it’s easy to set Excel to always show you the full, steady, one-size-fits-all menus. Just select Tools → Customize, and then in the Customize dialog box that appears, click the Options tab. Turn on “Always show full menus” to banish the personalized menus.

Note

When you turn off personalized menus in Excel, you’re actually changing a setting that affects all Office programs. Thus, you’re turning them off in other Office applications on your computer, including Word and Outlook.

The Task Pane

The Task Pane is the hub of activity in Excel. It provides information and controls to let you accomplish a specific task, like searching for help or opening a new spreadsheet. The Task Pane is a genuine improvement over the old way of getting work done in Excel. Instead of forcing you to dig through several different menus, the Task Pane gathers everything you need into one convenient location. It also lets you see your spreadsheet data at all times and continue working with it (Figure 1-8). Other Excel windows aren’t nearly as polite—they lock you out of your spreadsheet and obscure your data until you’re finished with them. For example, if you choose to format a group of cells, you can’t edit any information in the Excel grid until you finish using the Format Cells dialog box and click OK.

Unfortunately, the Task Pane lets you perform only a limited number of tasks, so it doesn’t replace Excel’s menus and toolbars. Still, it’s handy. To select the task that you want to perform, click the title of the Task Pane window. Figure 1-8 shows the list of possible tasks. Note that the current task is identified by a check-mark.

Tip

The Task Pane window often uses hyperlinks, which look like ordinary text, but you can click them to trigger an action, just like you click a toolbar button or pick an option from the menu. To find out if a given text item is a hyperlink, hover over it with the mouse. If the text becomes underlined, it’s a hyperlink. (Try the “Create a new workbook” link, for example, which you can see at the bottom of Figure 1-8.)

You can close the Task Pane window at any time by clicking the “x” in the top-right corner of the window. Or, you can quickly hide or show the Task Pane by choosing View → Task Pane.

When you first start Excel, the Task Pane appears on the right with the Getting Started task displayed. To switch to any of 10 other tasks, click the drop-down arrow in the window title and choose the task from the list.
Figure 1-8. When you first start Excel, the Task Pane appears on the right with the Getting Started task displayed. To switch to any of 10 other tasks, click the drop-down arrow in the window title and choose the task from the list.

All the Office programs include a Task Pane, but the list of available tasks differs. In Excel 2003, you can use the following tasks:

  • Getting Started. This task is the one you see when you first start Excel. It provides a list of links from Microsoft’s Office Web site (http://office.microsoft.com). For example, you can click “Get the latest news about using Excel” to open up your Internet browser with a list of bulletins about new developments on Planet Excel. You can also search the Office Web site for help files by using the “Search for” box. Finally, at the bottom of the Getting Started window are a couple of hyperlinks that let you open an existing spreadsheet or create a new one.

  • Help. This task lets you search the built-in Excel help files. If your computer is connected to the Internet, this search actually branches out to Microsoft’s online Excel documentation, where it retrieves the latest relevant information. For more information about getting help in Excel, check out Appendix A.

  • Search Results. The Search Results task window works in conjunction with the Help or Getting Started tasks. Both of these tasks let you search the online Office help. Once Excel has finished a search, the Search Results task window appears with a list of matching results.

Tip

You can navigate back to the Search Results task window at any time to see the results from your most recent search.

  • Clip Art. This extremely useful task lets you find clip art using a straightforward keyword search. For example, if you need to impress agricultural clients with a giant farm animal interposed between your columns of numbers, type “sheep” into the Clip Art search. After a short delay, a series of tiny pictures appears. You can then drag these pictures directly onto your spreadsheet and resize them as needed. The Clip Art search can find matching pictures that are installed on your computer (as a part of Office 2003) and free graphics from the Office 2003 Web site.

  • Research. While the Clip Art task let you find the graphics you need, the Research task lets you find the information you want. This search can include looking a term up in a dictionary or thesaurus, or performing an online search for news articles, stock quotes, encyclopedia entries, and more. Best of all, you perform the search inside Excel, so you can drag and drop results into your worksheet.

  • Clipboard. Excel lets you place multiple pieces of data on the clipboard at the same time. You can examine these pieces of data—which may be snippets of text, pictures, charts, or entire cell ranges (groups of cells)—using the Clipboard task window. You can then remove items or drag them back onto your spreadsheet, giving you a great way to quickly rearrange your tables. For more information, see Section 3.2.3.

  • New Workbook. This task lets you create a new spreadsheet. You have the choice of creating a blank spreadsheet or creating a spreadsheet based on a prebuilt template (fully designed spreadsheets that let you plug in numbers and perform specific tasks like figuring out loan payment amounts or creating sales invoices). Chapter 8 is all about templates.

  • Shared Workspace and Document Updates. These advanced tasks let you manage the way several people can collaborate on a spreadsheet through a shared workspace, although you can’t use these features unless you have SharePoint Server (a powerful collaboration tool included with Windows 2003 Server).

  • XML Source. Excel 2003 introduces a bunch of geek-only features that let spreadsheet wizards import and export XML data, which is a special format used to organize information so that it’s more easily exchanged between different operating systems and different programs.

Note

Occasionally, you come across tasks you can’t easily reach from the Task Pane menu. For example, when you launch a file search from the File menu in Excel 2003 (as described at the end of this chapter), the Basic File Search task appears, but it isn’t available through the main Task menu. (In contrast, Excel 2002 does offer file searching as one of its Task Pane options.)

The Toolbars

Excel, like any self-respecting Windows program, is filled with toolbars (Figure 1-9). You can think of toolbars as a lighter version of the Task Pane windows. Like the Task Pane, toolbars group together buttons for a common task (like manipulating pictures, creating charts, reviewing a document, and so on). Also like the Task Pane, toolbars stay out of your way while you work. But unlike the Task Pane, toolbars don’t provide much information to help you out if you aren’t already an expert. You can hover over a toolbar button to see a one- or two-word title for a button (as shown in Figure 1-9), but that’s about it.

Hovering over a toolbar button displays (very) brief tooltip text that describes that button.
Figure 1-9. Hovering over a toolbar button displays (very) brief tooltip text that describes that button.

To see a menu that lists all the available Excel toolbars (Figure 1-10), right-click any toolbar, or choose View → Toolbars. The toolbars that have a checkmark next to their name are the ones that are currently visible. You can use this list to show new toolbars (select a toolbar that isn’t checked) or hide visible ones (click a checked toolbar).

In this picture, only two toolbars are currently displayed: the all-important Standard and Formatting toolbars. To show other toolbars, right-click anywhere on one of the currently displayed toolbars for a menu of toolbar choices, as shown here.
Figure 1-10. In this picture, only two toolbars are currently displayed: the all-important Standard and Formatting toolbars. To show other toolbars, right-click anywhere on one of the currently displayed toolbars for a menu of toolbar choices, as shown here.

Tip

Resist the urge to display all the toolbars at the same time, or you’ll end up with a heavily cluttered window that shmushes your worksheet into a tiny corner.

Excel provides a grand total of 19 toolbars (not including the Task Pane, which is really a completely different type of window). In addition, some third-party products automatically add Excel toolbars to your computer. For example, if you install the full version of Adobe Acrobat (the software for creating PDF files), you find a new PDFMaker toolbar. Excel always shows third-party toolbars at the end of the toolbar list.

Note

To make life a little easier, Excel opens some toolbars automatically when you need them. For example, when you select a chart, the Chart toolbar springs into action at the top of the window, only to disappear again as soon as you select a different part of your spreadsheet. You see the same behavior when you create lists and work with pictures.

Excel starts you off with two important toolbars:

  • Standard. This toolbar includes the most commonly used buttons, like those for saving, opening, and printing spreadsheets. It also holds buttons for cutting and pasting data, undoing changes, and inserting charts.

  • Formatting. This toolbar includes buttons for changing text font, alignment, and style. You learn about these options in Chapter 4.

Both of these toolbars use a standard arrangement of buttons that’s a close replica of what you’ve probably seen in other Office programs, like Microsoft Word.

Moving toolbars

You can drag toolbars anywhere on the Excel window, which is useful, for instance, if you’re looking for ways to maximize the amount of screen space you have. For example, you can drag toolbars above the menu or to the left or right side of the window, in which case they change from a horizontal row of buttons to a vertical column of buttons. You can even drag a toolbar away from the window’s edge so that it becomes a standalone, resizable floating window like the one shown in Figure 1-11.

You can arrange toolbars in various places in the Excel window.
Figure 1-11. You can arrange toolbars in various places in the Excel window.

To move a toolbar, follow these three steps:

  1. Position your mouse over the left edge of the toolbar.

    The left edge of the toolbar has a series of dots to show you where the toolbar “grip” is. The mouse pointer changes to a four-way arrow to indicate you’re in the right place.

  2. Start dragging the toolbar.

    As you move the toolbar, other toolbars automatically rearrange themselves.

  3. Release the mouse when you’ve got the toolbar where you want it.

    Figure 1-11 shows some of the different ways you can position toolbars, and you can always drag toolbars back to their original positions.

Toolbars remember their last position. Thus, if you move a toolbar and then hide it, it appears in its new position the next time you show it. (To show a toolbar, click View → Toolbars and turn on the checkbox next to the toolbar you want to show.)

Tip

Sometimes a toolbar inadvertently gets loose from the edge of the window. Fortunately, there’s a quick way to return a rogue floating toolbar to its last docked position: just double-click the toolbar’s title bar.

Toolbars with missing buttons

Depending on the width of your window and the arrangement of your toolbars, some buttons on a toolbar may be invisible. You can tell if some are in hiding by checking out the toolbar’s right edge. If you see a symbol that includes two tiny triangles, you know that additional items lurk beneath the surface. To reach these missing buttons, click the right side of the toolbar, which opens a menu with the rest of the buttons.

Toolbar tricks

When you click the arrow on the right edge of a toolbar, Excel shows you not only any hidden buttons, but a couple of toolbar options, too. “Add or Remove Buttons” lets you customize the toolbar. You can also choose “Show Buttons on One Row” to put all the buttons from the Standard and Formatting toolbars on one horizontal row at the top of the window. While this arrangement gives you more space for your data, it also hides most of the buttons on your toolbars, which leads to extra clicking around when you need those babies.

Note

If you’ve chosen “Show Buttons on One Row,” the menu item changes to “Show Buttons on Two Rows” (which you can click to pop the Standard and Formatting toolbars back into separate rows).

The Formula Bar

The Formula bar appears above the worksheet grid but below the other Excel toolbars (Figure 1-12). It displays the address of the active cell (like B3) on the left edge, and it also shows you the content of the current cell.

The Formula bar shows information about the active cell.
Figure 1-12. The Formula bar shows information about the active cell.

You can use the Formula bar to enter and edit data, instead of editing directly in your worksheet. This approach is particularly useful when a cell contains a formula or a large amount of information, because you have more space to work with in the Formula bar than in a typical cell. Just as with in-cell edits, formula bar edits let you press Enter to confirm your changes or Esc to cancel them. You can also use the mouse: between the cell address and the box showing its contents, click the green checkmark to commit your modification or the red “X” to roll it back.

Note

You can hide (or show) the Formula bar by choosing View → Formula Bar. But it’s such a basic part of Excel that it would be unwise to get rid of it. Instead, keep it around until Chapter 7, where you see how to build formulas.

The Status Bar

Though people often overlook it, the Status bar (Figure 1-13) is a good way to keep on top of what Excel’s currently doing. For example, if you save or print a document, the Status bar, which is located at the bottom of the Excel window, shows the progress of the printing process. If you’re performing a quick action, the progress indicator may disappear before you have a chance to even notice it. But if you’re performing a time-consuming operation—say, printing out an 87-page table of the airline silverware you happen to own—you can look to the Status bar to see how things are coming along. (To hide or show the Status bar, choose View → Status Bar.)

The always-available Status bar displays basic status text (which just says “Ready” in this example) and various indicators when they’re active (“CAPS” and “SCRL” in this example).
Figure 1-13. The always-available Status bar displays basic status text (which just says “Ready” in this example) and various indicators when they’re active (“CAPS” and “SCRL” in this example).

Usually the Status bar displays one of two things:

  • The word “Ready.”Ready means that Excel isn’t doing anything much at the moment, other than waiting for you to take some action.

  • The word “Edit.”Edit means the cell is currently in Edit mode, and pressing the left and right arrow keys moves through the cell data, instead of moving from cell to cell. As you learned on Section 1.2, you can place a cell in edit mode or take it out of edit mode by pressing F2.

In addition, the compartments at the rightmost side of the Status bar give you a few other pieces of information, like whether Caps Lock is turned on. Table 1-2 describes these special values.

Table 1-2. Status Bar Indicators

INDICATOR

MEANING

CAPS

Every letter you type in is automatically capitalized. To turn this feature on or off, hit the Caps Lock key on your keyboard.

NUM

Many people find it fastest to use the numeric keypad (typically at the right side of your keyboard) to type in numbers. When this sign is off, the numeric keypad controls cell navigation instead. To turn this feature on or off, press the Num Lock key.

SCRL

When this sign is on and you use the arrow keys, the worksheet scrolls as normal, but the active cell doesn’t change. This feature lets you look at all the information you have in your worksheet without losing track of the place you’re currently in. You can turn this feature on or off by pressing the Scroll Lock key.

OVR

Overwrite mode is turned on. If you type new characters in edit mode, they overwrite existing characters (rather than displacing them). You can turn this feature on or off by pressing the Insert key.

END

You have pressed the End key, which is the first key in a two-key combination; the next key determines what happens. For example, hit End and then Home to move to the bottom-right cell in your worksheet. See Table 1-1 for a list of key combinations, some of which use End.

EXT

As you press the arrow keys, Excel automatically selects all the rows and columns you cross—in other words, your selection is extended, which is what the three-letter abbreviation refers to. Extended mode is a useful keyboard alternative to dragging your mouse to select swaths of the grid. To turn this mode on or off, press F8. You can learn more about selecting cells and moving them around in Chapter 3.

FIX

Indicates that Excel will automatically add a set number of decimal places to the values you enter in any cell. For example, if you set Excel to use two fixed decimal places and you type the number 5 into a cell, Excel actually enters 0.05. This seldom-used featured is handy for speed typists who need to enter reams of data in a fixed format. You can turn this feature on or off by selecting Tools → Options, choosing the Edit tab, and then turning on “Fixed decimal.” You can learn more about how to format numeric cells in Chapter 4.

Note

You can also use the Status bar to display quick totals as you select groups of numbers. This trick is explained in the box “A Truly Great Calculation Trick” on Section 3.2.

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 lets you 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 covered on Section 1.4.1). To use Save As, select File → Save As (or press F12) to display the Save As dialog box shown in Figure 1-14. Excel suggests you save your file in the My Documents folder, but you can save your document in whatever folder you like. To specify a folder, either click the icons on the left-hand side of the box, or choose from the drop-down "Save in” box. When you finish choosing a place to save your file, zip to the bottom of the Save As dialog box: here you type the file name and, finally, pick the file type.

At the bottom of the Save As dialog box, you can type in a file name and choose a file type.
Figure 1-14. At the bottom of the Save As dialog box, you can type in a file name and choose a file type.
  • 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 File → Save, or press Ctrl+S.

Tip

Resaving a spreadsheet is an almost instantaneous operation, and if you tend toward paranoia, 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. (You may also want to check out Excel’s AutoRecover option, covered on Section 1.4.3.)

Saving Your Spreadsheet in Other Formats

Excel saves spreadsheets as .xlsfiles (for example, AirlineSilverware.xls). As hardcore Excel programming gurus know, this file format has a codename, BIFF8 (which, obviously, stands for Binary Interchange File Format). This is the format used by Excel 2003, Excel 2002, Excel 2000, and Excel 97. The codename is useful to know because earlier versions of Excel also store spreadsheets as .xls files, but the underlying format is actually different—and sometimes maddeningly irreconcilable.

For example, Excel 95 uses the incompatible BIFF7 file format, which means it can’t open a BIFF8 spreadsheet at all. And while Excel 97 uses BIFF8, it doesn’t support a feature called pivot charts, which were introduced in Excel 2000. Excel 97 can still open Excel 2000 workbooks that contain pivot charts, but it doesn’t let you manipulate them.

The good news is that if you need to exchange spreadsheet files with somebody who’s saddled with a Paleolithic spreadsheet application, you can save a copy of your spreadsheet in an older format. To do so, select File → Save As. Then choose the format you want from the “Save as type” drop-down list and click Save. Excel lets you save your spreadsheet using a variety of different formats, including the classic Lotus and dBase formats from the DOS world. If you’re looking to view your spreadsheet using another program, use the CSV file type, which produces a comma-delimited text file that almost all spreadsheet programs on any operating system can read (comma-delimited means the information will have commas separating each cell). Figure 1-15 shows the Save As dialog box.

Tip

When you save your Excel spreadsheet in another format, make sure you keep a copy in the standard .xls 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.

Excel offers a wide variety of file-type options in the “Save as type” list, including options to save your Excel data as an HTML Web page.
Figure 1-15. Excel offers a wide variety of file-type options in the “Save as type” list, including options to save your Excel data as an HTML Web page.

Saving Your Spreadsheet with a Password

Occasionally, you might want to add confidential information to a spreadsheet—for 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. Doing so is easy—just follow these steps:

  1. Select File → Save As.

    The Save As dialog box appears.

  2. In the Save As dialog box, choose Tools → General Options.

    The Save Options dialog box appears.

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

    To thwart eagle-eyed coworkers or family members, Excel masks your password (displays asterisks in the window as you type).
    Figure 1-16. To thwart eagle-eyed coworkers or family members, Excel masks your password (displays asterisks in the window as you type).

    Note

    You can use any sequence of letters and numbers as a password. Passwords are case-sensitive, which means that PanAm is different from panam.

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

    If you use a password to restrict people from modifying the spreadsheet, the next time you open this file you’ll be given the choice—shown in Figure 1-17 on the bottom—to open it in read-only mode (which requires no password) or to open it in full-edit mode (in which case you need to supply the “password to modify”).

Top: Assign a “password to open” and you see this window when you open the file.Bottom: If you assign a “password to modify,” you see the choices in this window.
Figure 1-17. Top: Assign a “password to open” and you see this window when you open the file. Bottom: If you assign a “password to modify,” you see the choices in this window.

Disaster Recovery

The corollary to the edict “Save your data early and often” is the truism “Sometimes it’s not possible to catch everything before a sudden software, hardware, or power failure ends your Excel session early.” 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), you’ll lose, at most, 10 minutes of work.

Note

There’s really no danger in backing up too frequently. Unless you work with extremely complex or large spreadsheets—which may suck up a lot of computing power and take a long time to save—you can set Excel to save the document every five minutes with no appreciable slowdown.

AutoRecover comes switched on when you install Excel, but if you want to check, select Tools → Options (make sure you’ve got an open spreadsheet file that you’ve saved at least once), and then in the dialog box that appears, click the Save tab. Make sure that “Save AutoRecover info” is turned on. You can then choose a folder where you’d like Excel to save backup files (the folder Excel suggests—C:\Documents and Settings\(YourName)\Application Data\Microsoft\Excel\—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. You can also adjust the backup frequency in minutes (see Figure 1-18).

If your computer does crash, when you get it running again, you can easily retrieve your last AutoRecover backup. In fact, once you restart Excel, it automatically checks the backup folder, and, if it finds a backup, it opens a special Document Recovery window on the left of the Excel window, as shown in Figure 1-19.

Clicking Tools → Options → Save displays this window, which lets you configure how often (and where) AutoRecover saves backups.
Figure 1-18. Clicking Tools → Options → Save displays this window, which lets you configure how often (and where) AutoRecover saves backups.

In the Document Recovery window, [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 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.)

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.

Top: Every time Excel starts up, it looks for AutoRecover backups.Bottom: You can save or open an AutoRecover backup like an ordinary Excel file; simply click the item in the list.
Figure 1-19. Top: Every time Excel starts up, it looks for AutoRecover backups. Bottom: You can save or open an AutoRecover backup like an ordinary Excel file; simply click the item in the list.

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.

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.

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

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

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.

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

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

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