O'Reilly logo

Excel 2010: 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

A Tour of the Excel Window

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 in the Excel window, pointing out the important stuff and letting you know what you can usually ignore.

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 applications—including Word, Access, PowerPoint, and Excel—use the 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:

  • File isn't really a toolbar tab, even though it appears first in the list. Instead, it's your gateway to Excel's backstage view, as described on Going Backstage.

  • 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 page The Go To Feature).

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

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.

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.

Note

In some circumstances, you may see 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 on Attaching a Macro to a Button Inside a Worksheet.) The Add-Ins tab appears when you're viewing workbooks that were created in previous versions of Excel and used custom toolbars. And finally, you can create a tab of your own if you're ambitious enough to customize the ribbon, as explained in the appendix.

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.

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→Formula Bar. But the formula bar is 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.

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.

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.

Ordinarily, the formula bar is 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 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.

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.

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

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

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. You can place a cell in edit mode or take it out of edit mode by pressing F2.

Just to the right of the Cell Mode information, you may see a small button that lets you start recording a macro. (A macro is a series of steps you can replay as often as you need to automate tiresome chores. You'll learn more about macros in Chapter 28.)

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

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 you can use the Caps Lock key—that 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.

Table 1-2. Status bar indicators

Indicator

Meaning

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). SharePoint is introduced on Workbook Sharing in Action.

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 through 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 File®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 2).

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

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 "The Missing Manual Series of 4"). This indicator appears only in Page Layout view (as described on Page Layout View: A Better Print Preview).

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 on Making Continuous Range Selections.

Upload Status

Does nothing (that we know of). Excel does show a handy indicator in the status bar when you're uploading files to the Web, as you'll learn in Chapter 26. However, the upload status is always shown, and this setting doesn't seem to have any effect.

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

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

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

Going Backstage

Your data is the star of the show. That's why the creators of Excel refer to your worksheet as being "on stage." The auditorium is the Excel main window, which—as you've just seen—includes the handy ribbon, formula bar, and status bar. Sure, it's a strange metaphor. But once you understand it, you'll realize the rationale for Excel's backstage view, which temporarily takes you away from your worksheet and lets you concentrate on other tasks that don't involve entering or editing data. These tasks include saving your spreadsheet, opening more spreadsheets, printing your work, and changing Excel settings.

To switch to backstage view, click the File button that's just to the left of the Home ribbon tab. Excel temporarily tucks your worksheet out of sight (although it's still open and waiting for you). This gives it space to show extra information related to the task you want to perform, as shown in Figure 1-14. For example, if you plan to print your spreadsheet, Excel's backstage view has room to show a preview of the printout. Or if you want to open an existing spreadsheet, Excel can show a detailed list of files you've recently worked on.

To get out of backstage view and return to your worksheet, just click the File button again, or press Esc.

When you first switch to backstage view, Excel shows the Info page, which provides some basic information about your workbook file, its size, when it was last edited, who edited it, and so on (see the column on the far right). The Info page also provides the gateway to three important features: document protection (Chapter 24), compatibility checking (page 41), and AutoRecover backups (page 82). To go to another section, click a different command in the column on the far left.

Figure 1-14. When you first switch to backstage view, Excel shows the Info page, which provides some basic information about your workbook file, its size, when it was last edited, who edited it, and so on (see the column on the far right). The Info page also provides the gateway to three important features: document protection (Chapter 24), compatibility checking (page 41), and AutoRecover backups (page 82). To go to another section, click a different command in the column on the far left.

Excel Options

The Excel Options window provides a central hub where you can adjust how Excel looks, behaves, and calculates (see Figure 1-15). To get to this window, choose File→Options.

The various 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 Lotus—an ancient piece of spreadsheet software—when you hit the "/" key.

The Excel Options window is divided into 10 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 General 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.

Figure 1-15. The Excel Options window is divided into 10 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 General 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.

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.

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.

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