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

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'll see the black focus box move to highlight the currently active cell.

In some cases, you might 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, 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 are 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'll skip to the right, over all filled cells, and stop just before the next blank cell. If you press Ctrl+ again, you'll 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'll 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 are 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 off into the nether regions of the spreadsheet with the help of the scrollbars 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'll 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.

Left: Personalized menus attempt to make life easier by hiding the options that Excel guesses you don't care about. 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. Right: This version lists all available options.

Figure 1-7.  Left: Personalized menus attempt to make life easier by hiding the options that Excel guesses you don't care about. 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. Right: This version lists all available options.

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.

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, inserting clip art, or sharing a 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 allows you to 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 only lets you perform 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.

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

When you first start Excel, the Task Pane appears on the right with the Getting Started task displayed. You can switch to any of 10 other tasks by clicking the drop-down arrow in the window title and choosing the task from the list. The current task is identified with a checkmark.

Figure 1-8. When you first start Excel, the Task Pane appears on the right with the Getting Started task displayed. You can switch to any of 10 other tasks by clicking the drop-down arrow in the window title and choosing the task from the list. The current task is identified with a checkmark.

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.

All the Office applications 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 allow you to 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 thumbnails 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. For more information about Excel and graphics, see Chapter 18.

  • 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, financial 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. But as you'll find out in Chapter 24, much of the research material is covered by exclusive subscription services that are only available if you're willing to shell out some cold cash.

  • Clipboard. Excel lets you place multiple pieces of data on the clipboard at the same time. You can examine these pieces of data—which might be snippets of text, pictures, charts, or entire cell ranges—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. You'll learn more about creating and saving new spreadsheets a little later in this chapter. Templates are covered in Chapter 15.

  • Shared Workspace and Document Updates. These 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 (an advanced collaboration tool included with Windows 2003 Server). Chapter 21 covers Excel collaboration.

  • XML Source. Excel 2003 introduces new features that let you 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. With the help of these features, you can plug your spreadsheet into an automated business process. For example, you could send data from an expense report spreadsheet directly to an application that processes expenses. You'll learn all about XML and the future of Office integration in Chapter 23.

Note

Occasionally, you'll 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. 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.

Toolbars provide tooltip text that describes each button. Unfortunately, this text probably won't tell you what you need to know if you're trying to learn about a feature you've never used before.

Figure 1-9. Toolbars provide tooltip text that describes each button. Unfortunately, this text probably won't tell you what you need to know if you're trying to learn about a feature you've never used before.

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

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 and plug-ins 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'll 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'll learn about these options in Chapter 4.

Both of these toolbars use a standard arrangement of buttons that is closely replicated in other Office applications, like Microsoft Word.

Excel provides a wealth of toolbars. In this picture, only two toolbars are currently displayed: the all-important Standard and Formatting toolbars. To show other toolbars, just choose View → Toolbars and select them by clicking on their name in the menu. Or, just right-click anywhere on one of the currently displayed toolbars, as shown here.

Figure 1-10. Excel provides a wealth of toolbars. In this picture, only two toolbars are currently displayed: the all-important Standard and Formatting toolbars. To show other toolbars, just choose View Toolbars and select them by clicking on their name in the menu. Or, just right-click anywhere on one of the currently displayed toolbars, as shown here.

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. 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 in the desired position.

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

You can arrange toolbars in various places in the Excel window. 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 floating window, like this Picture toolbar. You can resize floating windows in the same way you resize any other window.

Figure 1-11. You can arrange toolbars in various places in the Excel window. 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 floating window, like this Picture toolbar. You can resize floating windows in the same way you resize any other window.

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.

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 examining the right edge of the toolbar. If you see a symbol that includes two tiny triangles you know that additional items lurk beneath the surface. Figure 1-12 shows the tell-tale sign and the trick for revealing the hidden 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. (To learn more about customization, see Appendix B.) 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.

When you shrink the Formatting toolbar, some of the buttons for applying numeric styles disappear. You can reach these missing buttons by clicking the right side of the toolbar, which opens a menu with the rest of the buttons.

Figure 1-12. When you shrink the Formatting toolbar, some of the buttons for applying numeric styles disappear. You can reach these missing buttons by clicking the right side of the toolbar, which opens a menu with the rest of the buttons.

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-13). It displays the address of the active cell (like A1) on the left edge, and it also shows you the content of the current 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, when you'll learn how to build formulas.

The Formula bar (just above the grid) shows information about the active cell. In this example, the Formula bar shows that the current cell is B4 and that it contains the number 592. Instead of editing this value in the worksheet, you can click in the Formula bar and make your changes there.

Figure 1-13. The Formula bar (just above the grid) shows information about the active cell. In this example, the Formula bar shows that the current cell is B4 and that it contains the number 592. Instead of editing this value in the worksheet, you can click in the Formula bar and make your changes there.

The Status Bar

Though people often overlook it, the Status bar (Figure 1-14) is a good way to keep on top of Excel's current state. 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.)

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 discussed in Quick Ways to Add Data, 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. These special values are described in Table 1-2.

The Status bar is an always available (but often overlooked) part of the Excel window. In it, you can see the basic status text (which just says "Ready" in this example) and several compartments on the right that display various indicators when they're active (like "CAPS" and "SCRL" in this example).

Figure 1-14. The Status bar is an always available (but often overlooked) part of the Excel window. In it, you can see the basic status text (which just says "Ready" in this example) and several compartments on the right that display various indicators when they're active (like "CAPS" and "SCRL" in this example).

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.

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 allows you to 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'll 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'll 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 on Sidebar 3.1.

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