Microsoft Office Excel 2003 Programming Inside Out (Inside Out (Microsoft))

Having a powerful spreadsheet program at your fingertips doesn’t do you any good if you’re not able to find what you’re looking for, so the Excel designers spent a lot of time working on the organization and appearance of the Excel interface to make it easier for users to get the most out of their workbooks. Figure 2-4 shows the Excel window in all its glory, with callouts for the elements most commonly used in using and programming Excel.

Figure 2-4: The Excel interface offers quick access to the program’s diverse capabilities.

Starting with Excel 2002, the program came with an adaptive menu system that just displayed the most basic commands, requiring you to hover the mouse pointer over the bottom of a menu to expand the menu. It’s a great idea in theory, but for most users, who don’t have thorough knowledge of the program, it’s quite a hindrance. You can, and should, turn off the adaptive menu system by clicking Tools, Customize, Always Show Full Menus.

Title Bar.The title bar is at the top of the Excel window and displays the name of the program (Microsoft Office Excel 2003) followed by the name of the active workbook, if any. If the active workbook has any access restrictions or is shared, the workbook name will be followed by a designation in brackets. If you’ve opened more than one copy of the same workbook, the first name will be followed by :1, the second by :2, and so on.

Select All Button.The Select All button is the bare piece of real estate to the left of the Column A header and above the Row 1 header; clicking it selects every cell in a worksheet.

Command Bars.Menu bars and toolbars might look different, but underneath they work exactly the same way. Clicking File, Save is no different from clicking the Save toolbar button, so why have both systems, plus the Ctrl+S keyboard shortcut, in place? Because different users prefer different methods.

Formula Bar.The formula bar, the long, white strip just above the column headers in a worksheet, is where Excel displays the formula in the active cell. If there is no formula in the active cell, the cell’s value appears on the formula bar. You can hide the formula bar by clicking View, Formula Bar, which is a toggle (that means you click the bar to turn it off, and click again to turn it back on).

Name Box.The name box is in the running as the most versatile element in the Excel interface. If you have created a named range, as described earlier in this chapter, you can select a range by clicking the down arrow to the right of the name box and clicking the name you want to select (thereby outlining the cells in the named range). When you don’t have a named range selected, the name box displays the reference of the active cell (for example, A1), or the area selected while dragging. For instance, if you select an area three rows high by ten columns wide, the name box displays 3R × 10C until you release the mouse button, and then the name box displays the reference of the cell in the upper-left corner of the selected range.

• Clip Art.The Clip Art task pane gives you links you click to search for clip art in the Office gallery, to organize clips you have saved, and to get help on topics related to clip art.

• Clipboard.The Clipboard task pane is the new home for the Office Clipboard. The Office Clipboard can hold up to 24 items you have copied or cut to the clipboard.

• Document Recovery.If Excel crashes while you have a workbook open, the Document Recovery task pane appears when you re-open Excel. You’ll have the chance to choose whether you want to revert to the most recently saved version of the workbook or to recover the workbook using the last AutoRecover file.

• Document Updates.Used with SharePoint Services, the Document Updates task pane gives you the ability to monitor workbooks you have placed in a shared workspace for changes.

• Getting Started.The Getting Started task pane contains links for opening a recently used file, creating a new workbook, or connecting to the Microsoft Office Web site.

• Help.The Help task pane has links to Microsoft support newsgroups, to information about assistance and training for Microsoft Office, and find out what’s new in Excel 2003.

• New Workbook.The New Workbook task pane lets you use templates to create a new workbook, search for workbook templates on your computer or on the Microsoft Office site, or create a blank workbook.

• Research.The Research task pane has links to a range of tools you can use to look up words and phrases. There are dictionaries and thesauruses available in several languages, and there is a “translation” utility you can use to find corresponding terms in French, Portuguese, Italian, and other languages.

• Search Results.The Search Results task pane lets you search the Help system, Microsoft.com, the Office Support site, or online research tools for a word or phrase you type in the Search box.

• Shared Workspace.Also part of the new Excel 2003 support for SharePoint Services, the Shared Workspace task pane contains a set of hyperlinks you can use to create and manage workspaces where you and your colleagues can collaborate on a shared document.

• XML Source.The XML Source task pane contains a set of tools that let you assign XML structure to a worksheet so that you can exchange XML-formatted data with colleagues and clients.

Tab Bar.The tab bar displays a sheet tab for each worksheet in a workbook. You can navigate from sheet to sheet using the arrows on the tab bar, click the sheet tab of a worksheet to display that sheet, or change the color of the sheet tab.

Status Bar. As the name implies, the Excel status bar displays the program’s status. The status bar indicates whether a save or AutoRecover save is in progress, displays the running total of values in selected cells, and tells you, among other things, if your keyboard has caps lock, scroll lock, and/or number lock turned on.

The running total feature is a little-known but very handy way to quickly summarize a few cells of data in Excel. When you select more than one cell, a summary of the data appears on the status bar a bit to the left of the number lock indicator. The default summary operation is to find the sum of the data, but you can right-click the pane where the summary appears and, from the shortcut menu, click Average, Count, Count Nums (that is, the number of cells in the selection that contain a numeric value), Maximum, Minimum, or Sum. If you want to turn the feature off, you click None. To turn it back on, just right-click the same area of the status bar and select the new summary operation.