Section 1.3. Navigating in Excel

1.3. 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 might want to cover ground a little quicker. 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 you hold down Ctrl and press Home at the same time. Key combinations with a comma work in sequence. For example, the key combination "End, Home" means press End first, release it, and then press Home.
Table 1-1. Shortcut Keys for Moving Around a Worksheet

Key Combination


(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 screen. 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 screen. Thus, if the grid shows 10 cells at a time, this key moves to a cell in the same column, 10 rows down.


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


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 youre 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, youll 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 uncharted 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.

Getting Somewhere in a Hurry

If you're fortunate enough to know exactly where you need to go, you can use the Go To feature to make the jump. Go To moves to the cell address you specify. It comes in useful in extremely large spreadsheets, where just scrolling through the worksheet takes half a day.

To bring up the Go To dialog box (shown in Figure 1-8), choose Home Editing Find & Select Go To. Or you can do yourself a favor and just press Ctrl+G. Enter the cell address (such as C32), and then click OK.

The Go To feature becomes more useful the more you use it. That's because the Go To window maintains a list of the most recent cell addresses that you've entered. In addition, every time you open the Go To window, Excel automatically adds the current cell to the list. This feature makes it easy to jump to a far-off cell and quickly return to your starting location by selecting the last entry in the list.

The Go To window isn't your only option for leaping through a worksheet in a single bound. If you look at the Home Editing Find & Select menu, youll find more specialized commands that let you jump straight to cells that contains formulas, comments, conditional formatting, and other advanced Excel ingredients that you haven't learned about yet. And if you want to hunt down cells that have specific text, you need the popular Find command (Home Editing Find & Select Find), which is covered in Section 4.2.

Figure 1-8. You'll notice that in the Go To list, cell addresses are written a little differently than the format you use when you type them in. Namely, dollar signs are added before the row number and column letter. Thus, C32 becomes $C$32, which is simply the convention that Excel uses for fixed cell references. (You'll learn much more about the different types of cell references in Chapter 8.)

1.3.1. The Tabs of the Ribbon

In the Introduction you learned about the ribbon, the super-toolbar that offers one-stop shopping for all of Excel's features. All the most important Office applicationsincluding Word, Access, PowerPoint, and Exceluse the new ribbon, However, each program has a different set of tabs and buttons .

Throughout this book, you'll dig through the different tabs of the ribbon to find important features. But before you start your journey, it's nice to get a quick overview of what each tab provides. Here's the lowdown:

  • Home includes some of the most commonly used buttons, like those for cutting and pasting information, formatting your data, and hunting down important bits of information with search tools. You've already used the Go To button on this tab (see the box "Getting Somewhere in a Hurry," above).

  • Insert lets you add special ingredients like tables, graphics, charts , and hyperlinks .

  • Page Layout is all about getting your worksheet ready for the printer. You can tweak margins, paper orientation, and other page settings.

  • Formulas are mathematical instructions that you use to perform calculations. This tab helps you build super-smart formulas and resolve mind-bending errors.

  • Data lets you get information from an outside data source (like a heavy-duty database) so you can analyze it in Excel. It also includes tools for dealing with large amounts of information, like sorting, filtering, and subgrouping .

  • Review includes the familiar Office proofing tools (like the spell checker). It also has buttons that let you add comments to a worksheet and manage revisions.

  • View lets you switch on and off a variety of viewing options. It also lets you pull off a few fancy tricks if you want to view several separate Excel spreadsheet files at the same time.

Note: In some circumstances, you may see a couple of tabs that aren't listed here. Macro programmers and other highly technical types use the Developer tab. (You'll learn how to reveal this tab in Section 28.1.1.) The Add-Ins tab appears when you're viewing workbooks that were created in previous versions of Excel, and which used custom toolbars . (Section A.1.2 has the full story.)
Collapsing the Ribbon

Most people are happy to have the ribbon sit at the top of the Excel window, with all its buttons on hand. However, serious number crunchers demand maximum space for their data. They'd rather look at another row of numbers than a pumped-up toolbar. If this describes you, then you'll be happy to find out you can collapse the ribbon, which shrinks it down to a single row of tab titles, as shown Figure 1-9. To collapse it, just double-click any tab title.

Even when the ribbon's collapsed , you can still use all its features. All you need to do is click a tab. For example, if you click Home, the Home tab pops up over your work-sheet. As soon as you click the button you want in the Home tab (or click a cell in your worksheet), the ribbon collapses itself again. The same trick works if you trigger a command in the ribbon using the keyboard, as described in Section 3.2.1.

If you use the ribbon only occasionally, or if you prefer to use keyboard shortcuts, it makes sense to collapse the ribbon. Even when collapsed, the ribbon commands are availableit just takes an extra click to open the tab. On the other hand, if you make frequent trips to the ribbon, or you're learning about Excel and you like to browse the ribbon to see what features are available, don't bother collapsing it. The two or three rows that you'll lose are well worth keeping.

Figure 1-9. Do you want to use every square inch of screen space for your cells? You can collapse the ribbon (as shown here) by double-clicking any tab. Click a tab to pop it open temporarily, or double-click a tab to bring the ribbon back for good. And if you want to perform the same trick without raising your fingers from the keyboard, you can use the shortcut key Ctrl+F1.

1.3.2. The Formula Bar

The formula bar appears above the worksheet grid but below the ribbon (Figure 1-10). It displays the address of the active cell (like A1) on the left edge, and it also shows you the current cell's contents.

Figure 1-10. 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 anywhere in the formula bar and make your changes there.

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. That's because the formula bar gives you more work room than a typical cell. Just as with in-cell edits, you press Enter to confirm your changes or Esc to cancel them. Or you can use the mouse: When you start tying in the formula bar, a checkmark and an "X" icon appear just to the left of the box where you're typing. Click the checkmark to confirm your entry, or "X" to roll it back.

Note: You can hide (or show) the formula bar by choosing View Show/Hide Formula Bar. But the formula bars such a basic part of Excel that you'd be unwise to get rid of it. Instead, keep it around until Chapter 8, when you'll learn how to build formulas.

Ordinarily, the formula bar's a single line. If you have a really long entry in a cell (like a paragraph's worth of text), you need to scroll from one side to the other. However, there's another optionyou can resize the formula bar so it fits more information, as shown in Figure 1-11.

Figure 1-11. To enlarge the formula bar, click the bottom edge and pull down. You can make it two, three, four, or many more lines large. Best of all, once you get the size you want, you can use the expand/collapse button on the right side of the formula bar to quickly expand it to your preferred size and collapse it back to the single-line view.

1.3.3. The Status Bar

Though people often overlook it, the status bar (Figure 1-12) 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 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 operationsay, printing out an 87-page table of the airline silverware you happen to ownyou can look to the status bar to see how things are coming along.

Tip: To hide or show the status bar, choose View Show/Hide Status Bar.

Figure 1-12. In the status bar, you can see the basic status text (which just says "Ready" in this example), the view buttons (which are useful when you're preparing a spreadsheet for printing), and the zoom slider bar (which lets you enlarge or shrink the current worksheet view).

The status bar combines several different types of information. The leftmost part of the status bar shows the Cell Mode, which displays one of three indicators.

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

  • The word "Enter" appears when you start typing a new value into a cell.

  • The word "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 Section 1.2, you can place a cell in edit mode or take it out of edit mode by pressing F2.

Farther to the right on the status bar are the view buttons, which let you switch to Page Layout View or Page Break Preview. These different views help you see what your worksheet will look like when you print it. They're covered in Chapter 7.

The zoom slider is next to the view buttons, at the far right edge of the status bar. You can slide it to the left to zoom out (which fits more information into your Excel window at once) or slide it to the right to zoom in (and take a closer look at fewer cells). You can learn more about zooming in Section 7.1.1.

In addition, the status bar displays other miscellaneous indicators. For example, if you press the Scroll Lock key, a Scroll Lock indicator appears on the status bar (next to the "Ready" text). This indicator tells you that you're in scroll mode . In scroll mode, the arrow keys don't move you from one cell to another; instead, they scroll the entire worksheet up, down, or to the side. Scroll mode is a great way to check out another part of your spreadsheet without leaving your current position.

You can control what indicators appear in the status bar by configuring it. To see a full list of possibilities, right-click the status bar. A huge list of options appears, as shown in Figure 1-13. Table 1-2 describes the different status bar options.

Note: The Caps Lock indicator doesn't determine whether or not you can use the Caps Lock keythat feature always works. The Caps Lock indicator just lets you know when Caps Lock mode is on. That way you won't be surprised by an accidental keystroke that turns your next data entry INTO ALL CAPITALS.

Figure 1-13. Every item that has a checkmark appears in the status bar when you need it. For example, if you choose Caps Lock, the text "Caps Lock" appears in the status bar whenever you hit the Caps Lock key to switch to all-capital typing. The text that appears on the right side of the list tells you the current value of the indicator. In this example, Caps Lock mode is currently off and the Cell Mode text says "Ready."

Table 1-2. Status Bar Indicators



Cell Mode

Shows Ready, Edit, or Enter depending on the state of the current cell, as described in Section 1.3.3.

Signatures, Information Management Policy, and Permissions

Displays information about the rights and restrictions of the current spreadsheet. These features come into play only if you're using Office SharePoint Server to share spreadsheets among groups of people (usually in a corporate environment). SharePoint is introduced in Section 23.2.

Caps Lock

Indicates whether Caps Lock mode is on. When Caps Lock is on, every letter you type is automatically capitalized. To turn Caps Lock mode on or off, hit Caps Lock.

Num Lock

Indicates whether Num Lock mode is on. When this mode is on, you can use the numeric keypad (typically at the right side of your keyboard) to type in numbers more quickly. When this sign's off, the numeric keypad controls cell navigation instead. To turn Num Lock on or off, press Num Lock.

Scroll Lock

Indicates whether Scroll Lock mode is on. When it's on, you can use the arrow keys to scroll the worksheet without changing the active cell. (In other words, you can control your scrollbars by just using your keyboard.) This feature lets you look at all the information you have in your worksheet without losing track of the cell you're currently in. You can turn Scroll Lock mode on or off by pressing Scroll Lock.

Fixed Decimal

Indicates when Fixed Decimal mode is on. When this mode is on, Excel automatically adds 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 Office button Excel Options, choosing the Advanced section, and then looking under "Editing options to find the "Automatically insert a decimal point" setting. Once you turn this checkbox on, you can choose the number of decimal places (the standard option is two).

Overtype Mode

Indicates when Overwrite mode is turned on. Overwrite mode changes how cell edits work. When you edit a cell and Overwrite mode is on, the new characters that you type overwrite existing characters (rather than displacing them). You can turn Overwrite mode on or off by pressing Insert.

End Mode

Indicates that you've pressed End, which is the first key in many two-key combinations; 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.

Macro Recording

Macros are automated routines that perform some task in an Excel spreadsheet. The Macro Recording indicator shows a record button (which looks like a red circle superimposed on a worksheet) that lets you start recording a new macro. You'll learn more about macros in Chapter 27.

Selection Mode

Indicates the current Selection mode. You have two options: normal mode and extended selection . When you press the arrows keys and extended selection is on, Excel automatically selects all the rows and columns you cross. Extended selection is a useful keyboard alternative to dragging your mouse to select swaths of the grid. To turn extended selection on or off, press F8. You'll learn more about selecting cells and moving them around in Chapter 3.

Page Number

Shows the current page and the total number of pages (as in "Page 1 of 4"). This indicator appears only in Page Layout view (as described in Section 7.2.2).

Average, Count, Numerical Count, Minimum, Maximum, Sum

Show the result of a calculation on the selected cells. For example, the Sum indicator shows the total of all the numeric cells that are currently selected. You'll take a closer look at this handy trick in Section 3.1.1.

View Shortcuts

Shows the three view buttons that let you switch between Normal view, Page Layout View (Section 7.2.2), and Page Break Preview (Section 7.3.2).


Shows the current zoom percentage (like 100 percent for a normalsized spreadsheet, and 200 percent for a spreadsheet that's blown up to twice the magnification).

Zoom Slider

Shows a slider that lets you zoom in closer (by sliding it to the right) or out to see more information at once (by sliding it to the left).

1.3.4. Excel Options

You might have already seen the Excel Options window, which provides a central hub where you can adjust how Excel looks, behaves, and calculates (see Figure 1-14). To get to this window, click the Office button, and then choose Excel Options on the bottom-right edge.

Figure 1-14. The Excel Options window is divided into nine sections. To pick which section to look at, choose an entry from the list on the left. In this example, you're looking at the Popular settings group. In each section, the settings are further subdivided into titled groups. You may need to scroll down to find the setting you want.

The top five sections in the Excel Options window let you tweak a wide variety of different details. Some of these details are truly handy, like the options for opening and saving files (which are described at the end of this chapter). Others are seldom-used holdovers from the past, like the option that lets Excel act like Lotusan ancient piece of spreadsheet softwarewhen you hit the "/" key.

Tip: Some important options have a small i-in-a-circle icon next to them, which stands for "information." Hover over this icon and you see a tooltip that gives you a brief description about that setting.

Beneath the top five sections are four more specialized sections:

  • Customize lets you put your favorite commands on the Quick Access toolbar, a maneuver you can learn more about in the Appendix.

  • Add-Ins lets you configure other utilities (mini-programs) that work with Excel and enhance its powers. For example, you'll turn to this list to switch on the Solver tool in Chapter 20.

  • Trust Center lets you tweak Excel's security settings that safeguard against dangerous actions (think: viruses). You need to learn more about these settings before you can use Excel to interact with a database or run macro code. Section 27.3.1 has full details.

  • Resources provides a few buttons that let you get extra diagnostic information, activate your copy of Office (which you've no doubt done already), and get freebies and updates on the Web (Figure 1-15).

Figure 1-15. Using the Resources section, you can check for late-breaking Excel updates (Check for Updates), run a tool to identify problems that are preventing Excel from working (Diagnose), and check out the information and freebies on the Office Online Web site (Go Online).

While you're getting to know Excel, you can comfortably ignore most of what's in the Excel Options window. But you'll return here many times throughout this book to adjust settings and fine-tune the way Excel works.

Excel 2007[c] The Missing Manual
Excel 2007[c] The Missing Manual
ISBN: 596527594
Year: 2007
Pages: 173 © 2008-2017.
If you may any questions please contact us: