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:

  1. Use the arrow keys on the keyboard. Keystrokes move you one cell at a time in any direction.
  2. 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. 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. .,

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.

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 Tabs of the Ribbon

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

Throughout this blog, 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.
  • 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 sub-grouping.
  • 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.

The Formula Bar

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

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.

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 option you can resize the formula bar so it fits more information, as shown in Figure 2.

The Status Bar

Though people often overlook it, the status bar (Figure 3) 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 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.

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.

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.

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

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

Lists below describes the different status bar options.

  • Cell Mode - Shows Ready, Edit, or Enter depending on the state of the current cell.
  • 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).
  • 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.
  • 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.
  • 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.
  • 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.
  • 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.
  • View Shortcuts - Shows the three view buttons that let you switch between Normal view, Page Layout View, and Page Break Preview.
  • Zoom - 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).

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. To get to this window, click the Office button, and then choose Excel Options on the bottom-right edge.

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. Others are seldom-used holdovers from the past, like the option that lets Excel act like Lotus an ancient piece of spreadsheet software when you hit the "/" key.

Beneath the top five sections are four more specialized sections:

  • Customize lets you put your favorite commands on the Quick Access toolbar.
  • 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
  • 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.
  • 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

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 to adjust settings and fine-tune the way Excel works.