Microsoft Office 2003

EX 162: Introduction

LECTURE NOTES

Explain that several methods can be used to view large-sized worksheets
Emphasize the importance of using cell references and why
Explain that cell references in a formula are called assumptions
Introduce chapter highlights

CLASSROOM ACTIVITIES

1. Group Activity: Point out that normally worksheets are larger than those already created. Ask students what problems might be presented by worksheets that extend beyond the window border.

2. Assign a Project: Ask students to refer to Figure 3-1a and identify values that would be affected if any of the assumptions in the worksheet were changed.

EX 162: Project — Financial Projection Worksheet with What-If Analysis and Chart

LECTURE NOTES

Review the worksheet to be created in the chapter using Figures 3-1a and 3-1b

· Review the requirements document and the sketch and emphasize their importance using Figure 3-2

· Point out that the figures in this book require a resolution of 1024 x 768, and refer students to Appendix E for more information about how to change the resolution on their computers

Use Table 3-1 to review projections data and what-if assumptions
Review the steps to start Excel
Refer students to Appendix F to use Windows XP steps

FIGURES and TABLES: Figures — 3-1a, 3-1b, 3-2; Table — 3-1

BOXES:

1. BTW: Correctness. Review the ways to ensure correctness in formulas.

2. BTW: Excel’s Usefulness. Discuss Excel’s ability to get complex business-related questions answered instantaneously and economically.

3. BTW: Rotating Text in a Cell. Review how to align data in a cell and then describe how to display the data vertically by rotating it.

TEACHER TIPS

Bolding the entire worksheet makes it easier to read. Frequently, the worksheet creator is not the only user of the worksheet. It is important to keep all potential users in mind when designing a worksheet.

CLASSROOM ACTIVITIES

1. Critical Thinking: Excel allows you to change values in a worksheet quickly and easily. How is this helpful in running a business? How can changing values affect business decisions?

2. Group Activity: Although electronic spreadsheets were introduced less than 50 years ago, people have created spreadsheets by hand for hundreds of years. Ask students to brainstorm about the advantages of creating an automated spreadsheet, rather than a handwritten one. How does the capability to recalculate automatically when values change make an electronic spreadsheet more valuable than a spreadsheet created by hand?

EX 165: Plan Ahead Box (Critical Thinking): General Project Decisions

LECTURE NOTES

Review the guidelines and decisions to make before creating the worksheet
Use Figures 3-3a and 3-3b to explain that the what-if analysis helps to answer questions quickly
Use Table 3-1 to review the projections data and what-if assumptions

· Explain that the what-if assumptions should not clutter the worksheet, but should be placed in an easily located portion of the worksheet

· Review the steps to enter the worksheet titles, change workbook properties, apply a theme, and save the workbook

FIGURES and TABLES: Figures — 3-3a, 3-3b

CLASSROOM ACTIVITIES

1. Group Activity: Divide the class into small groups. Have students review the Plan Ahead Box while referring to the requirements document in Figure 3-2. Then, have them close their books and create a sketch for the worksheets.

2. Quick Quiz:

1) What are the two pieces of information necessary or useful before creating a workbook? (Answer: Requirements document (or request for work), sketch)

EX 168: Rotating Text and Using the Fill Handle to Create a Series

LECTURE NOTES

· Use Figures 3-4 through 3-8 to review the steps for rotating text counterclockwise from 1 to 45 degrees in a cell

Use Table 3-2 to summarize the options on the Auto Fill Options menu
Use Table 3-3 to illustrate examples of series using the fill handle
Discuss how to use the fill handle to copy a cell to adjacent cells
Use Figures 3-9 through 3-11 to illustrate increasing column widths and entering row titles

FIGURES and TABLES: Figures — 3-4, 3-5, 3-6, 3-7, 3-8, 3-9, 3-10, 3-11; Tables — 3-2, 3-3

BOXES:

1. BTW: The Mighty Fill Handle. Describe the nuances of using the Fill Handle.

2. Other Ways: Encourage other ways to fill in the data.

3. Other Ways: Encourage alternate ways of indenting.

TEACHER TIPS

When creating a series, it is possible to drag too far and produce a longer series than desired (for example, students may drag through cell H3 in Figure 3-8, extending the series beyond December). If this happens, tell students they should select the series and then drag back inside the range (i.e., to the left) to erase the undesired cell contents.

Note that the fill handle also can be used to produce a series of numbers, dates, month names, and other series.

Note that column widths can be changed before or after values are entered into a worksheet.

CLASSROOM ACTIVITIES

1. Group Activity: Ask students why two cells must be copied to initiate some of the series in Table 3-3, such as a series of numbers or non-sequential months (to indicate the desired progression).

2. Quick Quiz:

1) Which Auto Fill option fills the destination area using the format of the source area (no content is copied unless fill is series)? a) Fill Series, b) Fill Formatting Only, c) Fill Without Formatting, d) Fill Months. (Answer: B)

EX 168: Plan Ahead Box (Critical Thinking): Plan the Layout of the Worksheet

LECTURE NOTES

Review the considerations for determining setup of the columns and rows
Use Figures 3-3a to show placement of the What-If Assumptions section in the worksheet

CLASSROOM ACTIVITIES

1. Critical Thinking: Ask the class to think about what consequences would result if the months were placed as rows instead of columns? Would the calculations have to be set up differently? Would the worksheet be more complex to work on?

2. Group Activity: Brainstorm with the class about placement of the What-If Assumptions section. For example, if the section were place elsewhere in the worksheet, what would the ramifications, if any, be? For example, would the calculation need to be modified and, if so, how?

EX 174: Copying a Range of Cells to a Nonadjacent Destination Area

LECTURE NOTES

· Review the methods for copying and moving cells to other areas by using copy and paste, and drag and drop

Use Figures 3-12 and 3-13 to illustrate copying a range of cells to nonadjacent destination area
Use Table 3-4 to summarize the options available on the Paste Options menu

FIGURES and TABLES: Figures — 3-12, 3-13; Table — 3-4

BOXES:

1. BTW: Fitting Entries in a Cell. Describe how to shrink entries and to zoom.

2. BTW: Move It or Copy It. Emphasize that moving and copying are not the same and describe why.

3. BTW: Cutting. Explain the difference between cutting and copying data.

4. Other Ways: Encourage students to explore other ways of copying a range of cells.

CLASSROOM ACTIVITIES

1. Quick Quiz:

1) Which do you use to complete a copy and paste operation when you want the copied information to remain on the Office Clipboard, the enter key or the Paste button? (Answer: The Paste button; the enter key removes the copied information from the Office Clipboard)

2. Group Activity: Discuss the differences between the Cut and Copy commands.

EX 177: Inserting and Deleting Cells in a Worksheet

LECTURE NOTES

Use Figures 3-14 through 3-16 to illustrate inserting a row

· Point out that single cells, a range of cells, rows, columns, or entire worksheets can be inserted or deleted at any time

Explain the meaning of the #REF! error message

FIGURES and TABLES: Figures — 3-14, 3-15, 3-16

BOXES:

1. BTW: Inserting Multiple Rows. Explain the ways of inserting the multiple rows.

2. BTW: Dragging Ranges. Describe how to drag ranges to the gridline.

3. BTW: The Insert Options Button. Describe the restriction on the Insert Options button

4. BTW: Ranges and Undo. Emphasize the consequences of positioning, adding to, copying, and deleting ranges and how to use the Undo command to undo those actions.

5. Other Ways: Encourage other ways of inserting cells.

TEACHER TIPS

When deleting an entire row or column, especially in a large worksheet, caution students that it is important to avoid accidentally deleting data in the row or column that is off the screen.

CLASSROOM ACTIVITIES

1. Quick Quiz:

1) When you select a row and ask Excel to insert a row, where is the new row inserted? (Answer: Above the selected row)

2) What happens to cell references when you insert new rows or columns? (Answer: Excel adjusts them to refer to the new locations)

2. Assign a Project: Have students open the worksheet they created in Chapter 2 and insert a single cell using the Cells command on the Insert menu. Ask students to observe and discuss the effect of inserting the cell. Then, have students click the Undo button on the Standard toolbar to undo the action. Recall that the Undo button can undo the last 16 actions performed.

EX 180: Entering Numbers with Format Symbols

LECTURE NOTES

Use Table 3-5 to illustrate several examples of numbers entered with format symbols

· Define format symbol and that the valid format symbols include the dollar sign ($), comma (,), and percent sign (%)

· Use Figure 3-17 to illustrate entering numbers with format symbols

· Explain how numbers are displayed when entered with a format symbol and one or more decimal places

FIGURES and TABLES: Figure — 3-17; Table — 3-5

BOXES:

1. Other Ways: Encourage experimenting with other ways to format cells.

CLASSROOM ACTIVITIES

1. Quick Quiz:

1) What are the three format symbols? (Answer: dollar sign ($); comma (,); percent sign (%)).

2. Project to Assign: Discuss in a one-page paper, the effects of using the format symbols.

EX 181: Freezing Worksheet Titles

LECTURE NOTES

· Describe the technique for freezing worksheet titles so that Excel displays the title on the screen even when other distant areas of a large worksheet are in view

· Use Figures 3-18 and 3-19 to illustrate freezing column and row titles

Use Figure 3-20 to illustrate entering the projected monthly sales

FIGURES and TABLES: Figures — 3-18, 3-19, 3-20

BOXES:

1. BTW: Freezing Titles. Explain how to freeze column headings, row titles, or both.

2. BTW: Your Age in Days. Show how to calculate someone’s age in days.

3. Other Ways: Suggest another way to freeze a title.

CLASSROOM ACTIVITIES

1. Quick Quiz:

1) After selecting cell A1, what cell do you click before using the Freeze command to freeze column and row titles? (Answer: The cell below the column headings to freeze and to the right of the row headings to freeze)

2) What happens if you do not display all of the rows or columns above and to the left of the cell you click when you freeze panes? (Answer: Any non-displayed rows or columns are hidden)

EX 183: Displaying a System Date

LECTURE NOTES

· Describe what the date stamp is and what the steps are for entering the date stamp by using the NOW function

Explain how Excel formats a date
Use Figures 3-21 through 3-24 to illustrate entering and formatting the system date

FIGURES and TABLES: Figures — 3-21, 3-22, 3-23, 3-24

BOXES:

1. BTW: Updating the System Date and Time. Describe when Excel does not update the date and time.

2. Other Ways: Review other ways to enter date and time.

TEACHER TIPS

Accepted date formats can vary from one country to another. In the United States, the customary format is Month, Date, Year (e.g., March 14, 2005). In many European countries, however, the usual format is Date-Month-Year (14-Mar-2005). A wise spreadsheet developer will choose a format suitable to his or her audience.

CLASSROOM ACTIVITIES

1. Critical Thinking: Excel allows you to display a system date using the date stamp and the NOW function. When might a date stamp be important? Why? What might affect a spreadsheet developer’s choice for the date format used to display the date?

2. Quick Quiz:

1) True or False: Excel automatically formats this number as a date, using the date and time format, mm/dd/yyyy hh:mm. (Answer: True)

LAB ACTIVITIES

1. Have students determine how many days they have been alive by entering the current date (e.g., 12/5/2007) in cell A1, their birth date (e.g., 6/11/87) in cell A2, and the formula =A1-A2 in cell A3.

EX 186: Absolute versus Relative Addressing

LECTURE NOTES

· Explain the terms and differences of an absolute cell reference, a relative cell reference, and a mixed cell reference

· Use Table 3-6 to discuss formulas entered

· Refer to Table 3-7 for examples of cell references

· Use Figures 3-25 through 3-27 to illustrate entering a formula containing absolute cell references

FIGURES and TABLES: Figures — 3-25, 3-26, 3-27; Tables — 3-6, 3-7

BOXES:

1. BTW: Absolute Referencing. Emphasize that the paste operation is the only operation affected by an absolute cell reference.

CLASSROOM ACTIVITIES

1. Group Activity: Have students consider formulas for cell B13 with various types of cell references (e.g., B4 * B25, B4 * $B$25, B4 * $B25, B4 * B$25). If the formulas were copied to cell C13, what would be the formulas in cell C13? (C4 * C25, C4 * $B$25, C4 * $B25, C4 * C$25) If the formulas were copied to cell B14, what would be the formulas in cell B14? (B5 * B26, B5 * $B$25, B5 * $B26, B5 * B$25). Continue with other examples, challenging students to determine what the results will be when formulas with various types of cell references are copied.

2. Group Activity: Brainstorm with the class about the situations in which an absolute, relative, or mixed reference would be used.

EX 186: Plan Ahead Box (Critical Thinking): Determine Necessary Formulas and Functions Needed

LECTURE NOTES

· Review the formulas to be used in the worksheet and where they should be entered

· Use Table 3-6 to explain the formulas for column B and how they would affect column C

CLASSROOM ACTIVITIES

1. Assign a Project: Although Table 3-6 provides a description of what each formula does, have the students write down on a sheet of paper what the natural language is for the formulas. For example, in natural language, the formula “=SUM(B9:B13)” would be “the sum of column B row 9 through column B row 13).”

2. Group Activity: Divide the class into smaller groups and have each group think of 3 activities that they might do in their daily lives and then have the groups convert those activities into formulas to be shared with the class.

EX 189: Making Decisions — The IF Function

LECTURE NOTES

· Explain that the IF function is useful for assigning a value to a cell based on a logical test

· Define the format of the IF function and provide examples

· Review the logical operators in Table 3-8

· Use Figures 3-28 and 3-29 to illustrate how to enter an IF function

· Discuss the steps for entering additional values, including using the fill handle to copy formulas with absolute cell references using Figures 3-30a and 3-30b

· Use Figures 3-31 and 3-32 to illustrate copying formulas with absolute cell references using the fill handle

· Show how to determine row totals in nonadjacent cells using Figure 3-33

· Review the feature of freezing a worksheet title and how to unfreeze the worksheet and save the workbook using Figure 3-34

· Explain about a nested IF function

FIGURES and TABLES: Figures — 3-28, 3-29, 3-30a, 3-30b, 3-31, 3-32, 3-33, 3-34; Table — 3-8

BOXES:

1. BTW: Logical Operators in IF Functions. Explain about the logical operators to use with IF function and refer to Table 3-8.

2. BTW: Replacing a Formula with a Constant. Explain how to make a cell value constant.

3. BTW: Error Messages. Explain what happens when Excel cannot calculate a formula and review the error messages students might receive.

4. BTW: Toggle Commands. Explain that many commands can be toggled between on and off.

5. BTW: Work Days. Discuss use of the NETWORKDAYS formula.

5. BTW: Using IFERROR. Discuss this function for checking a formula for correctness and the similarity to the IF function.

6. Other Ways: Review the other ways of entering the IF function.

TEACHER TIPS

Instead of entering a function by typing it and pressing the enter key or clicking the Enter box, students can enter a function by clicking the Insert Function box in the formula bar, clicking an appropriate category in the Or select a category list (for the IF function, the category is Logical), and then clicking the desired function in the Function name list. If students have trouble recalling the IF function’s general form, they might want to use the Insert Function box, since a dialog box provides separate text boxes for the logical_test, value_if_true, and value_if_false. It also automatically encloses text-values in quotes.

CLASSROOM ACTIVITIES

1. Group Activity: Ask the class for examples of the IF function that people use everyday. For example, =IF (exam grades > = 70, return to school for another semester, wait tables at Bob’s Burgers). Students also can be asked to write down one instance of the IF function in everyday language. For example, “If (girl1 says ‘yes’ when I ask her out, write date on calendar, ask girl2).” Then, ask students to write down an instance of a nested IF function in everyday language. Encourage students to volunteer their everyday IF functions.

2. Divide the class into small groups. Ask each group to play “Hot Potato” to review the meanings of the comparison operators, using the information in Table 3-8 on page EX 189. To play “Hot Potato,” each group needs a ball or some similar item (a crumpled piece of notebook paper will serve). The student who is “It” holds the ball, calls out the name of any one of the comparison operators (from column 2 of the table), and then tosses the ball to another member of the group, who must draw the symbol for the operator. This person then tosses the ball to still another student, who must explain the example in column 3 of the table. This student then begins the cycle again, by calling out another of the arithmetic operators and tossing the ball. Tell students they may refer to Table 3-8 at any time during the game, if they need to. Continue until all operators have been visited at least once.

LAB ACTIVITIES

1. Ask students to open the worksheet and experiment with toggling between Freeze Panes and Unfreeze Panes to determine how these affect the student’s view of the worksheet. Ask them to keep track of the affects and have them note their observations in 1–2 paragraphs or a list.

EX 195: Formatting the Worksheet

LECTURE NOTES

· Summarize the three ways in which the worksheet can be formatted — the numbers, the titles, and the table

· Use Figures 3-35 through 3-39 to review the steps for assigning formats to nonadjacent ranges

· Use Figures 3-40 through 3-42 to illustrate how to format worksheet titles

· Demonstrate how to assign cell styles to rows and colors to a cell using Figure 3-43

FIGURES and TABLES: Figures — 3-35, 3-36, 3-37, 3-38, 3-39, 3-40, 3-41, 3-42, 3-43

BOXES:

1. BTW: Selecting Nonadjacent Ranges. Emphasize the challenges and restrictions for selecting nonadjacent ranges and then explain the steps.

2. BTW: The Fill and Font Color Button. Discuss the color bar on the Ribbon’s Home tab, for reusing recently selected colors.

3. Other Ways: Review the other ways of formatting.

4. Other Ways: Refer the other ways to format worksheet titles.

CLASSROOM ACTIVITIES

1. Group Activity: Divide students into small groups and give them a list of three numbers: 0.75, 150, and 2,565. Ask each group to predict how the numbers will display when each of the categories in the Category list in the Format Cells dialog box (Figure 3-37) is applied. Then, have students enter the three numbers in a worksheet, select a number, right-click the number, click Format Cells on the shortcut menu, and then compare their predictions with the number shown in the Sample area.

2. Quick Quiz:

1) What are two ways that a negative dollar value can be formatted in Excel? (Answer: A minus sign before the dollar sign or enclosing the dollar value in parentheses)

EX 196: Plan Ahead Box (Critical Thinking): Identify How to Format Various Elements of the Worksheet

LECTURE NOTES

Use Figure 3-35 to describe how to format numbers, titles, rows, and assumptions table.
Explain which formats to use for dollar amounts
Review the font to use for the assumptions table so the table does not detract from worksheet.

CLASSROOM ACTIVITIES

1.Group Activity: Discuss the use of the dollar amount formats and what currencies from other countries might affect layout of the spreadsheet.

2. Quick Quiz:

1) What is the suggested way of diminishing the visual impact of the assumptions table? (Answer: Decreasing the font size to 8 points to set it apart from the other data of 11 point font)

EX 201: Copying a Cell’s Format Using the Format Painter Button

LECTURE NOTES

· Use Figures 3-44 through 3-46 to describe the steps for using the Format Painter button to copy a cell’s format

Use Figure 3-47 to illustrate formatting the What-If Assumptions table and save the workbook

FIGURES and TABLES: Figures — 3-44, 3-45, 3-46, 3-47

BOXES:

1. BTW: Painting a Format to Nonadjacent Ranges. Discuss toggling the Format Painter button.

2. Other Ways: Encourage using other ways for formatting.

CLASSROOM ACTIVITIES

1. Critical Thinking: Ask students to think about the use of italic, bold, and underline as methods for highlighting text. Which is more effective? When would one be more useful? Do you know of any conventions for the use of these three formats? For example, is it wise to use underlining of text if the text is not a hyperlink? Many Web pages use the convention of underlining to indicate hyperlinks. Would using underlining for non-hyperlinks confuse the reader?

2. Quick Quiz:

1) True or False: Using the Format Painter button on the Formulas tab of the Ribbon, you can format a cell quickly by copying a cell’s format to another cell or a range of cells. (Answer: False; the Format Painter button is on the Home tab of the Ribbon.)

EX 204: Adding a 3-D Pie Chart to the Workbook

LECTURE NOTES

· Use Figure 3-48 to define Pie chart and review how the chart should convey information

· Use Figures 3-49 through 3-51 to explain the steps for creating and formatting a 3-D Pie chart on a separate chart sheet

· Discuss the concept of 3-D charts and that they can be rotated

· Use Figures 3-52 through 3-57 to illustrate inserting a chart title and data labels

· Explain how to format the 3-D Pie chart and the individual slices of the pie

· Use Figures 3-58 and 3-59 to illustrate rotating the 3-D Pie chart

· List the chart characteristics that can be controlled using the 3-D Format bevel gallery

· Use Figures 3-60 through 3-64 to illustrate applying a 3-D format to the Pie chart

· Review exploding or offsetting a pie slice using Figures 3-65 through 3-67

· Use Figure 3-68 to illustrate changing the colors of the remaining slices

FIGURES and TABLES: Figures — 3-48, 3-49, 3-50, 3-51, 3-52, 3-53, 3-54, 3-55, 3-56, 3-57, 3-58, 3-59, 3-60, 3-61, 3-62, 3-63, 3-64, 3-65, 3-66, 3-67, 3-68

BOXES:

1. BTW: Charts. Discuss how Excel redraws the chart based on new values.

2. BTW: Certification. For more information on the MCAS program see Appendix G or visit the Excel 2007 Certification Web page.

3. BTW: Chart Items. Describe how to hover the mouse to view a chart tip.

4. BTW: Exploding a 3-D Pie Chart. Explain that dragging one slice explodes all slices.

5. Other Ways: Indicate another way to select a chart.

TEACHER TIPS

If, after drawing a chart, students decide another chart type would be more appropriate, right-click within the chart area, click Chart Type on the shortcut menu, and then choose a different type.

CLASSROOM ACTIVITIES

1. Project to Assign: Ask students to think of a scenario for which a Pie chart would be suitable to show the relationship of parts to a whole. For example, students might think of the number of hours per day spent on different tasks (attending classes and doing schoolwork, eating, sleeping, recreation, and so on). Have students list the parts and then determine approximately what fraction of the whole each part represents (e.g., if a student spends 8 hours of a day sleeping, then sleeping represents about 8/24, or 1/3, of the student’s day. Using this information, have students sketch a Pie chart for the scenario, with each slice an appropriate size (e.g., if a student spends 8 hours sleeping, the slice for sleeping should be about 1/3 of the pie). From this project, students should see that each slice corresponds to the relative size of each part, and that the Pie chart clearly shows the relationship of each part to the whole.

2. Group Activity: Have students refer to Figures 3-48 and 3-51. Brainstorm with students and make a list of as many differences as they can find between the two charts. (Hint: Do not forget about rotation of the chart as one of the differences.)

LAB ACTIVITIES

1. Have students experiment with formatting a chart. Have them experiment with different style charts and then the Pie chart. Ask them to experiment with different 3-D formats and bevel styles, colors, and exploding (offsetting) a pie slice. Ask them to print their favorite version of their experimentation.

EX 204: Plan Ahead Box (Critical Thinking): Specify How the Chart Should Convey Necessary Information

LECTURE NOTES

Describe that the Pie chart is on its own page, rather than being embedded on the worksheet
Use Figure 3-3a and Figure 3-38 to review the way the Pie chart should look.

CLASSROOM ACTIVITIES

1. Quick Quiz:

1) What are the entries called that identify the month names slices of the Pie chart in the range B3:G3? (Answer: category names)

2. Group Activity: Brainstorm with class about the advantages and disadvantages of placing the Pie chart on a chart page vs. embedding the chart.

EX 216: Renaming and Reordering the Sheets and Coloring Their Tabs

LECTURE NOTES

Use Figures 3-69 through 3-71 to illustrate how to rename and reformat the worksheet tabs and change their order in the workbook

FIGURES and TABLES: Figures — 3-69, 3-70, 3-71

BOXES:

1. Other Ways: Describe other ways to rename, move, and copy a worksheet.

TEACHER TIPS

The National Association of Professional Organizers suggests that one of the best ways to keep an office organized is to be consistent with colors used for labeling purposes. While color selection for worksheet tabs can be made simply at random, a meaningful selection of colors used for worksheet tabs can help an office’s organization and productivity. Think about how the colors used to identify worksheet tabs can be used elsewhere in an office to promote consistent labeling. Also mention that other scenarios might require selecting specific, meaningful tab colors. The office might have just black and white printers, however.

CLASSROOM ACTIVITIES

1. Quick Quiz:

1) To rename a worksheet, right-click on the sheet tab, and click what on the shortcut menu? (Answer: Click Rename on the shortcut menu)

2) To move a worksheet, right-click on the sheet tab, and click what on the shortcut menu? (Answer: Click Move or Copy on shortcut menu)

2. Project to Assign: Have students research the Web to discover the history of the Pie chart. How did it come into existence as a viable graphic to emphasize relationships. Ask students to write a one-page paper and cite their references (Web sites and links to those sites).

EX 218: Checking Spelling, Saving, Previewing, and Printing the Workbook

LECTURE NOTES

Explain the steps how to check spelling on multiple worksheets
Discuss the steps to preview and print the workbook using Figures 3-72a and 3-72b
Discuss why worksheets are printed in landscape orientation

FIGURES and TABLES: Figures — 3-72a, 3-72b

BOXES:

1. BTW: Checking Spelling. Discuss the way Excel checks spelling.

2. BTW: Printing in Black and White. Discuss ways to speed up printing and save ink.

3. BTW: Quick Reference. Point out the location for the Quick Reference Summary and the Excel 2007 Quick Reference Web page.

CLASSROOM ACTIVITIES

1. Quick Quiz:

1) Which button do you click to run the spell checker? (Answer: The Spelling button on the Ribbon)

2. Group Activity: Have students call out what they recall from previous chapters about previewing and printing worksheets. Keep a list on the board or overhead projector.

EX 220: Changing the View of the Worksheet

LECTURE NOTES

· Describe the various techniques available for viewing the worksheet

· Use Figures 3-73 through 3-75 to illustrate shrinking and magnifying the view of a worksheet or chart

· Use Figures 3-76 and 3-77 to illustrate splitting a window into panes

· Define the vertical and horizontal split bars for creating viewing panes

Review the steps to remove the panes from the window

FIGURES and TABLES: Figures — 3-73, 3-74, 3-75, 3-76, 3-77

BOXES:

1. BTW: Zooming. Discuss zooming to reduce or enlarge the view of the worksheet.

2. BTW: Window Panes. Discuss additional ways to control the split bar to create panes.

3. Other Ways: Name the other ways to control the size and location of the panes.

TEACHER TIPS

Students must be careful not to confuse the scroll arrows, which are to the right of the sheet tab in Figure 3-77 and are used to scroll in the left and right panes, with the tab scrolling buttons, which are to the left of the sheet tab and are used to move between worksheets.

CLASSROOM ACTIVITIES

1. Group Activity: Have students offer reasons for magnifying or shrinking the display. If it is not mentioned, point out that because of their screen dimensions, worksheets, and charts often are magnified when working with notebook computers.

2. Projects to Assign: Have students open a workbook that contains a chart, and select and view the worksheet (not the chart). Ask them to experiment with splitting the window into four panes, changing the center, and moving in the panes using the scroll bars and observing how the panes change. Next, have then experiment with splitting the window into two panes by dragging a split bar to the edge of the window or double-clicking a split bar. Again, have them move the split location and scroll in the panes. Finally, have students write a paragraph describing how splitting a worksheet into panes can help when working with large worksheets.

EX 223: What-If Analysis

LECTURE NOTES

· Define what-if analysis (sensitivity analysis) and its use for examining the effect on a worksheet when data values are changed

· Use Figures 3-78 to illustrate analyzing data in a worksheet by changing values

· Emphasize that Excel recalculates formulas and redraws associated charts when values are changed

· Define goal-seeking as a reciprocal operation of the what-if analysis

· Use Figures 3-79 through 3-81 to illustrate goal seeking

FIGURES and TABLES: Figures — 3-78, 3-79, 3-80, 3-81

BOXES:

1. BTW: Undoing What You Did. Review how to undo actions by using the Undo button.

2 Other Ways: Mention the other ways to goal seek.

TEACHER TIPS

Emphasize that goal seeking assumes the value of only one referenced cell can be changed, and that cell can be referenced either directly or indirectly in the formula or function.

CLASSROOM ACTIVITIES

1. Group Activity: Divide the class into small groups. Have the groups note 3 different what-if examples. (e.g., “What if we had gone to the supper club, instead of the mud wrestling tournament? Would we still be hungry?”).

2. Quick Quiz:

1) If you know the result you want a formula to produce, what can you use to determine the value of a cell on which the formula depends? (Answer: goal seeking )

LAB ACTIVITIES

1. Ask students to use the workbook to experiment with changing one or more of the assumptions and noting what effect this has on the Pie chart. Have students print out and turn in their most interesting results.

Microsoft Office Excel 2007

Chapter Three: What-If Analysis, Charting, and Working with Large Worksheets

Chapter Objectives

Chapter Objectives	2
EX 162: Introduction	2
EX 162: Project — Financial Projection Worksheet with What-If Analysis and Chart	2
EX 165: Plan Ahead Box (Critical Thinking): General Project Decisions	3
EX 168: Rotating Text and Using the Fill Handle to Create a Series	3
EX 168: Plan Ahead Box (Critical Thinking): Plan the Layout of the Worksheet	4
EX 174: Copying a Range of Cells to a Nonadjacent Destination Area	5
EX 177: Inserting and Deleting Cells in a Worksheet	5
EX 180: Entering Numbers with Format Symbols	6
EX 181: Freezing Worksheet Titles	6
EX 183: Displaying a System Date	7
EX 186: Absolute versus Relative Addressing	7
EX 186: Plan Ahead Box (Critical Thinking): Determine Necessary Formulas and Functions Needed	8
EX 189: Making Decisions — The IF Function	8
EX 195: Formatting the Worksheet	9
EX 196: Plan Ahead Box (Critical Thinking): Identify How to Format Various Elements of the Worksheet	10
EX 201: Copying a Cell’s Format Using the Format Painter Button	10
EX 204: Adding a 3-D Pie Chart to the Workbook	11
EX 204: Plan Ahead Box (Critical Thinking): Specify How the Chart Should Convey Necessary Information	12
EX 216: Renaming and Reordering the Sheets and Coloring Their Tabs	12
EX 218: Checking Spelling, Saving, Previewing, and Printing the Workbook	13
EX 220: Changing the View of the Worksheet	13
EX 223: What-If Analysis	14
End of Chapter Material	15
Glossary of Key Terms	16