List of Figures

Chapter 1: What's New in Microsoft Office Excel 2007

Figure 1-1: Excel 2007 looks and operates differently than previous versions. For example, the big orb with the Microsoft Office logo in the upper-left corner replaces the File menu.

Figure 1-2: The SmartArt gallery gives a clue to the kind of visual enhancements added in Excel 2007.

Figure 1-3: Rest the pointer on most palettes to see live previews of the effects on selected cells or objects.

Figure 1-4: Page Layout view is a welcome new feature for fine-tuning printed output.

Figure 1-5: Icon sets give you quick visual clues about relative values within tables of data.

Figure 1-6: Microsoft has improved PivotTables and PivotCharts, and the new interface makes it easier to work with them.

Figure 1-7: Type an equal sign and any letter (or letters) to display an AutoComplete function list with descriptive ScreenTips.

Figure 1-8: Use the Name Manager dialog box to audit and organize named ranges in your workbooks.

Figure 1-9: Charting is easier and flashier than ever.

Figure 1-10: Filter is more automatic and provides more powerful filtering than ever.

Chapter 2: Exploring Excel Fundamentals

Figure 2-1: If you don't do it when you first start Excel, you can use the Excel Options dialog box to activate the program later.

Figure 2-2: Click Check For Updates to keep your Microsoft products current.

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

Figure 2-4: Workbooks initially comprise three worksheets.

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

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

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

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.

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.

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

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.

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.

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

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

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.

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

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

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

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

Figure 2-20: Click the Microsoft Office Button and then click New to display the New Workbook dialog box.

Figure 2-21: You create a copy of any file you select in the New From Existing Workbook dialog box.

Figure 2-22: A number of templates are ready and waiting on your computer when you first install Excel.

Figure 2-23: Microsoft Office Online provides a fortune in template treasures from which to choose.

Figure 2-24: You can add your own templates to the New dialog box.

Figure 2-25: It's easy to create your own tabs in the New dialog box.

Figure 2-26: The Save As dialog box opens when you save a file for the first time.

Figure 2-27: Files created by any previous version of Excel open in Compatibility mode.

Figure 2-28: The Compatibility Checker opens when you try to save an old-format workbook containing features not supported by the older version of Excel.

Figure 2-29: You can specify the default format to use when saving.

Figure 2-30: To display the General Options dialog box, click the Microsoft Office Button, click Save As, and then click Tools, General Options.

Figure 2-31: Use the Properties Ribbon to add descriptive information you can use later when looking for that needle in a file stack.

Figure 2-32: View properties about any file before you open it.

Figure 2-33: Use named cells to create dynamic properties based on worksheet cells.

Figure 2-34: Click the arrow next to the Open button for more choices when opening documents.

Figure 2-35: You can specify additional criteria when looking for files in the Open dialog box, using the menu buttons adjacent to each heading in the file list.

Figure 2-36: The Open And Repair command gives you a ray of hope for recovering lost data.

Figure 2-37: The Extract Data button offers two ways to recover your data.

Figure 2-38: Use the File Name drop-down list to specify files created by a particular application.

Figure 2-39: The Web Page file format creates a folder full of supporting files to go with the main Web page.

Figure 2-40: Rest the pointer on any command or button on the Ribbon or the Quick Access Toolbar to display a ScreenTip.

Figure 2-41: A little circle icon containing an i means you can see more information by simply resting the pointer on the icon.

Figure 2-42: The Help dialog box attempts to connect to the Web when you first open it.

Figure 2-43: Your Help system works a bit differently if you are not connected to the Web.

Figure 2-44: Click the Table Of Contents Button to display or hide the navigation pane.

Figure 2-45: Narrow your results using the Search menu.

Figure 2-46: With AutoRecover, you can specify how often Excel will automatically save your work.

Figure 2-47: The Microsoft Office Diagnostics dialog box gives you a chance to get your ailing Office installation back in shape.

Chapter 3: Custom-Tailoring the Excel Workspace

Figure 3-1: The Quick Access Toolbar usually appears above the Ribbon, on the title bar.

Figure 3-2: You can move the Quick Access Toolbar below the Ribbon.

Figure 3-3: Right-click any command or group, and you can add it to the Quick Access Toolbar.

Figure 3-4: The Customize category in the Excel Options dialog box is the command center for the Quick Access Toolbar.

Figure 3-5: Each item in the Choose Commands From drop-down list corresponds to a tab on the Ribbon.

Figure 3-6: When you select an object on the worksheet, additional tabs appear that correspond to items in the Choose Commands From drop-down list.

Figure 3-7: ScreenTips help you identify commands in the list.

Figure 3-8: If you add more buttons than can be displayed, click the More Controls button.

Figure 3-9: You can add custom buttons to run macros in macro-enabled workbooks.

Figure 3-10: You can change the default button image for your custom macro-driven buttons.

Figure 3-11: You can configure a custom version of the Quick Access Toolbar that travels with a workbook.

Figure 3-12: You can easily remove any button added to the Quick Access Toolbar.

Figure 3-13: The Mini toolbar appears when you right-click cells or relevant objects.

Figure 3-14: The View tab on the Ribbon contains commands you can use to control the appearance of your workbook.

Figure 3-15: The Advanced category in the Excel Options dialog box includes a number of display options.

Figure 3-16: Display underlying values and formulas for easier auditing.

Chapter 4: Security and Privacy

Figure 4-1: The Trust Center dialog box controls many security settings.

Figure 4-2: Security alerts appear in the Message Bar. Click the Options button in the Message Bar to learn more about the active content in the file.

Figure 4-3: Using the options in the Add-Ins, ActiveX Settings, and Macro Settings categories, you can enable or disable most types of active content that you might encounter.

Figure 4-4: The External Content category contains settings that control data connections and links.

Figure 4-5: If you are uncomfortable with Excel connecting automatically to the Web, you can specify otherwise.

Figure 4-6: The Document Inspector examines the hidden places in your workbooks where personal data can hide.

Figure 4-7: You can add digital signatures to your workbooks.

Figure 4-8: For what it's worth, you can create your own digital signature.

Figure 4-9: The Signatures task pane appears when you attempt to edit a workbook after adding a signature.

Chapter 5: Planning Your Worksheet Design

Figure 5-1: Monthly total worksheets are often oriented vertically, as shown here.

Figure 5-2: Worksheets are often harder to view and print when oriented horizontally.

Figure 5-3: If showing all the detail data is too cumbersome, you can create summary sheets for reporting purposes.

Figure 5-4: You can use outlining to hide the detail for summary purposes.

Figure 5-5: Make sure critical worksheets are understandable and well documented.

Figure 5-6: You can put raw imported data on its own worksheet and use a formatted worksheet to present the pertinent information.

Chapter 6: How to Work a Worksheet

Figure 6-1: The four blocks of cells on this worksheet are separate regions.

Figure 6-2: Use the Zoom slider or the Zoom dialog box to view large areas of a worksheet for easier selection.

Figure 6-3: Hold down the Ctrl key and drag to select multiple-area ranges with the mouse.

Figure 6-4: Select entire columns and rows by clicking their headings, or hold down the Ctrl key while clicking to select nonadjacent rows and columns.

Figure 6-5: Use the Find & Select commands to zero in on specific items.

Figure 6-6: The Go To and Go To Special dialog boxes are your selection transporters.

Figure 6-7: When you start entering a formula by typing an equal sign, the formula bar offers ways to help you finish it.

Figure 6-8: Because the number 123,456,789,012 is too long to fit in either cell A1 or cell B1, Excel displays it in scientific notation.

Figure 6-9: Click the Wrap Text button to force long text entries to wrap within a single cell.

Figure 6-10: We typed the policy numbers in column A as text.

Figure 6-11: You can insert characters from the extended character sets of any installed font.

Figure 6-12: You can easily make entries in a range of cells by first selecting the entire range.

Figure 6-13: Click the small arrow next to the Undo button to select any number of the last 16 actions to undo at once.

Figure 6-14: To insert a blank worksheet, click the Insert Worksheet tab, or right-click any sheet tab to display a worksheet-focused shortcut menu.

Figure 6-15: Double-click the sheet tab to type a new name. You might want to keep it short.

Figure 6-16: Click and drag sheet tabs to rearrange worksheets.

Figure 6-17: You can scroll to display the totals in column N or row 38, but you won't be able to see the headings.

Figure 6-18: With the window split, you can scroll each pane independently.

Figure 6-19: Freezing panes locks the top and/or left panes of a split window.

Figure 6-20: Click the Zoom To Selection button with the active area selected to view it all on the screen.

Figure 6-21: Click Add to name the current view and print settings in the Custom Views dialog box.

Figure 6-22: The Protect Sheet dialog box gives you pinpoint control over many common editing actions.

Figure 6-23: Click Format, Lock Cell to unlock specific cells for editing.

Figure 6-24: Use the Protect Structure And Windows dialog box to set the protection status for the entire workbook.

Figure 6-25: You can specify cells that can be edited, as well as the individuals who are allowed to edit them, by using the Allow Users To Edit Ranges dialog box.

Figure 6-26: Specify ranges you want to allow users to edit using the New Range dialog box.

Figure 6-27: Set permissions for individual users by clicking Permissions in the Allow Users To Edit Ranges dialog box.

Figure 6-28: Click Add in the Permissions dialog box to add to your list of authorized users.

Figure 6-29: Use the Hide & Unhide commands to protect parts of your workbooks.

Chapter 7: How to Work a Workbook

Figure 7-1: Clicking View, Arrange All opens the Arrange Windows dialog box, which gives you a choice of configurations.

Figure 7-2: These windows are arranged in the Horizontal configuration.

Figure 7-3: Clicking View, Full Screen hides the formula bar, status bar, Quick Access Toolbar, and Ribbon to maximize the screen space available for viewing your data.

Figure 7-4: If more than two windows are open, select one in the Compare Side By Side dialog box.

Figure 7-5: The Synchronous Scrolling button locks side-by-side window scrolling.

Figure 7-6: You can work on the summary worksheet while viewing supporting worksheets in the same workbook.

Figure 7-7: After clicking the New Window button to open a second window for the same workbook, select an Arrange option to fit both windows on the screen simultaneously.

Figure 7-8: You can change the display characteristics of one window without affecting the other.

Figure 7-9: You can radically change view options in one window while maintaining a regular view of the same worksheet in another window.

Figure 7-10: The Unhide dialog box lists all the workbooks you currently have hidden.

Figure 7-11: Clicking Review, Protect Workbook helps insulate your workbooks from inadvertent modification.

Figure 7-12: Applying a password to encrypt a workbook also turns on protection of the workbook structure.

Chapter 8: Worksheet Editing Techniques

Figure 8-1: The Clipboard task pane stores multiple items that you copy or cut.

Figure 8-2: Before you paste, select more cells than you copied to create multiple copies of your information.

Figure 8-3: Paste Special is probably the most popular power-editing feature, and its most often used options are available as commands on the Paste menu.

Figure 8-4: We used the Values option of Paste Special to add the totals from column D to those in column G.

Figure 8-5: We copied cells D2-E6, selected cell G2, and then clicked Home, Paste, Transpose to redistribute the rows of data into columns.

Figure 8-6: We created hyperlinks to supporting workbooks at the bottom of this worksheet.

Figure 8-7: Before you finish dragging, press Ctrl to copy the selection. A plus sign and destination reference appear next to the pointer.

Figure 8-8: The gray I-beam indicates where Excel will insert selected cells.

Figure 8-9: Right-click a row or column heading, and click Insert. Click the smart tag after you insert for some post-insertion options.

Figure 8-10: Click the Insert Cells command to choose the direction to move existing cells in your worksheet.

Figure 8-11: When you insert after copying or cutting cells, the Insert Paste dialog box appears.

Figure 8-12: Inserting previously copied or cut cells is faster than inserting cells and then copying or cutting data to fill the inserted range.

Figure 8-13: Use the Delete dialog box to choose the direction to move cells.

Figure 8-14: You can create errors when you delete the wrong cells.

Figure 8-15: The commands on the Clear menu remove the corresponding attributes of selected cells without removing the cells.

Figure 8-16: Drag the fill handle while pressing Shift to insert cells.

Figure 8-17: Drag the selection with the right mouse button to display a shortcut menu.

Figure 8-18: Click the arrow next to the Undo button to select and simultaneously undo up to the last 100 actions.

Figure 8-19: Double-click a cell containing a formula to edit it and to display range finders.

Figure 8-20: Click the Microsoft Office Button, Excel Options, Advanced to display editing-related workspace settings.

Figure 8-21: Copy the contents of a cell to adjacent cells by dragging the fill handle.

Figure 8-22: Create a simple series by dragging the fill handle and then clicking Fill Series on the smart tag action menu.

Figure 8-23: Create simple data series by selecting a single value and dragging the fill handle.

Figure 8-24: Specify data series intervals by selecting a range of values and dragging the fill handle.

Figure 8-25: We extended a series into B3-B5 by selecting B1-B2 and double-clicking the fill handle.

Figure 8-26: If you right-click and drag the fill handle, this shortcut menu appears when you release the mouse button.

Figure 8-27: We created a linear trend series in column A and a growth trend series in column C.

Figure 8-28: Use the Series dialog box for more control when creating a series.

Figure 8-29: Use the Fill menu for quick access to common fill actions.

Figure 8-30: Clicking Justify distributes the long label in cell A1 to cells A1-A5.

Figure 8-31: You can create custom lists that you can type by dragging the fill handle.

Figure 8-32: The automatic format extension adds data to an existing table without reformatting.

Figure 8-33: The AutoCorrect dialog box controls automatic format and formula extension when working in tables.

Figure 8-34: Use the Find tab to locate a character string.

Figure 8-35: Click Choose Format From Cell to use the formatting of a selected cell as search criteria.

Figure 8-36: Click Format in the Find And Replace dialog box to display the Find Format dialog box.

Figure 8-37: You can find and replace character strings and formats by clicking the Replace command on the Find & Select menu.

Figure 8-38: You can add your most common typing errors to the AutoCorrect dialog box.

Figure 8-39: The Smart Tags tab in the AutoCorrect dialog box is the control center for all your smart tag needs.

Figure 8-40: Excel includes built-in custom smart tag action menus for stock-ticker symbols and e-mail addresses, among other types of data.

Figure 8-41: Type enough letters to match an existing entry, and AutoComplete finishes it for you. As shown on the right, keep typing to override AutoComplete.

Figure 8-42: Right-click the cell directly below a list, and click Pick From Drop-Down List to display a list of unique entries in the column.

Figure 8-43: Use the Spelling dialog box to review your text and add often-used words to your dictionary.

Figure 8-44: Gain more control over spelling with the Proofing tab in the Excel Options dialog box.

Figure 8-45: The Research, Thesaurus, and Translate buttons on the Review tab open a task pane.

Figure 8-46: You can narrow your search to use specific resources.

Figure 8-47: Specify the research resources available using the Research Options dialog box.

Figure 8-48: The three worksheets are grouped.

Figure 8-49: Copying a group of worksheets creates the same number of new worksheets.

Figure 8-50: With group editing, Excel applies all edits and formats to all the worksheets.

Figure 8-51: Use the Fill Across Worksheets dialog box to copy selected data to all the worksheets in a group.

Figure 8-52: The Formula Auditing group on the Formulas tab provides access to most of the Excel 2007 auditing features.

Figure 8-53: The Error Checking dialog box helps you figure out what's wrong with formulas that display error values.

Figure 8-54: Click Options in the Error Checking dialog box to specify error-checking rules.

Figure 8-55: Click the Evaluate Formula button on the Formulas tab to systematically inspect nested formulas.

Figure 8-56: Each time you click the Evaluate button, Excel calculates another nested level in the selected formula.

Figure 8-57: Use Step In and Step Out to display calculable arguments separately.

Figure 8-58: Select a cell, and click Watch Window to keep an eye on it, no matter where you are currently working.

Figure 8-59: The Watch Window displays all the current information for the watched formula.

Figure 8-60: When you trace dependents, arrows point to formulas that directly refer to the selected cell.

Figure 8-61: When you click Trace Dependents again, arrows point to the next level of formulas, ones that indirectly refer to the selected cell.

Figure 8-62: When you trace precedents, arrows point from all the cells to which the formula in the selected cell directly refers.

Figure 8-63: Cells with error values display smart tag action menus.

Figure 8-64: Select a cell that contains an error value, and click Trace Error to display arrows that trace the error to its source.

Figure 8-65: If you trace the precedents of a cell that contains a reference to another worksheet or workbook, a special tracer arrow appears.

Figure 8-66: You can attach comments to cells to help document your worksheet.

Figure 8-67: Start with a hierarchical worksheet like this one.

Figure 8-68: Make the worksheet hierarchies collapsible using the Excel outlining features.

Figure 8-69: Two clicks transformed the outlined worksheet in Figure 8-68 into this quarterly overview.

Figure 8-70: Use the Settings dialog box to adjust for a nonstandard layout.

Figure 8-71: When you click the hide detail symbols (-) above Q1, Q2, and Q3, Excel replaces them with show detail symbols (+).

Figure 8-72: The default function in the Consolidate dialog box is Sum.

Figure 8-73: All the worksheets in this workbook are identical, which is necessary when consolidating by position.

Figure 8-74: The Consolidate command uses the references in the All References list to create the consolidated totals.

Figure 8-75: Range B5-I10 in the Consolidated worksheet now contains totals of the corresponding cells in the 12 supporting worksheets.

Figure 8-76: Use the categories in the left column of each source worksheet as the basis for this consolidation.

Figure 8-77: The Consolidate command created a separate line item in the consolidation worksheet for each unique item in the source worksheets.

Figure 8-78: When you create links to the source worksheets, the consolidation worksheet is outlined and linking formulas are created in subordinate outline levels.

Chapter 9: Worksheet Formatting Techniques

Figure 9-1: All entries in this worksheet are displayed in their default formats.

Figure 9-2: The formatted worksheet is easier to read.

Figure 9-3: The Home tab on the Ribbon and the Format Cells dialog box are your formatting toolboxes.

Figure 9-4: The Format As Table palette offers a selection of predefined formats you can apply with one click.

Figure 9-5: The Format As Table dialog box appears after you select a format in the Format As Table palette.

Figure 9-6: In seconds, you can transform a raw worksheet into something more presentable.

Figure 9-7: The Table Tools Design tab appears on the Ribbon whenever you select a cell in a table.

Figure 9-8: Drag the lower-right corner of a table to make it bigger.

Figure 9-9: Right-click any table format thumbnail to reveal a shortcut menu containing helpful options.

Figure 9-10: Click the New Table Style button on the Format As Table palette to create your own table styles.

Figure 9-11: The Convert To Range command removes the automatic table features but leaves the formatting.

Figure 9-12: Themes control the overall palette of available colors, fonts, and effects.

Figure 9-13: Changing the theme instantly changes the look of all the worksheets in the active workbook.

Figure 9-14: The Custom category is created in the Themes palette when you save a custom theme.

Figure 9-15: The Cell Styles palette offers categories of styles based on the current theme.

Figure 9-16: Use the Style dialog box to create your own cell styles.

Figure 9-17: Custom styles appear at the top of the Cell Styles palette.

Figure 9-18: Modify an existing cell style by right-clicking its thumbnail in the palette and clicking Modify.

Figure 9-19: Copy cell styles from any open workbook using the Merge Styles command.

Figure 9-20: The revamped conditional formatting features in Excel 2007 are more powerful and easier to use than previous versions.

Figure 9-21: Select the Less Than rule on the Highlight Cells Rules menu to create a stoplight chart using conditional formatting.

Figure 9-22: We created two conditions-one to flag high values and one to flag low values. These guys had a rough January.

Figure 9-23: We used the same top/bottom rule on two different selections, with different results.

Figure 9-24: You can rest the pointer on items on the Data Bars menu to see a live preview on your worksheet.

Figure 9-25: We used highlight cells in the body of this table and data bars in the Totals column.

Figure 9-26: Use the New Formatting Rule dialog box to construct your own conditional formats.

Figure 9-27: Use the second rule type in the New Formatting Rule dialog box to create your own highlight cells rules.

Figure 9-28: Use the Conditional Formatting Rules Manager dialog box to tweak any rules that have been applied in a workbook.

Figure 9-29: Click the Collapse icon in the Applies To text box to minimize the dialog box and allow direct selection of the cell range you want.

Figure 9-30: Use the Go To Special dialog box to locate all conditional formats or just matching ones.

Figure 9-31: Use the last rule type in the list to create your own conditional formatting formulas.

Figure 9-32: Click the Dialog Box Launcher in the Number group to display the Number tab of the Format Cells dialog box.

Figure 9-33: You can format individual characters within a cell.

Figure 9-34: Use the Number category for general-purpose, noncurrency numeric formatting.

Figure 9-35: Select Currency in the Number drop-down list to apply the default currency format.

Figure 9-36: The $ button applies a standard Accounting format and offers a few optional currency symbols.

Figure 9-37: It is standard practice to use currency symbols only in the top and bottom rows of a table.

Figure 9-38: When using percentages, it makes a difference whether you format before or after typing values.

Figure 9-39: Excel provides many fraction-formatting options.

Figure 9-40: Excel provides several frequently requested formats in the Special category.

Figure 9-41: Use the Custom category to create new formats using special codes.

Figure 9-42: We created a custom format by typing codes in the Type text box.

Figure 9-43: You can create your own four-part formats.

Figure 9-44: Excel can address most of your alignment needs via tools on the Ribbon.

Figure 9-45: Alignment means a lot more than just right, left, or justified.

Figure 9-46: Use the Horizontal alignment options to control the placement of text from left to right.

Figure 9-47: Use the Vertical options to control placement of text from top to bottom.

Figure 9-48: Use the Orientation menu to rotate or stack text in a cell.

Figure 9-49: The Shrink To Fit alignment option reduces the font size until the cell contents fit within the cell.

Figure 9-50: The Font group contains font-formatting controls, as well as border and fill controls.

Figure 9-51: On the Font tab you can assign fonts, character styles, sizes, colors, and effects to your cell entries.

Figure 9-52: Click the arrow next to the Borders button to display the Borders menu.

Figure 9-53: Using the Border tab, you can assign 13 styles of borders in 70 colors.

Figure 9-54: We created two boxes using the Draw Border command and another using the Draw Border Grid command.

Figure 9-55: Use the Fill Color palette to add color to cells.

Figure 9-56: Using the Fill tab, you can select colors and patterns for cell backgrounds.

Figure 9-57: Click the More Colors button on the Fill tab in the Format Cells dialog box to select the colors you need.

Figure 9-58: Click the Fill Effects button on the Fill tab in the Format Cells dialog box to use gradient fills in cells.

Figure 9-59: Add a background graphic to any worksheet.

Figure 9-60: You can use the Cell Size commands on the Format menu to manage row height and column width.

Figure 9-61: The cursor looks like a double-headed arrow when adjusting column width or row height with the mouse.

Figure 9-62: The Merge & Center button offers a variety of merge commands.

Figure 9-63: Most of the cells in the top five rows of this worksheet, and a couple in the sixth row, were merged in various combinations.

Figure 9-64: This template file serves as the basis for creating new expense reports.

Chapter 10: Creating Spiffy Graphics

Figure 10-1: The Insert tab contains most of the drawing tools.

Figure 10-2: The Shapes palette provides a wealth of graphic options.

Figure 10-3: New tabs appear, under Drawing Tools, when you select a graphic object.

Figure 10-4: When you create or size objects, hold down Shift to constrain them.

Figure 10-5: A sampling of objects created using the Curve, Freeform, and Scribble tools.

Figure 10-6: When you click Edit Points, handles appear at each vertex, as in the polygon on the right.

Figure 10-7: Change the type of a selected edit point by right-clicking it and then clicking commands on the shortcut menu.

Figure 10-8: Change the shape of freeform objects by dragging the vertex handles that appear when you click a point.

Figure 10-9: Text boxes are only one of the many graphic objects that can display text.

Figure 10-10: Many shapes have special diamond-shaped handles that you can drag to control a specific feature of the shape.

Figure 10-11: Drag the panhandle of any two-dimensional object to rotate it.

Figure 10-12: The connector tools appear in the Lines group on the Shapes palette.

Figure 10-13: Connector lines remain attached to the points where you place them, even when you drag the shape to a new location.

Figure 10-14: Callouts help you describe important items or call attention to important messages.

Figure 10-15: Use the WordArt button to create stunning logos and headings.

Figure 10-16: The Text Effects menu offers advanced formatting effects.

Figure 10-17: SmartArt graphics help you illustrate conceptual information.

Figure 10-18: You can type your text into the box on the left, and it appears in the graphic to the right.

Figure 10-19: Use the SmartArt Styles palette on the Design tab under SmartArt Tools to add graphic interest to your SmartArt.

Figure 10-20: Use the Clip Art task pane to add not only art but also photos, movies, or sounds to your worksheets.

Figure 10-21: The Preview/Properties dialog box gives detailed information about the selected clip.

Figure 10-22: The Microsoft Clip Organizer is the wizard behind the curtain of the Clip Art task pane.

Figure 10-23: Images copied from other programs can usually be pasted in more than one format using Paste Special.

Figure 10-24: You can use the Create New tab of the Object dialog box to simultaneously insert an object and start the program used to create it.

Figure 10-25: When you insert an object, the source program opens, but parts of the Excel interface are still visible.

Figure 10-26: Use the Create From File tab to insert existing files into your workbooks.

Figure 10-27: This version of the Format tab appears when a picture is selected.

Figure 10-28: This version of the Format tab appears when any Shape, Text Box, or WordArt object is selected.

Figure 10-29: The Font dialog box offers a few formatting options not found elsewhere.

Figure 10-30: Adjust the space between letters in graphic objects using the Character Spacing tab.

Figure 10-31: Apply compression settings to any imported picture.

Figure 10-32: The Format Shape dialog box offers central control of graphic-object formatting.

Figure 10-33: Each option causes different controls to appear in the dialog box.

Figure 10-34: You can create an infinite number of shadow effects using the Format Shape dialog box.

Figure 10-35: Start with a basic shape, and give it depth.

Figure 10-36: You can "extrude" 3-D bevels from both the front and the back of a flat shape.

Figure 10-37: The Recolor drop-down list offers special effects you can apply to bitmap images.

Figure 10-38: The Text Box category of the Format Shape dialog box offers controls not found on the Ribbon.

Figure 10-39: You can create columnar text boxes using the Format Shape dialog box.

Figure 10-40: Your formatting options are more limited with embedded objects.

Figure 10-41: Select a group of objects, and then right-click any one of them to display a shortcut menu of applicable commands.

Figure 10-42: You can reposition objects in relation to each other using buttons in the Arrange group on the Page Layout tab.

Figure 10-43: Use the Selection And Visibility task pane to control the display of multiple objects.

Figure 10-44: You can control how objects respond to changes on the worksheet.

Figure 10-45: Use the Align commands to straighten up objects.

Figure 10-46: You can lock objects and the text they contain to prevent changes when the workbook is protected.

Figure 10-47: Assigning macros to objects turns them into buttons.

Figure 10-48: The Camera button creates a linked image of a selected range.

Figure 10-49: Use the Copy As Picture command to create "screen shots" of selected areas of your worksheets.

Figure 10-50: This logo shows a different use for a diagram.

Figure 10-51: You can do a lot with text boxes.

Figure 10-52: This shows how you can use a background picture and some cartoon callouts.

Figure 10-53: You can use cell borders to create patterned backgrounds.

Figure 10-54: You can apply a picture to WordArt as a fill color

Chapter 11: Printing and Presenting

Figure 11-1: The Page Layout tab on the Ribbon and the Page Setup dialog box control most printing options.

Figure 11-2: Click the Margins button on the Page Layout tab to choose a basic margin setting.

Figure 11-3: You can specify precise margin settings on the Margins tab in the Page Setup dialog box.

Figure 11-4: You can choose from predefined headers and footers or create your own using the Header/Footer tab in the Page Setup dialog box.

Figure 11-5: The Header dialog box contains tools to make creating a header a snap.

Figure 11-6: You can manipulate the fonts and even add pictures to headers and footers.

Figure 11-7: Use the Sheet tab in the Page Setup dialog box to set the area to print and the row and column titles to print on each page of your worksheet.

Figure 11-8: We defined the print titles and used Page Layout view to see the results.

Figure 11-9: Use the Print dialog box to tell Excel what you want to print and how many copies you want.

Figure 11-10: Page Break Preview shows default page breaks with heavy dashed lines, which you can reposition by dragging.

Figure 11-11: You can display lines and handles that you can use to adjust the size of margins, headers, footers, and columns in Print Preview by clicking the Show Margins check box.

Figure 11-12: Learn more about XPS documents by clicking the link at the bottom of the Microsoft XPS Document Writer Properties dialog box.

Figure 11-13: You can view XPS documents in an XPS-enabled Web browser.

Chapter 12: Building Formulas

Figure 12-1: Enter external references easily by clicking the cell to which you want to refer.

Figure 12-2: Cell F4 contains relative references to the cells to its left.

Figure 12-3: We copied the relative references from cell F4 to cells F5:F8.

Figure 12-4: The formula in cell C5 contains relative references. We copied the relative formula in cell C5 to cells C6:C8, producing incorrect results.

Figure 12-5: We created an absolute reference to cell B2 before copying the formula.

Figure 12-6: This loan payment table uses formulas that contain mixed references.

Figure 12-7: Use the Sum button to add a summary formula in a cell adjacent to columns or rows of numbers.

Figure 12-8: The Insert Function dialog box gives you access to all the built-in functions in Excel.

Figure 12-9: When you start to type a function, Excel lists all the functions that begin with that letter or letters.

Figure 12-10: Use the Name box on the formula bar to quickly assign names to cells and ranges.

Figure 12-11: When you click Define Name on the Formulas tab, Excel suggests any label in an adjacent cell in the same row or column as a name.

Figure 12-12: The Name Manager dialog box provides central control over all the names in a workbook.

Figure 12-13: Use the Scope drop-down list to specify a worksheet to which you want to restrict a name's usage.

Figure 12-14: Use the Create Names From Selection dialog box to name several cells or ranges at once using labels.

Figure 12-15: Names you define appear in the Formula AutoComplete list when you type a formula.

Figure 12-16: Click the Use In Formula button, and select a name to enter it into the selected cell.

Figure 12-17: The Paste Name dialog box changes, depending on whether you are editing within a cell.

Figure 12-18: Click Paste List in the Paste Name dialog box to create a list of names and references starting at the active cell.

Figure 12-19: Use the Apply Names dialog box to substitute names for cell and range references in your formulas. Click Options to display all the options shown here.

Figure 12-20: Use the Go To dialog box to select a cell or range name so you can move to that cell or range quickly.

Figure 12-21: You can enter line breaks in a formula to make it more readable.

Figure 12-22: We created this table to illustrate the power of structured references.

Figure 12-23: We created the formula in cell H4 by dragging to select cells in the table.

Figure 12-24: Structured reference specifiers automatically appear in the AutoComplete drop-down list if they are applicable when creating a formula.

Figure 12-25: You can drag the fill handle to extend structured reference formulas into adjacent cells, but they behave a little bit differently than regular formulas.

Figure 12-26: We replaced the second structured reference with an absolute cell reference to make filling these formulas work properly.

Figure 12-27: The Formulas category in the Excel Options dialog box controls worksheet calculation and iteration.

Figure 12-28: Select any part of a formula, and press F9 to convert it to its resulting value.

Figure 12-29: This error message appears when you attempt to enter a formula that contains a circular reference.

Figure 12-30: The Circular References menu appears if any circular references are present.

Figure 12-31: The discount formula in cell M29 is circular because it depends on the total, which in turn depends on the discount value in M29.

Figure 12-32: We entered a single array formula in the selected range F4:F8.

Figure 12-33: We used a two-dimensional array formula in B10:E14 to compute the rank of each exam score. A similar one-dimensional array appears in F10:F14.

Figure 12-34: To calculate total wages paid, we used a single-cell array formula in B3 to multiply hours worked by wages due for each employee individually.

Figure 12-35: An array constant is the argument for this array formula.

Figure 12-36: Excel disables external links by default, requiring your intervention.

Figure 12-37: Use the Edit Links dialog box to manage all your external links.

Figure 12-38: The Startup Prompt dialog box lets you customize the startup behavior of external links.

Figure 12-39: The Conditional Sum and Lookup buttons live on the Formulas tab when installed.

Figure 12-40: Use the Add-Ins dialog box to install additional tools.

Figure 12-41: The Conditional Sum Wizard helps you construct SUM formulas that are choosy about what they include.

Figure 12-42: These wizard pages let you select the cells to include in your calculation.

Figure 12-43: We added a list of unique salesperson names (conditions) in column E and copied the conditional sum formula to cells F5:F9.

Figure 12-44: Specify the lookup range and the row and column you want to find using the first two steps of the Lookup Wizard.

Figure 12-45: If you copy the formula to a single cell, the parameters are fixed; copying both the formula and the parameters lets you create a lookup table.

Figure 12-46: You can enter different months and product numbers to change the corresponding value in cell R5.

Chapter 13: Using Functions

Figure 13-1: The online Help system includes a comprehensive function reference.

Figure 13-2: The Insert Function dialog box provides assistance with using functions.

Figure 13-3: The Function Arguments dialog box provides assistance with entering function arguments.

Figure 13-4: Click an argument name in the Function ScreenTip, which appears when you click an existing function, to highlight the corresponding argument in the cell.

Figure 13-5: Ask a question in the Search text box, and Excel suggests some possible functions you can try.

Figure 13-6: If a function has more than one form, the Select Arguments dialog box appears.

Figure 13-7: The Function Library group on the Formulas tab provides direct access to the built-in functions in Excel.

Chapter 14: Everyday Functions

Figure 14-1: You can create complex conditional tests using the OR function.

Figure 14-2: You can use the VLOOKUP function to retrieve information from a vertical table like this one.

Figure 14-3: You can use the HLOOKUP function to retrieve information from a horizontal table like this one.

Figure 14-4: The vector form of the LOOKUP function can retrieve information from a nonparallel cell range.

Figure 14-5: Use the INDEX function to retrieve the address or value in a cell where information is located.

Chapter 15: Formatting and Calculating Date and Time

Figure 15-1: After you drag the fill handle to extend a date series, use the smart tag action menu to adjust the series.

Figure 15-2: Use the Series dialog box to create date series.

Figure 15-3: Starting with the values in the Selected Values area, we created the values to the right by dragging the fill handle.

Figure 15-4: Use the Number tab in the Format Cells dialog box to apply Date and Time formats to cells.

Chapter 16: Functions for Financial Analysis

Figure 16-1: The Euro Currency Tools add-in can help when you need to work with euros.

Figure 16-2: Use the Solutions group on the Formulas tab to work with euros.

Figure 16-3: Click the Euro Conversion button to convert all the values in a cell range from one euro currency to another.

Chapter 17: Functions for Analyzing Statistics

Figure 17-1: PERCENTRANK links percentile figures to input values.

Figure 17-2: The VAR and STDEV functions measure the dispersion of sample exam scores.

Figure 17-3: The LINEST function computes the slope and y-intercept of a regression line.

Figure 17-4: The TREND function creates a data series that can be plotted as a line on a chart.

Figure 17-5: TREND can predict the sales figures for months 19, 20, and 21.

Figure 17-6: Use the Add-Ins dialog box to install the Analysis Toolpak.

Figure 17-7: Click the Data Analysis button on the Data tab to display the Data Analysis dialog box.

Figure 17-8: We generated this table of statistics describing the column of 1,000 values contained in Column A using the Descriptive Statistics tool.

Figure 17-9: Column F contains the distribution bins.

Figure 17-10: This dialog box appears after you select the Histogram tool in the Data Analysis dialog box.

Figure 17-11: This analysis tells us that three scores were at least 900 but less than 950; 48 are at least 950 but less than 1,000; and so on.

Figure 17-12: Use the FREQUENCY function to link the distribution analysis to the input data.

Figure 17-13: Use the Rank And Percentile tool to generate an output table like the one shown in the lower figure.

Figure 17-14: The Parameters area in the Random Number Generation dialog box changes to reflect the distribution type you select.

Figure 17-15: The Patterned option in the Distribution list creates an arithmetic series with operational repetitions.

Figure 17-16: The Sampling tool extracts a random or periodic subset of a numeric population.

Figure 17-17: We'll use this 18-month demand curve to demonstrate the Moving Average tool.

Figure 17-18: The Moving Average tool helps smooth out bumpy curves to reveal the trend.

Figure 17-19: The Moving Average tool provides a better perspective of the overall trend.

Chapter 18: Performing What-If Analysis

Figure 18-1: Begin building the data table by typing the interest rates and the PMT function in the worksheet.

Figure 18-2: Use the Data Table dialog box to specify the input cell.

Figure 18-3: The monthly loan payments for each interest rate now appear in the data table.

Figure 18-4: This data table computes the monthly payments on two different loan amounts at various interest rates.

Figure 18-5: Cell B2 contains the formula for this two-variable table.

Figure 18-6: This data table calculates the monthly payments using various interest rates and terms.

Figure 18-7: Copying the results range to another part of the worksheet transfers the numeric values, not the formulas used to compute them.

Figure 18-8: We'll use the Scenario Manager to model the effects of changing values in D2- D3, D5, and E8- E13 of this worksheet.

Figure 18-9: When you click the What-If Analysis button on the Data tab and then click Scenario Manager, Excel displays the Scenario Manager dialog box.

Figure 18-10: Here we entered the references of the changing cells individually by clicking cells and ranges, separating each reference from the next with a comma.

Figure 18-11: Because we previously named each changing cell, the names appear in the Scenario Values dialog box.

Figure 18-12: Clicking Show replaces your current worksheet values with the values of a specified scenario.

Figure 18-13: Clicking Merge in the Scenario Manager dialog box displays the Merge Scenarios dialog box, with which you can import scenarios from any worksheet in any open workbook.

Figure 18-14: The merged scenarios are now available on the same worksheet.

Figure 18-15: Use the Scenario Summary dialog box to specify the type of report and the result cells you want to see.

Figure 18-16: The Scenario Summary option creates a report on a new worksheet named Scenario Summary.

Figure 18-17: The comments entered in the Scenario Manager dialog box are hidden in row 4 of the Scenario Summary report.

Figure 18-18: The Scenario PivotTable report manipulates the actual data in the report.

Figure 18-19: Use the Goal Seek command to find the maximum mortgage you can borrow if you want to keep your payments under a certain limit.

Figure 18-20: To use goal seeking, complete the Goal Seek dialog box.

Figure 18-21: The Goal Seek Status dialog box informs you when Excel finds a solution.

Figure 18-22: The Goal Seek command returns this result when asked to find the square root of 4.

Figure 18-23: You can use the Solver to determine how many advertisements to place in each publication to meet your objectives at the lowest possible cost.

Figure 18-24: Use the Solver Parameters dialog box to set up your problem.

Figure 18-25: Click Add in the Solver Parameters dialog box to add constraints.

Figure 18-26: The Solver lists the constraints in alphabetical order and uses defined cell and range names whenever possible.

Figure 18-27: When the Solver succeeds, it presents the Solver Results dialog box.

Figure 18-28: To specify an integer constraint, select the item labeled int.

Figure 18-29: Load Model and Save Model in the Solver Options dialog box provide a way to store and retrieve your Solver parameters.

Chapter 19: Basic Charting Techniques

Figure 19-1: If your chart layout includes a title, Excel will create one for you-but you'll probably have to edit the text.

Figure 19-2: Because we chose Best Fit for this chart's data labels, Excel doesn't try to fit the label 2383 into the narrow slice, displaying it outside the pie instead.

Figure 19-3: Your defined templates appear in their own gallery, accessible by clicking Templates in the Create Chart or Change Chart Type dialog box.

Chapter 20: Charting Beyond the Ribbon

Figure 20-1: The Chart Objects list enumerates the elements of the current chart that you can format.

Figure 20-2: When you select Line Color or Line Style, you'll see essentially the same set of options for all the line elements in Excel charts. A similar set of options is available for borders.

Figure 20-3: Click Fill in the Format Chart Area dialog box to apply solid colors, gradients, pictures, and textures to background areas of your charts.

Figure 20-4: Less is sometimes more. Applying No Fill to this chart's chart area eliminates distraction.

Figure 20-5: The Excel 2007 Gradient Fill options let you select colors, angles, directions, transparency, and more.

Figure 20-6: We've applied a fish-fossil texture to the plot area of this chart and a clip art image to the column markers.

Figure 20-7: By selecting the Fixed option and typing a value in the text box at the right, you can set up your own value-axis scaling.

Figure 20-8: The Format Axis dialog box for a text category axis lets you adjust the spacing between gridlines and the position of axis labels, among other things.

Figure 20-9: When a chart compares two sets of values, it can sometimes be effective to put the value axis between the sets.

Figure 20-10: You can change the appearance of a time-scaled chart dramatically using the parameters in this dialog box.

Figure 20-11: With the base unit set to its automatically determined value, Days, Excel generates a simple line chart of daily prices.

Figure 20-12: Changing the base unit to Months creates a monthly open-high-low-close chart.

Figure 20-13: Switching the base unit to Years creates yearly bars.

Figure 20-14: This three-dimensional perspective chart has a depth axis as well as the usual value and category axes.

Figure 20-15: Moving either series to a secondary axis makes it easier to see whether and how the two sets of numbers might relate to one another.

Figure 20-16: When you select the chart area, Excel outlines the source data on your worksheet. You can drag a fill handle to alter the source range.

Figure 20-17: You can expand or contract an existing chart by going to the Select Data Source dialog box and modifying the reference in Chart Data Range.

Figure 20-18: Sometimes your source data will arrive in a form that's awkward for plotting.

Figure 20-19: By default Excel omits missing data in a source range and ignores data in hidden rows and columns.

Figure 20-20: This worksheet uses multilevel categories; city sales offices are grouped by state.

Figure 20-21: A multilevel category chart uses two or more sets of category names to label the category axis.

Figure 20-22: PivotCharts like this are an alternative (and in most cases superior) way to plot multilevel data.

Figure 20-23: Using the Format Trendline dialog box, you can add six different types of trendlines to a data series.

Figure 20-24: You can customize error bars for direction, style, and amount.

Chapter 21: Managing Information in Tables

Figure 21-1: Structured referencing, exemplified by the formula in E2, makes calculations easier to understand and less prone to error.

Figure 21-2: Use the AutoCorrect dialog box to control table expansion.

Figure 21-3: The total row uses the SUBTOTAL function to perform summary calculations.

Figure 21-4: One easy way to sort on a single column is to right-click a cell in the column and choose Sort.

Figure 21-5: The Sort dialog box remembers the last sort settings you used..

Figure 21-6: To sort on three columns, you supply the names of the column headings in the Sort By and Then By boxes.

Figure 21-7: The rows are now arranged in descending order by salary, with rows of common salary arranged first by sex and then by last name.

Figure 21-8: Use the Sort Left To Right option to reorder the years into a descending sequence.

Figure 21-9: The lateral sort specified in Figure 21-8 generates this rearrangement of the data.

Figure 21-10: Sorting this worksheet laterally has broken the formulas in row 5.

Figure 21-11: Use the filter lists to display only those rows in which the age is 33.

Figure 21-12: Use the Top 10 Filter dialog box to zero in on the top or bottom n list elements.

Figure 21-13: Use the Custom Filter dialog box to apply more complex filter criteria to a single column.

Figure 21-14: Use the Advanced Filter command to locate homes within this list that meet specific criteria.

Figure 21-15: This criteria range filters the list to show homes that are either on lots of at least 2 acres or within elementary-school district U.

Figure 21-16: In the Advanced Filter dialog box, select Filter The List, In-Place, and specify the addresses of your list and your criteria ranges.

Figure 21-17: Excel responds with a list filtered to show just the homes in which you're interested.

Figure 21-18: Using the criteria range in cells A1-A4 reduces the list to those houses in elementary-school districts U, F, and T.

Figure 21-19: To display 2,000-square-foot houses in middle-school districts T and J, repeat the >=2000 criterion in each line of the criteria range.

Figure 21-20: The criterion in A2 returns all houses with prices per square foot less than $50.

Figure 21-21: This criterion in A2 uses an absolute reference, because the referenced cell, H1, lies outside the list.

Figure 21-22: The formula in cell G2 uses an ordinary cell reference to D2.

Figure 21-23: By turning the list into a table and editing the formula, we can turn the formula in G2 into something easier to read.

Figure 21-24: This formula uses structured referencing to compare the current row to the total row.

Figure 21-25: The [#This Row] tag tells Excel to total values from the current row only.

Figure 21-26: This example shows a Dark style, with all six of the Table Style Options selected.

Figure 21-27: You can create your own styles by duplicating and modifying existing ones.

Chapter 22: Analyzing Data with PivotTable Reports

Figure 22-1: It's difficult to see the bottom line in a flat list like this; turning the list into a PivotTable will help.

Figure 22-2: These two PivotTables provide summary views of the information in Figure 22-1.

Figure 22-3: In this PivotTable, we've rearranged the data along two axes-rows and columns.

Figure 22-4: This PivotTable presents a "filtered" view, confining the report to a single category.

Figure 22-5: As you select the check boxes for fields in the PivotTable Field List window, Excel populates the table layout at the left side of the worksheet.

Figure 22-6: Excel builds the table, piece by piece, as you select fields.

Figure 22-7: By default, Excel puts all numeric fields, including years and quarters, in the Values box. You can fix that by dragging field headings to the appropriate locations.

Figure 22-8: Excel offers three PivotTable layout options: Compact (left), Outline (center), and Tabular (right).

Figure 22-9: In the Field Settings dialog box, you can override the default subtotaling behavior for a particular field.

Figure 22-10: You can generate subtotals using more than one summary function; this table uses four for the Category field.

Figure 22-11: Subtotals for Channel, an inner field, appear in rows 30–32 of this table.

Figure 22-12: We filtered the table to show only the three best-selling categories.

Figure 22-13: Using this dialog box, you can change the function applied to a field in the Values area of your PivotTable.

Figure 22-14: When you choose a calculation such as Difference From, the Base Field and Base Item boxes display relevant options.

Figure 22-15: From this table, a PivotTable will apply the COUNT function to the Date field to count performances.

Figure 22-16: The PivotTable uses the Date field from Figure 22-15 twice-once to count performances, a second time to calculate percentage of total.

Figure 22-17: Create a calculated field in this dialog box.

Figure 22-18: This calculated field multiples an existing field by a constant.

Figure 22-19: Use this dialog box to create a calculated item for a field.

Figure 22-20: This calculated item will appear by default whenever you include the Channel field in the PivotTable.

Figure 22-21: Excel lists calculated fields and items on a new worksheet.

Figure 22-22: To make the data in this table more meaningful, you can group the date field.

Figure 22-23: Excel gives you lots of ways to group by date.

Figure 22-24: In this table, daily data is grouped by months and then by quarters.

Figure 22-25: When you create a new PivotChart, Excel draws a blank chart canvas as well as a blank table layout. The program creates a PivotTable at the same time it creates the PivotChart.

Figure 22-26: We've used a PivotChart to plot two distribution channels for one book category.

Chapter 23: Working with External Data

Figure 23-1: The Existing Connections dialog box lists connection files that are already established for you.

Figure 23-2: By default, Excel renders imported data as a table. Using the Import Data dialog box, you can create a PivotTable (or PivotTable and PivotChart) instead.

Figure 23-3: You can set your connection to refresh the imported data at regular time intervals.

Figure 23-4: When you click the From Access command to open an Access file, the Select Table dialog box asks you to choose which table you want to import.

Figure 23-5: You can use the Text Import Wizard to tell Excel how to parse your text file.

Figure 23-6: If your file is delimited, the second page of the wizard indicates what character Excel has recognized as the delimiter, and the Data Preview section shows how Excel will parse your file.

Figure 23-7: If your file is fixed-width, be sure to look at the Data Preview box; you can fix any mistakes by manipulating the vertical bars.

Figure 23-8: You can use the third page of the wizard to control the data type of each column.

Figure 23-9: When you open an XML data file, Excel presents this dialog box. Choose the first option to open all elements of the XML structure or the third option to work only with particular elements.

Figure 23-10: Each element of the opened XML file maps to a column in the resulting XML table.

Figure 23-11: When you open an XML file using the XML Structure task pane to map elements to table columns, no data appears until you drag XML elements from the task pane to the worksheet and then refresh.

Figure 23-12: You can drag headings from the XML Structure task pane to the worksheet to create a table showing only particular columns.

Figure 23-13: The first step in using Microsoft Query is to choose a data source.

Figure 23-14: The Query Wizard, a friendly front end to Microsoft Query begins by asking you to choose the columns of data that you want to include in your query.

Figure 23-15: Filters, specified on the second page of the wizard, select the records that meet your criteria.

Figure 23-16: Use the Sort Order page to arrange the records that are returned to Excel.

Figure 23-17: Save the query if you want, and indicate whether you want to return directly to Excel or go on to the full Microsoft Query for further processing.

Figure 23-18: We're using Query to edit a query against three tables in Northwind.mdb.

Figure 23-19: The Joins dialog box tells you exactly how your tables are joined and lets you modify the joins or create new ones.

Figure 23-20: The Sort dialog box displays the current sort order and lets you add fields from a list.

Figure 23-21: We've sorted first by the supplier's company name and then by product name.

Figure 23-22: When we clicked the Criteria Equals button, Query displayed the criteria pane and applied the filter to the result set.

Figure 23-23: When you use the Criteria Equals button in two separate fields, the filter in Query admits only those records that meet both criteria.

Figure 23-24: The Add Criteria dialog box lets you select fields, comparison operators, and values.

Figure 23-25: This criterion returns records where units on stock are below the reorder level.

Figure 23-26: We used the Cycle Through Totals button to calculate the total units on order.

Figure 23-27: Instead of clicking Cycle Through Totals, you can click Records, Edit Column.

Figure 23-28: You can apply aggregate calculations to groups of records; here we calculated the total units on order per company.

Figure 23-29: We've filtered the supplier list to show only those companies with 20 or more products on order.

Figure 23-30: When executed, this query will prompt the user for a product name.

Figure 23-31: Web queries are marked by intersecting rectangles and a globe, and Excel 2007 includes three of them to try.

Figure 23-32: Data returned by this Web query includes hyperlinks to the MSN MoneyCentral Investor site.

Figure 23-33: The New Web Query form is a specialized Web browser.

Figure 23-34: We've displayed the site we want to query in the New Web Query form.

Figure 23-35: In addition to offering other features, the Web Query Options dialog box lets you control how much of the Web site's formatting Excel should preserve.

Figure 23-36: Because we queried this site using full HTML formatting, the downloaded data includes active hyperlinks and other welcome formatting characteristics.

Chapter 24: Collaborating on a Network or by E-Mail

Figure 24-1: The File In Use dialog box appears when you try to open a busy file.

Figure 24-2: The File Now Available dialog box alerts you when the file is no longer in use.

Figure 24-3: Select the Allow Changes By More Than One User At The Same Time check box to share the workbook.

Figure 24-4: Excel outlines cells changed by others in a shared workbook and attaches a comment.

Figure 24-5: If more than one person changes the same cells, the last person to save changes might get to decide which ones to keep.

Figure 24-6: Use the Advanced tab to determine the way Excel handles changes.

Figure 24-7: Use the Highlight Changes command to show what has been done in a shared workbook.

Figure 24-8: You can choose to create a history worksheet detailing the changes made to a shared workbook.

Figure 24-9: You can ensure that change tracking is protected in a shared workbook.

Figure 24-10: Use the Select Changes To Accept Or Reject dialog box to specify which changes you want to review.

Figure 24-11: Each change is highlighted and described, and you can accept or reject it.

Figure 24-12: When the master workbook is already open, select the other workbooks to merge.

Figure 24-13: When you attach a workbook to an Outlook 2007 e-mail message, the Attachment Options task pane appears. The Shared Attachments option in this task pane helps you create a document workspace on your SharePoint site.

Figure 24-14: Use the Move Or Copy dialog box to create a workbook that contains only the worksheets you want to e-mail to the recipient.

Figure 24-15: Use the Permission dialog box to specify who can read and who can change your file.

Figure 24-16: Clicking More Options in the Permission dialog box takes you to this dialog box, where you can fine-tune your permissions settings.

Chapter 25: Collaborating Using the Internet

Figure 25-1: You can download a SharePoint document by clicking it in Internet Explorer.

Figure 25-2: You can also access SharePoint documents by opening them directly in Excel.

Figure 25-3: When you check a document back in, SharePoint presents this form, letting you append comments to the document.

Figure 25-4: With document checkout required, SharePoint automatically checks out the document when you open it for editing in Excel 2007.

Figure 25-5: Document status information, including the status of other team members, appears in the Document Management task pane.

Figure 25-6: You can use the Documents panel to open other documents from the same SharePoint library.

Figure 25-7: Type the URL for your SharePoint site to publish your Excel table as a SharePoint list.

Figure 25-8: If the data type for a column is incorrect, note the Key Cell address, and correct the contents of the cell.

Figure 25-9: User changes to this SharePoint list will be reflected in the Excel table.

Figure 25-10: Use the Show tab in the Excel Services Options dialog box to show or hide worksheets or items in the workbook.

Figure 25-11: Use the Parameters tab in the Excel Services Options dialog box to specify named cells that are user editable.

Chapter 26: Recording Macros

Figure 26-1: In the Record Macro dialog box, you must provide a name and indicate where the macro should be stored. The Shortcut Key and Description fields are optional.

Figure 26-2: The change from Record Macro to Stop Recording is your only indication that your actions are now being preserved for posterity.

Figure 26-3: The Macro dialog box lets you run macros without keyboard shortcuts.

Figure 26-4: Excel stores the VBA code for each action you recorded in the module. You can inspect and edit it in the Visual Basic Editor.

Figure 26-5: You can display detailed Help information about any VBA keyword by clicking in the word and pressing F1.

Figure 26-6: The Object Browser displays the classes of objects belonging to the Excel application.

Figure 26-7: Here the Object Browser shows the Range object and some of the Range object's methods and properties.

Figure 26-8: The MacroTemp macro contains the formatting code you recorded.

Chapter 27: Creating Custom Functions

Figure 27-1: In column F, we want to calculate the discount for each item ordered.

Figure 27-2: Clicking Insert, Module adds a new module to the workbook.

Figure 27-3: This worksheet shows the result of the DISCOUNT custom function.

Figure 27-4: The DISCOUNT custom function now includes comments.

Figure 27-5: If you save your custom functions as an add-in, the code for those functions is available in a module in the Visual Basic Editor, and you can add more functions as the need arises.

Chapter 28: Debugging Macros and Custom Functions

Figure 28-1: Clear the Auto Syntax Check check box if you don't want to know about syntax errors until you run your code.

Figure 28-2: The Visual Basic Editor uses highlighting to mark breakpoint lines.

Figure 28-3: This Debug. Assert statement puts the compiler in break mode when the value of x equals 9 or greater.

Figure 28-4: You can use the Add Watch dialog box to add a watch variable or to set a conditional breakpoint.

Chapter 29: Using Hyperlinks

Figure 29-1: You can use the Insert Hyperlink dialog box to link to a file or Web page.

Figure 29-2: Clicking the Browsed Pages button reveals the names of sites you've recently visited and files you've recently opened.

Figure 29-3: The Browsed Pages list (see Figure 29-2) includes both Web pages and local documents. To filter out the Web pages, click Recent Files.

Figure 29-4: You can create a link to any worksheet, named range, or cell on the current worksheet.

Figure 29-5: You can link to a file that doesn't yet exist-and create it either immediately or the first time you click the hyperlink.

Figure 29-6: You can use this form of the Insert Hyperlink dialog box to create a mailto- link.

Chapter 30: Linking and Embedding

Figure 30-1: Embedding text creates a movable object whose source is identified by an OBJECT formula.

Figure 30-2: Excel can paste a PowerPoint slide in six of the many formats that PowerPoint posts to the Clipboard.

Figure 30-3: When you link data, Excel creates an external-reference formula similar to the formula it would use to reference a cell on another worksheet.

Figure 30-4: By clicking the Object command on the Insert tab, you can create embeddable objects or link or embed entire files.

Figure 30-5: You can take advantage of Microsoft Equation Editor and other OLE server add-ins by clicking the Object command on the Insert tab.

Figure 30-6: You can embed any kind of file in an Excel worksheet. If the file is not associated with an OLE application, Windows encapsulates it using Packager.

Figure 30-7: To update, alter, or sever a link, click the Data tab, and then click Edit Links (in the Connections group).

Chapter 31: Using Excel Data in Word Documents

Figure 31-1: Immediately after you paste Excel cells into Word, a smart tag menu gives you access to the formatting options you're most likely to need.

Figure 31-2: Word can paste an Excel range in any of these formats.

Figure 31-3: When you double-click an embedded Excel object, the Word Ribbon is replaced by the Excel Ribbon, letting you use Excel formatting and editing features to modify the object.

Figure 31-4: In the Links dialog box in Word, you can switch between manual and automatic updating, modify the link specification, or break the connection.

Figure 31-5: Hyperlinks can connect a Word document to an Excel document, but they are not updated when the source changes.

Figure 31-6: When you select Microsoft Office Excel Worksheet on the Create New tab in the Object dialog box in Word, a window into Excel appears in your Word document.

Figure 31-7: In the Select Table dialog box, specify the worksheet that contains the records you want to merge.

Figure 31-8: In the Mail Merge Recipients dialog box, you can filter and sort the data source.

Figure 31-9: We've used the Filter And Sort dialog box to limit our list to ZIP codes that start with 9.