MicrosoftВ® Office ExcelВ® 2007 Inside Out (Inside Out (Microsoft))

This section will take you on a tour of not only the dashboard of Office Excel 2007 but also the trunk and the glove compartment. We might even slip on some gloves and take a peek under the floor mats.

Facts About Worksheets

Here are a few random tidbits of interesting information about the grid called the worksheet, shown in Figure 2-3.

• Column letters range from A through XFD. (After column Z comes column AA, after column ZZ comes column AAA, and so on, up to XFD.) Row numbers range from 1 through 1,048,576.

• The currently selected cell is referred to as the active cell. When you select a range of cells, only the cell in the upper-left corner is considered the active cell. The reference of the active cell appears in the Name box on the left end of the formula bar.

• The headings for the columns and rows containing selected cells are highlighted, making it easier to identify the location of selected cells.

• With 16,000 columns and 1,048,576 rows, your worksheet contains more than 16 trillion individual cells. Before you try to unravel the mysteries of the universe on a single worksheet, however, remember that the number of cells you can use at a time is limited by the amount of memory your computer has. Although Excel allocates memory only to cells containing data, you might have trouble actually using all the cells on one worksheet, no matter how much memory you have.

Figure 2-3: The available space on the worksheet is much larger in Excel 2007.

The workbook window is like a porthole through which you can see only a portion of a worksheet. To illustrate, suppose you were to cut a small, square hole in a piece of cardboard and place the cardboard over this page. At any given time, you could see only a portion of the page through the hole. By moving the cardboard around the page, however, you could eventually read the entire page through the window in your piece of cardboard. Viewing worksheets in Excel is much the same. You can also open another window to view different sections of the same worksheet simultaneously.

Using the Workbook Window

A new workbook, shown floating (that is, neither maximized nor minimized) in Figure 2-4, originally consists of three individual worksheets.

Figure 2-4: Workbooks initially comprise three worksheets.

For more information about using workbooks, see Chapter 7, "How to Work a Workbook."

Workbooks are great organizational tools. For example, you can keep in the same workbook all the documents that relate to a specific project, department, or individual. Workbooks can eliminate a considerable amount of clutter on your hard disk. The more documents you have to manage, the more valuable workbooks become. You can use workbooks as a multiuser management tool. For example, you can organize worksheets in groups for individual tasks or individual users. You can also share a workbook so more than one person can work on it at the same time.

If you routinely create folders on your hard disk to contain groups of related files, you can think of workbooks as folders where you can keep all related spreadsheets.

For more information about using and sharing workbooks, see Chapter 24, "Collaborating on a Network or by E-Mail."

The Title Bar

At the top of the Excel workspace is the title bar, which displays the application name along with the name of the workbook in which you are currently working. If the window is floating, as shown in Figure 2-4, the workbook name appears at the top of the window instead of at the top of the Excel workspace. (For more information about maximizing and minimizing your Excel workbook, see "Resizing the Window" on page 29.)

Getting Around in the Workbook

At the bottom of the workbook window are controls you can use to move from worksheet to worksheet in a workbook. Figure 2-5 shows these navigational controls.

Figure 2-5: Use the workbook navigational controls to move among undisplayed worksheets.

You need the tab-scrolling buttons shown in Figure 2-5 only when your workbook contains more sheet tabs than can be displayed at once.

If you have one, you can use the wheel on your Microsoft IntelliMouse pointing device (or any wheel-equipped mouse) to scroll through your worksheet. Turn the wheel toward you to scroll down or away from you to scroll up. To scroll left to right, press the wheel button, and drag the mouse in the direction you want to move. (This is alternatively referred to as panning.) When you press the wheel button, a gray directional device appears, which is anchored to the spot where you first pressed the wheel button. The speed of panning depends on how far you drag away from the anchored directional device. As you press the button down and drag, a black arrow appears, pointing in the direction you're dragging:

You can change the default behavior of the wheel from scrolling to zooming. To do so, click the Microsoft Office Button, click Excel Options, and in the Advanced category, select the Zoom On Roll With IntelliMouse check box.

For more information, see "Zooming Worksheets" on page 154.

Workbook Navigation Tips

Many features and controls can help you navigate through the rows, columns, and worksheets in a workbook. Here are the highlights:

• Use the sheet tab navigation buttons to view all the sheet tabs in your workbook; click a tab to view the contents of that worksheet.

• Drag the tab split box to the right if you want to see more sheet tabs at the expense of the horizontal scroll bar width. To return to the usual tab display, double-click the tab split bar.

• Press Ctrl+Page Down to activate the next worksheet in the workbook; press Ctrl+Page Up to activate the previous worksheet.

• Press Ctrl+Home to jump to cell A1 from anywhere on a worksheet.

• Right-click any scroll bar to display a shortcut menu dedicated to scrolling actions, as shown in Figure 2-6.

• Drag the scroll box (also known as the scroll thumb) to move around the worksheet. Click the scroll bar anywhere outside the scroll box to move one screen at a time in that direction.

• The size of the scroll box changes depending on the size of the scrollable area. For example, the scroll boxes shown in Figure 2-6 are more than half as large as the scroll bars themselves, indicating there is little more to see in the active area of the workbook-nothing, in fact, because this is a blank workbook. As you add data to more columns and rows than can appear on a single screen, the scroll boxes get proportionally smaller, giving you immediate feedback about the size of the worksheet.

• Using the scroll arrows at either end of the scroll bars, you can move through the worksheet one column or row at a time.

• The Name box at the left end of the formula bar always displays the active cell reference, regardless of where you scroll the window.

• To scroll the worksheet without changing the active cell, press Scroll Lock. For example, to scroll to the right one full screen without moving the active cell, press Scroll Lock, and then press Ctrl+Right Arrow.

Figure 2-6: Right-click a scroll bar to display a shortcut menu of navigational commands. Only the active workbook window has scroll bars.

Resizing the Window

At the right end of the workbook window title bar are the Minimize, Maximize/Restore, and Close buttons. When your workbook window is maximized, the active window opens at full size in the Excel workspace.

After you maximize the window, a button with two small boxes-the Restore button-takes the place of the Maximize button. When you click the Restore button, the active window changes to a floating window.

Inside Out-See More Rows on Your Screen

You can set the Windows taskbar at the bottom of the screen to automatically hide itself when not in use. Click the Windows Start button, click Control Panel, click Appearance And Personalization, and click Taskbar And Start Menu (in Windows XP, just click Taskbar And Start Menu in the Control Panel). On the Taskbar tab, select the Auto-Hide The Taskbar check box, and then click OK. Now the taskbar stays hidden and opens only when you move the pointer to the bottom of the screen.

When you click the Minimize button (the one with a small line at the bottom), the workbook collapses to a small title bar.

Minimizing workbooks is a handy way to reduce workspace clutter when you have several workbooks open at the same time. Click the Restore button on the title bar to display the workbook at its former floating size, or click the Maximize button to make the workbook fill the Excel workspace.

You can also drag the borders of a floating window to control its size. The smaller the window, the less you see of the worksheet; however, because you can open multiple windows for the same workbook, you might find it more convenient to view different parts of the workbook, or even different parts of an individual worksheet, side by side in two small windows rather than switch between worksheets or scroll back and forth in one large window.

Inside Out-Microsoft and the SDI

No, we're not talking about the Strategic Defense Initiative (a.k.a. Star Wars). The single document interface (SDI) initiative that Microsoft implemented in its Office programs a couple of versions ago is, for the first time, an option in Office Excel 2007. Prior to SDI, regardless of the number of documents you had open, the applications were visible and available for task switching in Windows only by pressing Alt+Tab or by using the Windows taskbar. If you had three Excel worksheets open, you saw only one instance of Excel.

Microsoft's SDI initiative dictates that each document generates its own window, each of which becomes a separate item on the taskbar. Open three Excel worksheets, and you'll see three items on the taskbar. This is arguably a more realistic way to handle documents, which is why Microsoft did it in the first place. Some, however, might prefer the old method, which reduces the number of open windows on the desktop. In Excel 2007, you have the ability to choose between multiple document interface (MDI) and SDI. (By the way, you'll never see these terms used anywhere-for good reason: Multiple creates a single window, and single creates multiple windows. Geek double-speak!) To change from SDI (the default) to MDI, click the Microsoft Office Button, and then click the Excel Options button to display the dialog box of the same name. In the Personalize category, in the Top Options For Working With Excel area, clear the Show All Windows In The Taskbar check box, and then click OK to save your changes. Doing so causes only one Excel item to appear on the Windows taskbar regardless of how many workbooks you have open.

Exploring the Ribbon

After you get the raw data into Excel by whatever means, you'll be spending a lot of time using the Ribbon to massage and beautify your data. The Ribbon is one of the most ambitious user interface (UI) changes ever attempted, and it redefines the workflow in every 2007 Microsoft Office system program. You'll take a quick look here, but rest assured the Ribbon will be a hot topic throughout this book. Figure 2-7 shows the Ribbon at rest.

Figure 2-7: The Ribbon, which includes what used to be called the menu bar, dominates the top of the Excel window.

The Ribbon comprises a number of tabs, each containing several Ribbon groups, which in turn contain sets of related controls: commands, buttons, menus, galleries, and Dialog Box Launchers. The hierarchy within and among Ribbon tabs was designed to approximate a general "workflow" model, with the most often used features and options stacked more or less from left to right within and among tabs. For example, the Home tab contains commands you need when you create a new worksheet and start performing tasks such as cutting and pasting, formatting, and sorting. Well to the right of the Home tab, the Review tab contains commands relating to documents that are more or less complete, addressing issues such as verifying the spelling and protecting the document.

The Ribbon Speaks

For years now, many of us in the computer-book writing business have been anticipating early retirement due to the expected advent of stunningly simple user interfaces and the holy grail of "self-documenting" software. In reality, this goal has proven as elusive as the "paperless office," so we've kept as busy as ever. But this release of the 2007 Microsoft Office system represents another fine attempt at rendering our jobs obsolete. You can gain helpful information about your immediate surroundings by simply brandishing your pointer. For example, the left side of Figure 2-8 shows the ScreenTip that opens when you rest the pointer anywhere in the Number Format drop-down list. The right side of Figure 2-8 shows what happens when you click the Number Format drop-down list.

Figure 2-8: Rest your pointer on an object on the Ribbon to display an explanatory ScreenTip. Click any drop-down list on the Ribbon to display a menu, list, or gallery of options.

The icons representing the various options in the Number Format drop-down list shown in Figure 2-8 are another step in the right documentation direction, although further exploration might be required to discover the meaning of items such as the cryptic "12" icon for Number format. (See "Using Accounting Formats" on page 301.) And, as always, you can press F1 at any time to open the Excel Help window and gain additional insight.

Drop-Down Lists and Dialog Box Launchers

The Number group on the Home tab shown in Figure 2-8 contains the aforementioned Number Format drop-down list, four regular buttons, and a menu button (the $ sign), which acts like any other button when you click the button proper, but when you click the arrow next to it, displays a drop-down list of alternate actions for that button. Anytime you see an arrow directly to the right of a button or a box on the Ribbon, clicking the arrow reveals more options. (Just to confuse the issue a bit, the Decrease Font Size button in the Font group has an identical arrow, which is actually part of the button and doesn't invoke a drop-down list.)

Many groups display a tiny button in the lower-right corner called a Dialog Box Launcher. This is a visual cue telling you there is more you can do there. When you rest the pointer on a Dialog Box Launcher, a ScreenTip opens with details about its function; click the Dialog Box Launcher to display the promised result, as shown in Figure 2-9.

Figure 2-9: Rest the pointer on a Dialog Box Launcher button for an explanation of its function; click the button to open the corresponding dialog box.

Sometimes Dialog Box Launchers actually launch dialog boxes, as shown in Figure 2-9; other times clicking the Dialog Box Launcher displays a task pane on the side of the window, as shown in Figure 2-10. In the latter case, the Dialog Box Launcher button acts as a toggle-that is, clicking it opens the task pane, and clicking it again closes the task pane.

Figure 2-10: Clicking some Dialog Box Launcher buttons causes a task pane to open on the side of the window.

Another way to access advanced options relegated to dialog boxes is to look for commands listed at the bottom of menus and galleries sporting an ellipsis (...). For example, at the bottom of the Number Format drop-down list shown in Figure 2-8 is a command called More Number Formats. Just like the Dialog Box Launcher, you can click this to display the Format Cells dialog box. As was the case in previous versions of Excel, an ellipsis adjacent to the name of a command indicates that clicking that command displays a dialog box with additional options rather than immediately issuing the command.

Galleries and Live Preview

The concept of galleries goes way back, even if the term is new to Office Excel 2007. The idea is to provide a visual clue about what's going to happen when you click something, besides just the name of a command or button. Microsoft has done this with fonts for some time now. When you click the Font menu or drop-down list, each font name appears in its own font. Excel 2007 includes a bunch of other galleries that provide similar visuals and adds nifty functionality called live preview. Taking the same example one step further, Figure 2-11 shows the Font drop-down list displaying the available fonts "in situ." With live preview, you simply rest the pointer on the font name to momentarily cause selected cells to display that font. (In the figure, the entire worksheet is selected.)

Figure 2-11: Not only are font names displayed in their respective fonts in the drop-down list, but simply resting the pointer on a font name temporarily displays that font in selected cells.

As you can see in Figure 2-11, perhaps Algerian isn't the best font for a table of sales totals, but it's easy to get a look at a lot of options this way. The cell contents are not affected; this is simply a way to visualize what will happen if you actually commit by clicking. Have some fun by dragging the pointer up and down the list of fonts and watching them change almost as fast as you can drag.

The dialog box that opens when you click the Microsoft Office Button and then click Excel Options is probably the most important. As you can see, the Excel Options dialog box contains options that control nearly every aspect of Excel, including general settings such as how many worksheets appear in a default workbook and the name and point size of the default font:

You'll see little i (for information) icons adjacent to many of the items shown in the dialog box; rest the pointer on them to display ScreenTips, as shown previously. The Excel Options dialog box also provides special settings for default file-saving formats, worksheet-level and workbook-level display settings, and many other hard-to-classify options. If you take a moment to click each tab on the left side of the dialog box and look through the options available, you'll get an idea of the scope of the program as well as the degree of control you have over your workspace. If you're unsure about what a particular setting or option does, simply click the Help button (the question mark icon) in the title bar of the dialog box to open the Help system.

Introducing Contextual Tool Sets

Microsoft has been dancing with context sensitivity for several releases now. In 2000, Excel shipped with default "learning" menus and toolbars that modified themselves based on usage patterns, which turned out to be somewhat unpopular because commands would tend to "disappear" with lack of use. Some of this functionality carried through to Excel 2003, with a somewhat better implementation. The context sensitivity built into Office Excel 2007 is smarter and, best of all, does not take little-used items away like the previous approaches-in fact, the Ribbon and its normal contents remain steadfast, while additional context-triggered tools appear on Ribbon tabs that display only when needed. Figure 2-12 shows what happens when you click a chart object.

Figure 2-12: When you select an object, tabs appear containing tools that apply only to that object. Here, three tabs of chart tools appear on the Ribbon when a chart is selected.

Not only do three new tabs appear on the Ribbon in Figure 2-12-Design, Layout, and Format-you'll also see a higher-level heading, entitled Chart Tools, above the new tabs. These headings appear over sets of contextually triggered tabs to define their overall function. Chart objects are complex enough that clicking one triggers several tabs' worth of contextual tools; other objects might generate only one tab. This functionality helps reduce clutter in the interface, taking groups of task-specific tools out of the way until you need them.

Where Is the File Menu?

It's probably one of the three things you first notice once you start working with Office Excel 2007. At first, it looks like the old menus are across the top of the screen, just like before, until you start clicking and you see that no menus are dropping down. And then you notice that the "menu" names are different from what you remember. And just as you try to open one of your trusty old Excel files, it hits you-where's the File menu?

A new File menu is in town, called the Microsoft Office Button, which sports a big Microsoft Office logo. It's the big orb in the upper-left corner of the screen, as shown in Figure 2-13.

Figure 2-13: The new File menu is an orb with the 2007 Microsoft Office logo on it.

As you can see, even the one "menu" left doesn't really look much like a menu, but many of the old File menu commands are still here as clickable items on the left side of the menu. The Excel 2003 File menu used to be the longest menu in all the land! So, the new Microsoft Office Button is a definite organizational improvement.

Using the Quick Access Toolbar

It's hard not to think of the Ribbon as a toolbar, since anyone who has used Microsoft Office programs in the past 10 years or so has gotten accustomed to them and to the term. The Ribbon is not a toolbar according to Microsoft, and only one "real" toolbar is left. It's at the top of the screen, and it's now called the Quick Access Toolbar, as shown in Figure 2-14.

Figure 2-14: Meet the lone survivor of the Great Toolbar Massacre of 2006, the Quick Access Toolbar.

The Quick Access Toolbar is pretty much like toolbars as you knew them, with a few exceptions. You can dock it in only two locations-either above or below the Ribbon-unlike previous toolbars that could "float" over the worksheet or be docked at the top, bottom, or sides of the screen. And you cannot close or hide the Quick Access Toolbar.

Inside Out-Customizing the UI

Some of us have spent time customizing the UI of Excel and other Microsoft Office programs by changing and adding toolbars and menus. Office Excel 2007 has only one toolbar, you cannot create new ones, and you cannot mess with the Ribbon at all. But perhaps not surprisingly, 98 percent of the Excel-using public will not miss this functionality. (This is an accurate number gleaned from usability surveys!) For those of us in the other 2 percent, we won't be getting our toolbars back, but we can still customize the Quick Access Toolbar, and for the more adventurous among us, Ribbon customization will be made possible with the help of the Microsoft Developer Network. Tweaking your UI has changed from being a procedure anyone could tackle by taking a peek "under the hood" and has moved further "behind the curtain," geared to those with a working relationship with Visual Basic for Applications (VBA). Visit MSDN.com, and check out the Ribbon tools.

Accessing Commands with the Keyboard

When you press the Alt key, Excel activates keyboard command mode and displays little pop-up labels adjacent to each tab and toolbar button, as shown at the top of Figure 2-15.

Figure 2-15: Press the Alt key to activate keyboard command mode and display pop-up labels showing you the keys you can press to activate the respective tab, button, or command.

For example, after you press the Alt key to activate the pop-up labels, you can press the N key to display the Insert tab and add pop-up labels to the commands it contains. Then, press the M key-the pop-up letter adjacent to the SmartArt button-to display the SmartArt dialog box, as shown in Figure 2-15. So, instead of reaching for the mouse, simply pressing Alt, N, M gets you there. This makes for extremely fast command access after you've learned the right keys for tasks you do often.

You can use the slash (/) key just like the Alt key to access the Excel command structure. But you can alternatively set a different key to activate menus. Click the Microsoft Office Button, click Excel Options, select the Advanced category, scroll down to the Lotus Compatibility options, and then type a different character in the Microsoft Office Excel Menu Key text box.

The Joy of Shortcut Menus

Shortcut menus contain only those commands that apply to the item indicated by the position of the pointer when you activate the menu. Shortcut menus provide a handy way to access the commands most likely to be useful at the pointer's current location and to help minimize mouse movements (which are hard on wrists!).

To access a shortcut menu, right-click. The menu opens adjacent to the pointer, as shown in Figure 2-16.

Figure 2-16: Right-clicking displays a shortcut menu.

Shortcut menus can contain many combinations of commands, depending on the position of the pointer and the type of worksheet. For example, if you display a shortcut menu when the pointer is on a cell rather than a column heading, some of the commands change to ones specific to cells rather than columns.

A new feature added in Office Excel 2007 is the Mini toolbar, a small floating toolbar that opens along with the shortcut menu whenever the selected object can contain any kind of text, as you can see in Figure 2-16. You can control whether the Mini toolbar appears from the Excel Options dialog box. Click the Microsoft Office Button, click Excel Options, and in the Personalize category, select or clear the Show Mini Toolbar On Selection check box.

Meet the Formula Bar

Worksheet cells are the building blocks of Excel. They store and display the information you enter on an Excel worksheet so you can perform worksheet calculations. You can enter information directly in a cell, or you can enter information through the formula bar, as shown in Figure 2-17.

Figure 2-17: The formula bar displays the contents of the active cell.

The contents of the active cell appear in the formula bar, and the active cell address appears in the Name box at the left end of the formula bar. The formula bar split handle and the Insert Function button are always available, but the other two formula-editing buttons appear only while you are entering or editing data in a cell, as shown in Figure 2-17. Clicking the Cancel button cancels the current action in the cell and is the same as pressing the Esc key. Clicking the Enter button enters the current action in the cell and is the same as pressing the Enter key (except that pressing the Enter key also usually activates the cell directly below the active cell). You can drag the formula bar split handle to the left to make more room for formulas or to the right to increase the size of the Name box. Clicking the Insert Function button displays a dialog box that helps you construct formulas. For information about creating formulas and using the Insert Function dialog box, see Chapter 12, "Building Formulas."

A new wrinkle in Office Excel 2007 is the ability of the formula bar to expand and contract to display or hide long formulas. The formula bar split handle helps a bit, but for really long formulas the formula bar is vertically expandable, as shown in Figure 2-18. Previously, long formulas would cause the formula bar to open and obscure worksheet data, so this is a definite improvement.

Figure 2-18: The formula bar expands and contracts to show or hide long formulas.

When you select a cell containing a long formula, you'll see only part of the formula in the formula bar. If there is more formula to be seen, Excel displays a set of up and down arrows at the right end of the formula bar, which you can use to scroll through the formula one line at a time. At the far right end of the formula bar is the Expand Formula Bar button sporting a chevron, which, when clicked, expands the formula bar. If you need to see even more of the formula, you can drag the bottom border of the formula bar as far as you need, as shown in Figure 2-18. (This particular 100-cell formula is good for illustrating the expanding formula bar, but failure to use the SUM function in this situation might get you drummed out of the Sensible Formulas Guild.) The next time you click the Expand Formula Bar chevron, the bar expands to the last size you specified.

Facts About the Status Bar

The status bar, located at the bottom of the Excel window, displays information about what's happening in your workspace. For example, most of the time, Excel displays the word Ready at the left end of the status bar. When you type, the status bar displays the word Enter; when you double-click a cell that contains data, the status bar displays the word Edit.

Several items appear at the right end of the status bar: several buttons, a slider, and a display area for various purposes including summary information, keyboard modes, page numbers, and much more, any of which you can turn on or off. Right-click the status bar anywhere to show the Customize Status Bar shortcut menu filled with options, as shown in Figure 2-19.

Figure 2-19: You have numerous options for displaying information on the status bar.

All the commands on the Status Bar Configuration menu with check marks adjacent to them are turned on by default. Most of the commands on this menu control the display of different types of information depending on what is selected. Cell Mode refers to the aforementioned Ready/Enter/Edit indicators when working in cells. The icon that looks like a spreadsheet with a tiny red ball shown in Figure 2-19 on the left side of the status bar is a record button for macros. The Macro Recording command controls its display. (If any macros are available, a Play arrow appears next to the icon, the display of which is controlled by the Macro Playback command.) The last three commands on the menu control the display of items on the right end of the status bar. View shortcuts are the three buttons (Normal, Page Layout View, and Page Break Preview) visible next to Zoom percentage and the Zoom slider, which also have corresponding commands controlling their display. Drag the Zoom slider to change the percentage, or click the percentage indicator to display the Zoom dialog box for more precision.

For more information about keyboard modes, see "Navigating Regions with the Keyboard" on page 126. For more about views, see Chapter 11, "Printing and Presenting."

When you select two or more cells that contain values, Excel displays summary information using those values on the status bar:

This is the AutoCalculate feature. The AutoCalculate area of the status bar usually displays the sum and average of the selected values, as well as the number of cells selected that contain any kind of data (blank cells are ignored). As you can see in Figure 2-19, additional AutoCalculate options are available, including Minimum and Maximum values in selected cells, and Numerical Count, which counts only cells containing numbers and ignores cells containing text.