"2003 Excel Formulas"
Excel 2003 Page 1 of 21 Microsoft Office 2003 Concepts and Techniques EXCEL 2003 Instructor’s Manual PROJECT TWO FORMULAS, FUNCTIONS, FORMATTING, AND WEB QUERIES OBJECTIVES Students will have mastered the material in this project when they can: Enter formulas using the keyboard and Check the spelling of a worksheet Point mode Preview how a printed copy of the Recognize smart tags and option buttons worksheet will look Apply the AVERAGE, MAX, and MIN Print a partial or complete worksheet functions Display and print the formulas version of Verify a formula using Range Finder a worksheet Format a worksheet using buttons and Use a Web query to get real-time data commands from a Web site Add conditional formatting to a range of Rename sheets in a workbook cells E-mail the active worksheet from within Change the width of a column and Excel height of a row PROJECT OVERVIEW In creating the workbook for this project, students learn how to enter formulas, calculate an average, find the highest and lowest numbers in a range, verify formulas using Range Finder, change fonts, draw borders, align text, format numbers, change column widths and row heights, and add conditional formatting to a range of numbers. Students learn how to spell check a worksheet, preview a worksheet, print a worksheet, print a section of a worksheet, and display and print the formulas version of the worksheet using the Fit to option. They also learn how to complete a Web query to generate a worksheet using external data obtained from the Web and how to rename sheet tabs. Finally, students learn how to send an e-mail directly from within Excel with the opened workbook as an attachment. INSTRUCTOR NOTES Case Perspective, EX 65 LECTURE NOTES The Case Perspective presents a real-life situation in which Microsoft Excel could be used and offers background for the worksheet created in this project (Figure 2-1). Review the Case Page 2 of 21 Project 2: Formulas, Functions, Formatting, and Web Queries Perspective. Note how Excel will be used to create the worksheet and access real-time data from the Web. Introduction, EX 66 LECTURE NOTES Remind students of what they learned in Project 1 and introduce them to the topics covered in this project. Discuss Figures 2-1a and 2-1b. Much of the work in this project is done using formulas. A formula can be defined as an equation with more than one variable that is used to solve practical problems. Students probably have worked with formulas in mathematics, science, and business classes. Perhaps surprisingly, however, formulas also are used in psychology, anthropology, sports, art, music, the language arts, and other areas. Formulas can ascertain abstract possibilities, such as an object’s length as its speed approaches the speed of light (l = (c 2 r 2 ) s , where s is the stationary length, c is the speed of light, and r is the object’s speed) c2 and concrete realities, such as a woman’s shoe size (s = 3l-22, where l is the foot’s length in inches). Worksheets make formulas even more powerful. This project shows how formulas and functions can be entered into one cell and then copied to other cells, instantly displaying results based on different variables. CLASSROOM ACTIVITIES As students work through this project, encourage them to recognize the symbiotic relationship between formulas and worksheets. Project Two – Blue Chip Stock Club Investment Analysis, EX 67 LECTURE NOTES Using Figure 2-2, summarize the need, source of data, summary of calculations, Web requirements, and other facts about the development for this worksheet. Help students to understand all of the calculations that must be made for each stock. Using the sketch in Figure 2- 3, review the desired formatting for the worksheet. Use Figure 2-1a to describe the worksheet created and Figure 2-1b to identify the Web queries. Discuss More About Aesthetics versus Function on page EX 68. CLASSROOM ACTIVITIES Divide the class into small groups. Ask each group to refer to pages EX 67-69 and discuss the Blue Chip Stock Club Investment Analysis project, making certain they understand the needs, source of data, required calculations, and Web requirements of the project. Starting and customizing Excel, EX 69 LECTURE NOTES Recall that Windows must be running to start Excel. If necessary, tell students to change their computer’s resolution to 800 x 600 so their screens will match the figures in the textbook. To change the resolution, click Control Panel on the Start menu, click the Display icon, select the Settings tab, and adjust the slide in the Screen resolution area. Point out that Appendix B provides information on how to change the resolution. Review Steps 1 through 5 on page EX 70 to start and customize Excel. Discuss More About Starting Excel on page EX 70. Excel 2003 Page 3 of 21 CLASSROOM ACTIVITIES Ask students to recall other ways to start Excel (e.g., double-click the Excel icon on the desktop; click Microsoft Office Excel 2003 on the Start menu; click the Start button, point to All Programs, click New Office Document, click the General tab, and then double-click the Blank Workbook icon). Entering the titles and numbers into the worksheet, EX 70 LECTURE NOTES Use Figure 2-1a to describe the worksheet title and subtitle. Review Steps 1 and 2 to enter the worksheet title and subtitle. Using Figure 2-1a, point out that some column titles include multiple lines of text. Explain how to start a new line in a cell by pressing ALT+ENTER. In Microsoft Word, pressing ALT+ENTER is called inserting a manual line break – this starts a new line, without starting a new paragraph. Use Table 2-1 to summarize the stock club’s investments. Review Steps 1 through 10 on page EX 71 to enter the column titles. Discuss More About Wrapping Text on page EX 71. Point out that Excel considers the dates in Table 2-1 to be numbers, and thus will display them right-aligned. The dates will be formatted later in this project. Review Steps 1 through 4 on page EX 72. to enter the stock data shown in Table 2-1. Review Steps 1 and 2 on page EX 72 to enter the row titles. Use Figure 2-4 to illustrate the worksheet to this point, including the worksheet title, column titles, and stock data. Review Steps 1 through 3 on page EX 72 to save the workbook. Discuss More About Two-Digit Years, More About Formatting a Worksheet, and More About Entering Numbers into a Range on page EX 72. DISCUSSION TOPICS Text that is too long for a cell can be handled in several different ways. What is the difference between: (1) Not giving Excel any instructions about what to do with the text overflow (2) Letting Excel wrap text in a cell (3) Using ALT+ENTER to cause Excel to go to a new line while entering text. In what situations might each of these three techniques be used? TROUBLESHOOTING TIPS Remind students that when they are entering data in a cell and press the ENTER key, Excel moves to a new cell. Emphasize that they should use ALT+ENTER, rather than ENTER, to create a new line in a cell. Students always should enter a year with four digits. Any two-digit year under 30 is considered by Excel to be a part of the 2000s, but if a two-digit year is greater than or equal to 30, Excel thinks it is part of the 1900s. For example, if students wish to enter the year 2040 in an Excel worksheet and they just type “40,” Excel will think they mean “1940.” Entering formulas, EX 72 LECTURE NOTES Explain how the initial cost for each stock is determined. Define formula. Consider the advantages of assigning a formula to a cell. Discuss More About Automatic Recalculation on page EX 74. Use Figures 2-5 and 2-6 to describe entering a formula using the keyboard. Excel calculates the result of a formula as soon as the formula is entered and displays the result in the worksheet. Emphasize the importance of the equal sign preceding a formula. Without the equal Page 4 of 21 Project 2: Formulas, Functions, Formatting, and Web Queries sign, Excel considers the “formula” to be text. Discuss the Q&A on page EX 74. Use Table 2-2 to list the arithmetic operators for negation, percentage, exponentiation multiplication, division, addition, and subtraction. Emphasize the operators with which students may be less familiar (e.g., ^, *, and /). Note the various ways formulas can be typed. Using uppercase and spaces can make formulas easier to read. If Excel recognizes an error in a formula, an error message displays. Common error messages, and their meanings, include: ##### The formula produces a result that is too wide for the column #DIV/0! The formula attempts to divide by 0 (a referenced cell acting as a divisor may be empty, making its value 0) #REF! The formula contains an invalid cell reference (a referenced cell may have been deleted) #VALUE The formula has the wrong type of data (a referenced cell in an arithmetic formula may contain text) #NUM The formula uses an invalid number (a referenced cell may contain a value that produces too large, or too small, a result to represent) Another error is to create a formula with a circular reference. This occurs when a cell reference in a formula refers to the formula’s result. For example, the formula = B1 + B2 + B3 in cell B3 is a circular reference because the result of the formula in cell B3 depends on the value in cell B3. Therefore, Excel is unable to determine the formula’s result. When a formula with a circular reference is entered, Excel immediately points out the problem and offers Help. CLASSROOM ACTIVITIES Divide the class into small groups. Ask each group to play “Hot Potato” using the information in Table 2-2 on page EX 74. 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 meaning of any of the arithmetic operators, and then tosses the ball to another member of the group, who must describe the operator. This person then becomes “It,” calling out a term and tossing the ball. Students may refer to Table 2-2 in their textbooks to help them identify the operators. Continue until all terms have been visited at least once. DISCUSSION TOPICS Cell references are an integral part of a formula. The cells referenced in a formula (i.e., the cells on which the result of the formula depends) are called precedent cells (or simply precedents). When the values in the precedent cells change, the formula’s result automatically changes. Therefore, specific numbers should be used in formulas only if they are constants (i.e., if they will not change). What are some examples in which entering specific numbers in formulas would be appropriate? TROUBLESHOOTING TIPS If students are working in an Excel worksheet, they should expect the cells with formulas to change their values when they change one of the values in the formula. If they enter such a value, and the value in the formula cell does not change, it probably means that Excel currently is in Manual Recalculation mode. Students can press F9 to recalculate, or change to Automatic Recalculation using the Calculation sheet from the Tools menu. Excel 2003 Page 5 of 21 QUICK QUIZZES What beginning symbol alerts Excel that what follows is a formula and not just text? (Answer: The equal sign) What does the asterisk (*) mean in a formula? (Answer: Multiply) How does Excel show you what cells you have included in a formula? (Answer: When the formula is selected, the included cells are identified by colored borders) Order of operations, EX 74 LECTURE NOTES The result of many numerical expressions depends on the sequence in which calculations are completed. Even a simple expression, such as 2 + 3 * 4, can have different results depending on what operation is completed first. Point out that Excel, like algebra, uses the order of operations agreed to by mathematicians. Review the order of operations. Some operations, such as multiplication and division or addition and subtraction, have the same rank in terms of precedence. In this case, Excel performs the operations from left to right. In other words, the result of the expression 12 / 3 * 4 is 16 (not 1), and the result of 8 – 2 + 4 is 10 (not 2). Explain how parentheses are used to override the order of operations. An opening parenthesis must have a closing parenthesis. If not, Excel will offer to supply the missing parenthesis or ask you to supply it. Use Table 2-3 to illustrate examples of valid Excel formulas. CLASSROOM ACTIVITIES Explore the effect of parentheses by presenting an expression, such as 3 * 4 ^ 2 - 12 / 2 + 1, evaluating it using the order of operations (43), then adding parentheses in one or more places, such as (3 * 4) ^ 2 - 12 / (2 + 1), and reevaluating the expression (140), noting how the result has changed. Write the following expression on the board: 6 * 2 ^ 4 - 12 / 3 + 1 Have students determine all of the different values that can be derived from this expression, depending on where pairs of parentheses are placed. Entering formulas using Point mode, EX 75 LECTURE NOTES Review how the current value, gain/loss, and percent gain/loss are derived for each stock. Define Point mode. Discuss More About Using Point Mode. Use Figures 2-7 through 2-10 to describe entering formulas using Point mode. Explain that Point mode can be a faster and more accurate (because it eliminates cell reference typing errors) way of entering formulas. Point mode also can more clearly show the relationships between cells in a formula. Note that Point mode and the keyboard can be combined to enter formulas. Mention that Excel rounds decimal values that are too long to display in a cell but maintains the original values for computational purposes, and that this can result in some seeming errors in displayed values. Discuss More About Troubling Formulas on page EX 77. PROJECTS TO ASSIGN Ask students to practice entering formulas using both the keyboard and Point mode. Have them prepare a brief report explaining which method generally is easier, faster, and/or more accurate, as well as a list of situations in which one method or the other would be preferable. Page 6 of 21 Project 2: Formulas, Functions, Formatting, and Web Queries Copying the formulas using the fill handle, EX 77 LECTURE NOTES Point out that although similar formulas could be entered for the remaining stocks, an easier (and possibly more accurate) technique is to copy the formulas using the fill handle. Use Figures 2-11 through 2-13 to illustrate copying formulas using the fill handle. Consider Other Ways to copy formulas. Remind students that cell references are adjusted when formulas are copied. Explain how the cell references are changed when formulas are copied downward and across. To see how cell references change, encourage students to compare the formula in the source area, cell F4 (D4*E4), to the formula in a cell in the paste area, such as F12 (D12*E12). QUICK QUIZZES If you copy across, which references does Excel adjust? (Answer: The column references) Smart tags, EX 78 LECTURE NOTES Define smart tags. List some types of data labeled with smart tags. Tell how to use smart tags. Define smart tag indicator and explain how to display, and use, the Smart Tag Actions button. Point out the Auto Fill Options button that appears on the screen in Figures 2-12. Note that Excel also can display other Options buttons. Use Table 2-4 to summarize the smart tags and Options buttons available in Excel. CLASSROOM ACTIVITIES Divide the class into small groups. Ask each group to review Table 2-4 on page EX 79, making sure that everyone in the group understands each smart tag and Options button and the function of the corresponding menu. Encourage the groups to bring up any questions they have when the class reconvenes. DISCUSSION TOPICS Microsoft’s smart tag technology was introduced with Office XP and users continue to discover how helpful they can be. In addition to taking advantage of the preloaded, preconfigured smart tags that come with Excel, users also can create their own smart tags that connect to custom- defined user information, or acquire smart tags from third-party sources. For example, you could develop a smart tag that recognizes specific names of items in a store’s inventory. When those item names are entered into a worksheet, the smart tag would recognize the name and list appropriate options, such as accepting automatic corrections, changing the format, or even obtaining real-time inventory statistics from the company’s e-commerce Web site. What are other situations in which creating a custom smart tag would be beneficial? How could a custom smart tag make some specific Excel computing tasks easier? Why would a third-party, such as a software company or large e-commerce enterprise, want to create smart tags? To whom would such a company distribute the third-party smart tags? Determining totals using the AutoSum button, EX 79 LECTURE NOTES Using Figure 2-13, point out that the cells in row 13 will contain totals. Remind students how totals can be obtained by entering the SUM function or clicking the AutoSum button. Recall when the AutoSum button must be clicked twice. Review Steps 1 and 2 to determine totals using Excel 2003 Page 7 of 21 the AutoSum button. Discuss Figure 2-14. Explain how the CTRL key could have been used to select the nonadjacent range before clicking the AutoSum button. Discuss More About Selecting a Range on page EX 80. CLASSROOM ACTIVITIES Ask students why totals are not obtained in cells D13, E13, G13, and J13. TROUBLESHOOTING TIPS Students should be careful to differentiate between double-clicking the AutoSum button and clicking it twice. When they point to a cell where they want a sum, they should click the AutoSum button twice, not double-click it. Determining the total percent gain/loss, EX 80 LECTURE NOTES Review Steps 1 and 2 to determine the total percent gain/loss. Discuss Figure 2-15. Explain why the formula was not copied originally to the cell. Define blank cell. As implied in the text, if the formula had been copied earlier, the formula in cell J13 (I13/F13) would have resulted in an error message (#DIV/0!) because F13, the divisor, would have a numerical value of zero (0). Mathematically, division by zero (0) always is meaningless or impossible. TROUBLESHOOTING TIPS If students enter a formula that references a blank cell, the value in that blank cell is considered to be zero. If the blank cell happens to be the divisor in an arithmetic operation, they will get an error message, since division by zero is not allowed. Using the AVERAGE, MAX, and MIN functions, EX 80 LECTURE NOTES Using Figure 2-15, point out that the average, highest, and lowest value must be determined for each number of shares. Define function. Students might be interested in comparing the definition of a function in Excel with the common definition of a function in mathematics (a correspondence between two sets of numbers so that for every number in the first set there is exactly one number in the second set). Discuss More About Formulas and Functions. Define arguments. Arguments can be numbers, text, logical values (such as TRUE or FALSE), constants, arrays (specially arranged groups of constants or areas of cells), error values (such as #N/A), cell references, formulas, or other functions. The argument designated must produce a valid value for that function. Describe the general format of a function. List the six methods that can be used to enter functions. Mention the factors on which the choice of method depends. DISCUSSION TOPICS There are six methods that can be used to enter functions. Many other tasks in Excel can be carried out in a variety of ways. Why does Microsoft give so many options, rather than just offering one way to do each task? Do multiple methods make things more confusing or less confusing? What has your experience been like, carrying out tasks in Excel (and other applications) in more than way? Is it best to experiment with many methods, or stick with the first one you learn? Page 8 of 21 Project 2: Formulas, Functions, Formatting, and Web Queries Determining the average of a range of numbers, EX 81 LECTURE NOTES Define the AVERAGE function. Discuss More About Statistical Functions. Use Figures 2-16 and 2-17 to illustrate determining the average of a range of numbers using the keyboard and mouse. Discuss Other Ways to determine the average. Point out that in a function the argument must be included with parentheses. Note that the right parenthesis is appended automatically when the Enter box is clicked or the ENTER key pressed. Explain why the arrow keys cannot be used to complete an entry in Point mode. CLASSROOM ACTIVITIES Ask students how the average number of shares would be found manually (add the number of shares of each stock and then divide by the number of stocks). Determining the highest number in a range of numbers, EX 82 LECTURE NOTES Describe the MAX function. Use Figures 2-18 through 2-20 to illustrate determining the highest number in a range of numbers using the Insert Function dialog box. Discuss Other Ways to determine the highest number in a range. Using Figure 2-19, point out that the Function Arguments dialog box displays the value the function will return and the first few numbers in the selected range. Note the advantages of using the MAX function instead of simply scanning the range and then entering the highest value. Discuss More About Entering Functions. CLASSROOM ACTIVITIES Divide the class into small groups. Ask each group to display the Insert Function dialog box shown in Figure 2-18. Have them click the drop down arrow and select All in the Or select a category box.. Then, have them scroll through the list of functions , and view descriptions of selected functions. Ask each group to record a list of functions they have used in the past, functions that look interesting, and functions they cannot imagine anyone using. Have the groups share their recordings with the class. DISCUSSION TOPICS Sometimes, it is just as easy simply to scan a list and pick out the highest value as it is to use the MAX function. Consider various numerical lists, such as a list of the ages of family members, a list of class grades, a list of stock prices, a list of the areas of states, a list of city populations, and so on. When would it make more sense to use the MAX function than simply to scan the list to determine the highest value? Why? Does the answer depend on the amount of data, the type of data, or both? Why? Determining the lowest number in a range of numbers, EX 84 LECTURE NOTES Describe the MIN function. Use Figures 2-21 through 2-24 to illustrate determining the lowest number in a range of numbers using the AutoSum menu. Consider Other Ways to determine the lowest number. Note the advantage of using the AutoSum menu to enter a function. Explain how to access a function not available on the AutoSum menu. Note the many functions available in Excel. Using Figure 2-18, tell how to view categories and obtain descriptions of functions. Excel 2003 Page 9 of 21 PROJECTS TO ASSIGN Encourage students to explore the functions that Excel offers. After displaying the dialog box, students should use the drop-down arrow and select a category in the Or select a category box. Make a list of four functions that are displayed. Select each of the four function in turn and use the description of the function and the Help with this function link to learn more about the function. What does the function do? How is it used? When might it be used? Why is it in this category? Then, have students experiment with the Search for a function text box by entering a brief description of what they might want to do (e.g., find interest due) and clicking the Go button. List the functions returned and, by selecting each turn, describe the functions. Based on these results, how effective is the Search for a function box? Why? Have students write a brief report on their findings. QUICK QUIZZES What do you do if the range Excel selects when you use the AutoSum button to choose a function is not the correct range? (Answer: Select the correct range with the mouse) Copying the AVERAGE, MAX, and MIN functions, EX 87 LECTURE NOTES Use Figures 2-25 through 2-27 to describe copying a range of cells across columns to an adjacent range using the fill handle. Discuss Other Ways to copy a range of cells across columns to an adjacent range. Tell why the average in cell J14 was deleted. The negative Percent Gain/Loss values that appear in column J indicate a percent loss. In performing statistical computations such as average, however, a negative percent (i.e., a percent less than 0) makes no sense. For example, in basketball, how can a shooting percentage be less than 0%? Remind students that ranges are adjusted when functions are copied. Saving a workbook using the same file name, EX 88 LECTURE NOTES Review Step 1 to save the workbook. Note that when you save a workbook a second time using the same file name, Excel will not display the Save As dialog box as it does the first time you save the workbook. Mention other ways to save the workbook using the same file name – click Save on the File menu, press SHIFT+F12 or press CTRL+S. Explain how to save the workbook with a new name or on a different drive. Discuss More About File Types on page EX 81. CLASSROOM ACTIVITIES Ask students to suggest circumstances when they might save a workbook with a new name. Why? DISCUSSION TOPICS The importance of saving a workbook is clear – unless it is saved, a malfunction or power failure could cause the workbook to be lost. But, how often should an Excel workbook should be saved? Once a minute? Once every 15 minutes? Once an hour? Once a day? Why? Some computer users feel it is not time that is important, but that a workbook should be saved whenever you would not want to have to redo the work completed since the last time the workbook was saved. Is this a good rule of thumb? Why or why not? Page 10 of 21 Project 2: Formulas, Functions, Formatting, and Web Queries Verifying formulas using Range Finder, EX 89 LECTURE NOTES Mention the common mistake made when entering formulas. Explain how formula references can be verified. Define Range Finder. Explain how Range Finder is used to verify cell references. Use Figure 2-28 to describe verifying a formula using the Range Finder. Explain how the Range Finder can be used to change cells referenced in a formula. When using Range Finder, incorrect cell references also can be changed by double-clicking the incorrect reference in the formula bar, clicking the correct cell in the worksheet, and then pressing the ENTER key. To see the result of a formula when using Range Finder, press function key F9. To enter that result, instead of the formula, in the cell, press the ENTER key; to keep the formula in the cell, press the ESC key. Discuss More About Checking Formulas on page EX 90. PROJECTS TO ASSIGN With the Blue Chip Stock Club worksheet open, have students select a cell that contains a formula and then click Formula Auditing on the Tools menu. Have students use Help to learn more about two of the auditing commands. Then, ask them to use the two commands and note what takes place. Finally, have students write a brief report describing the commands and explaining how the commands can help to find errors in a worksheet. Formatting the worksheet, EX 90 LECTURE NOTES Recall reasons for formatting a worksheet. Explain how the formatting in this project is different from the formatting in the previous project. Using Figure 2-29, compare the unformatted worksheet in Figure 2-29a to the formatted worksheet in Figure 2-29b. Outline the type of formatting required for Project 2. Note the formatting that can be applied using the Formatting toolbar and mouse. Discuss More About Colors and More About Toolbars on page EX 91. CLASSROOM ACTIVITIES Using Figure 2-29b and the outline of formatting changes on page EX 91, ask students to identify formatting tasks they already know how to perform (e.g., bolding text) and those that they must learn how to do (e.g., formatting the date) to produce the formatted worksheet in Figure 2-29b. Changing the font and centering the worksheet title and subtitle, EX 91 LECTURE NOTES Mention that different fonts often are used in presentation-quality worksheets. Because too many font types and styles can be distracting, generally no more than two different fonts are used in a worksheet. Using Figure 2-29b, describe the format of the worksheet title. Use Figures 2-30 through 2-33 to illustrate changing the font and centering the worksheet title. Discuss Other Ways to format the worksheet title. Note that fonts can be changed at any time the worksheet is active. Explain why the Formatting toolbar is used to center the worksheet title instead of the Merge and Center button. Review Steps 1 through 5 on page EX 93 to change the font and center the worksheet subtitle. Discuss Figure 2-34. Consider formatting tasks that could, and could not, be doe to both titles at the same time. Excel 2003 Page 11 of 21 CLASSROOM ACTIVITIES Ask each student to choose which of the following statements best represents his or her opinion: (1) It is better to change fonts before any data is entered. (2) It is better to change fonts as you enter data. (3) It is better to change fonts after all the data is entered. Divide the class into three groups based on their choice. Ask each group to prepare a defense of their choice. Conduct an informal debate among the three groups. Changing the background and font colors and applying a box border to the worksheet title and subtitle, EX 94 LECTURE NOTES Use Figure 2-29b on page EX 90 to identify the final formats assigned to the worksheet title. Discuss the Q&A on page EX 93. Use Figures 2-35 through 2-38 to illustrate changing the background and font colors and applying a box border to the worksheet title and subtitle. Discuss Other Ways to change the background, font colors, and border. Explain how borders and background colors can be removed, and font colors returned to black. Discuss More About Adding Colors and Borders on page EX 94. To return a cell’s font, font size, and font color quickly back to the default setting (10 point Arial black), select the cell, click Cells on the Format menu, click the Font tab in the Format Cells dialog box, click the Normal font check box, and then click OK. DISCUSSION TOPICS One of the reasons for formatting a worksheet is to improve readability. When a background color is assigned to a cell, it is important to choose a contrasting font color. To make this point, compare the readability of the title in Figure 2-34 to the worksheet title in Figure 2-38. Using the Fill Color palette (Figure 2-35) and Font Color palette (Figure 2-36), which background color/font color combinations would be suitable? Why? Are there circumstances under which some combinations would be appropriate, while others would not be inappropriate? Applying formats to the column titles, EX 96 LECTURE NOTES Use Figure 2-29b to characterize the format of the column titles. Using Figure 2-39, describe bolding, centering, and applying a bottom border to the column titles. Discuss Other Ways to format column titles. Note the different ways in which cell contents can be aligned. As an alternative to using buttons on the Formatting toolbar, text can be aligned as it is entered into a cell by beginning the cell entry with a special character. An apostrophe (’) left-aligns text, a caret (^) centers text, and a quotation mark (“) right-aligns text. Using the Format Cells dialog box shown in Figure 2-32, point out that cell contents can be aligned vertically and rotated to various angles. The height of a row will adjust to fit the cell contents. Centering the stock symbols and formatting the dates and numbers in the worksheet, EX 96 LECTURE NOTES Explain why the stock symbols in column B should be centered. Use Figures 2-40 and 2-41 to describe centering data in cells and formatting dates. Consider Other Ways to center data in cells and format dates. Mention how the Center button and column B heading also could have been used to center column B. Page 12 of 21 Project 2: Formulas, Functions, Formatting, and Web Queries DISCUSSION TOPICS The Type list on Number sheet in Format Cells dialog box offers many different styles from which you can select to format a date. Is there a “best” style to select? Why might one style be preferable to another? What should be taken into consideration when selecting a style for the date? Formatting numbers using the Formatting toolbar, EX 98 LECTURE NOTES Use Figure 2-29b to identify the number formats in the worksheet. Discuss More About Rotating and Shrinking Entries in Cells. Define Currency style format. Use Figure 2-42 to identify the Currency Style button. Differentiate between a fixed dollar sign and a floating dollar sign. When a dollar sign is entered as part of a number, the number displays in the cell with a floating dollar sign. Emphasize that the Currency Style button applies a fixed dollar sign. In many financial documents, such as this worksheet, dollar signs ($) are displayed only in the first row where values appear and in the summary rows (e.g., total, average, highest, and lowest). Define Comma style format. Use Figures 2-42 through 2-44 to describe applying a Currency style format and Comma style format using the Formatting toolbar. Explain how the Increase Decimal button and the Decrease Decimal button are used to add, or eliminate, decimal places. Emphasize that a numeric format does not affect the actual contents of a cell. Discuss More About Formatting Numbers as You Enter Them on page EX 100. TROUBLESHOOTING TIPS Excel rounds numbers to fit the Currency style format. Rounding numbers can result in some seeming errors. When numbers are rounded, the actual number, not the rounded number, still is used in calculations. For example, if the Currency style format is applied to 3.425 and 5.269, the numbers display as $3.43 and $5.27. Yet, when the AutoSum button is used to add the numbers, the displayed result will be $8.69 (3.425 + 5.269 = 8.694 ≈ $8.69) instead of the $8.70 that might be anticipated from the displayed values. Emphasize that Excel stores the value of the numbers to several decimal places, but displays them by rounding to fit the selected format. QUICK QUIZZES What is the difference between Currency style format and Comma style format? (Answer: Currency style displays a dollar sign; Comma style does not) Applying a thick bottom border to the row above the total row and bolding the total row titles, EX 100 LECTURE NOTES Using Figure 2-29b, point out the bottom double border below row 12 and the bold row titles in cells A13:A16. Review Steps 1 and 2 to apply a thick bottom border to the row above the title row and bold the total row titles. Discuss Figure 2-45. Discuss the Q&A. Formatting numbers using the Format Cells command on the shortcut menu, EX 100 LECTURE NOTES Using Figure 2-29b, compare the position of the dollar sign in cell E4 with the position of the dollar sign in cell E14. Recall that cell E4 displays a fixed dollar sign to the far left in the cell, Excel 2003 Page 13 of 21 while cell E14 displays a floating dollar sign immediately to the left of the first digit. Remind students that the Cells command on the Format menu or the Format Cells command on the shortcut menu must be used to assign a floating dollar sign. Use Figures 2-46 through 2-48 to illustrate applying a Currency style format with a floating dollar sign using the Format Cells command. Discuss Other Ways to apply a floating dollar sign. Point out the difference between the fixed dollar signs and floating dollar signs in Figure 2-48. Use Figure 2-46 to recall the categories of formats from which students can choose. Note the importance of selecting the appropriate negative numbers format. Explain why the third selection was chosen in the Negative numbers list box. In addition to the many formats available in the Category list in the Format Cells dialog box (Figure 2-47), custom formats also can be created. To create a custom format, click Custom in the Category list and then edit the format codes in the Type field. The newly created format displays in the Custom format list for that workbook and can be applied just as any other format. PROJECTS TO ASSIGN Have students open an Excel worksheet, select a cell or range that contains a number, right-click the cell or range, and select Format Cells from the shortcut menu. Select the Number tab in the Format Cells dialog box. Choose at least four categories from the Category list (one at a time), characterize and provide a sample of each format, note the options available, and observe the effect the formats have on the selected cell(s). Finally, have students write a brief report on their findings. Formatting numbers using the Percent Style button and Increase Decimal button, EX 102 LECTURE NOTES Tell how the numbers in column J must be formatted. Point out the Percent Style button on the Formatting toolbar. Use Figure 2-49 to describe applying a Percent style format. Discuss Other Ways to apply a percent style format. Explain how the displayed value is determined when the Percent Style button is clicked. Conditional formatting, EX 103 LECTURE NOTES Describe conditional formatting. Mention how conditional formatting is used. Define condition. Explain how Excel uses a condition to determine whether to apply the formatting. Tell why conditional formatting is a powerful feature of Excel. The format used for conditional formatting takes the place of a cell’s normal formatting as long as the condition is true. When the condition is not true, the cell’s normal format is displayed again. Use Figures 2-50 through 2-54 to illustrate applying conditional formatting. Consider Other Ways to apply conditional formatting. Note the preview box in the Conditional Formatting dialog box in Figure 2-53. Explain the purpose of the Add button, Delete button, and middle text box in the Conditional Formatting dialog box. Use Table 2-5 to summarize the conditional formatting relational operators. Discuss More About Conditional Formatting on page EX 106. CLASSROOM ACTIVITIES Divide the class into small groups. Ask each group to play “Hot Potato” using the information in Table 2-5 on page EX 106. 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 Page 14 of 21 Project 2: Formulas, Functions, Formatting, and Web Queries any one of the relational operators, and then tosses the ball to another member of the group, who must describe the operator. This person then becomes “It”, calling out a term and tossing the ball. Students may refer to Table 2-5 at any time during the game and they may help one another. Continue until all operators have been visited at least once. DISCUSSION TOPICS Conditional formatting often is applied when creating a worksheet that others will use. What are some specific circumstances under which conditional formatting might be used? Why? Changing the widths of columns and heights of rows, EX 107 LECTURE NOTES Note Excel’s default column width. Define character and pixel (pronounced pick´ sell or pick sell´). A pixel can be thought of as similar to the lights that are used to create characters and graphics on electronic scoreboards. Note the resolution of a typical screen display. Mention Excel’s default row height. Explain why column widths and row heights sometimes are changed. Discuss More About Painting Formats. Changing the widths of columns, EX 107 LECTURE NOTES Explain how column width can be changed. Define best fit. Consider when column widths are set manually instead of using best fit. Explain how to set a column width to best fit. Use Figures 2-55 through 2-57 to illustrate changing the widths of columns. Discuss Other Ways to change the column width. Mention how to use the Column Width command on the shortcut menu to change a column’s width. The exact width of a column can be ascertained by right-clicking the column heading and then clicking Column Width on the shortcut menu. Note that column width can vary between zero (0) and 255 characters. Describe hiding cells. Explain how to display a hidden column. When the mouse pointer is on the hidden column boundary, it becomes a split double arrow separated by two parallel lines. Dragging this pointer to the right displays the hidden column. Columns also can be hidden by right-clicking the column heading and then clicking Hide on the shortcut menu. Once hidden, columns also can be revealed by positioning the mouse pointer just to the right of the hidden column, right-clicking, and then clicking Unhide on the shortcut menu. Discuss More About Hidden Columns on page EX 110. QUIZ QUIZZES What is the maximum number of characters for a column’s width? (Answer: 255) How do you display a hidden column? (Answer: Position the mouse pointer to the right of the column heading boundary where the hidden column is located and then drag to the right) Changing the heights of rows, EX 110 LECTURE NOTES Note that row height automatically is adjusted when font size is changed. Explain why row heights are changed manually. Use Figures 2-60 through 2-62 to illustrate changing the height of a row by dragging. Consider Other Ways to change row height. Note that row height can vary from zero (0) to 409 points. Explain hiding and displaying rows, which is similar to hiding and displaying columns. Tell how to set a row height to best fit. Although hidden rows and columns cannot be seen, cells in them still can be used in formulas. To see the contents of a hidden cell Excel 2003 Page 15 of 21 without unhiding the hidden row or column, type the cell reference in the Name box and press the ENTER key. The hidden cell’s contents will appear in the formula bar. Discuss More About Hidden Rows on page EX 111. DISCUSSION TOPICS Excel makes it possible to hide a row or column in a worksheet by changing the row height or column width to zero. Why might someone want to hide cells when creating a worksheet? QUICK QUIZZES How do you display a hidden row? (Answer: Position the mouse pointer just below the row heading boundary where the row is hidden and then drag down) How to you set a row height to best fit? (Answer: Double-click the bottom boundary of the row heading) Checking spelling, EX 111 LECTURE NOTES A misspelled word can damage the credibility of even the best worksheet. Imagine reviewing a stockbroker’s worksheet entitled “Curent Best Buys.” No matter how thorough the broker’s research, the misspelled “Current” is sure to shake the confidence of the worksheet’s readers. Define Excel’s spell checker. Note that spell checker checks words in the worksheet against words in its standard dictionary, which is shared by all Microsoft Office 2003 applications. Point out that the Spelling dialog box in Figure 2-63, and indicate that it can be used to add specialized words to a custom dictionary (by clicking the Add to Dictionary button). Use Figures 2-63 and 2- 64 to illustrate checking spelling on the worksheet. Discuss Other Ways to check spelling. Using the Spelling dialog box in Figure 2-63, tell how to correct a word with one of the suggestions, correct a word with a spelling that is not in the suggestions list, change the word throughout a worksheet, skip correcting a word, and ignore a word throughout the entire worksheet. Consider additional guidelines when using the spell checker. Discuss the Q&A on page EX 113. CLASSROOM ACTIVITIES Using Figure 2-63, ask students to distinguish between the top three buttons (Ignore Once, Ignore All, and Add to Dictionary) in the Spelling Dialog box and bottom three buttons (Change, Change All, and AutoCorrect) in the dialog box. If necessary, explain that the first group represents options used when a word is spelled correctly; the second group of buttons represents options used when a word is misspelled. Have students suggest situations in which each button might be used. DISCUSSION TOPICS Excel’s spell checker is a valuable tool, but is it enough merely to spell check a worksheet, or is proofreading necessary? Consider how a worksheet detailing company expenses with a column title “pay roll” instead of “payroll” would be received. What are some other examples of misspellings that might not be detected by spell checker? Page 16 of 21 Project 2: Formulas, Functions, Formatting, and Web Queries Previewing and printing the worksheet, EX 113 LECTURE NOTES Describe previewing the worksheet. Differentiate between portrait orientation and landscape orientation. Point out how the Print Preview command on the File menu or the Print Preview button on the Standard toolbar can reduce frustration. Print preview saves time and paper by helping to eliminate unattractive printouts. Use Figures 2-65 through 2-67 to illustrate previewing and printing a worksheet. Discuss Other Ways to preview a worksheet. Note that a saved workbook will maintain the same orientation until it is changed. Use Table 2-6 to describe the buttons in the Preview window. Point out alternative ways to move from page to page and magnify or reduce the print preview. Use Figure 2-65 to describe the options available in the Page Setup dialog box. Each tab in the Page Setup dialog box is used for a different purpose: Page Set orientation, scaling, paper size, print quality, first page number Margins Specify margins and whether to center the printout on the page Header/Footer Select or customize a built-in header or footer Sheet Stipulate a print area, print titles, what is printed, and page order Mention when the Print dialog box does, and does not, display. Discuss More About Error Checking. TROUBLESHOOTING TIPS It is important to distinguish the difference between clicking the Print button on the Standard toolbar and clicking the Print command on the File menu or a Print button in the dialog box or Preview window. To choose a printer, select the number of copies, or determine what pages to print students must use the Print command on the File menu or the Print button in a dialog box or the Preview window. Clicking the Print button on the Standard toolbar will not display the Print dialog box. Printing a section of the worksheet, EX 116 LECTURE NOTES Consider circumstances in which it might be desirable to print only a section of a worksheet. Use Figures 2-71 and 2-72 to describe printing a section of the worksheet. Discuss Other Ways to print a section. Using Figure 2-71, describe three option buttons in the Print what area: the Selection option button, the Active sheet(s) option button, and the Entire workbook option button. The Print dialog box also has two check boxes: Print to file in the Printer area and Collate in the Copies area. When the Print to file check box is checked, a printer file is created that saves the workbook along with the necessary information to print the workbook. Any printer that uses the same printer language as the printer specified in the Name text box can print the file and match the output. If the printer file is attached to an e-mail message, it can be used to print the workbook even if the computer receiving the message does not have Excel installed. When the Collate check box is checked and more than one copy of a multi-page workbook will be printed, an entire copy of the workbook is printed before the next copy of the workbook is printed. Discuss More About Printing a Section of a Worksheet. QUICK QUIZZES Which option button in the Print what area of the Print dialog box instructs Excel to print the worksheet currently on the screen? (Answer: The Active sheet(s) option button) Excel 2003 Page 17 of 21 Which option button in the Print what area of the Print dialog box instructions Excel to print all of the worksheets in the workbook? (Answer: The Entire workbook option button) Displaying and printing the formulas version of the worksheet, EX 118 LECTURE NOTES Explain how the values version of a worksheet is different from the formulas version of a worksheet. Discuss More About Values versus Formulas. Point out that CTRL+ACCENT MARK (`) is used to toggle between the two versions. Define debugging. The term “debugging” is said to have originated with famed computer programmer Grace Hopper (inventor of COBOL), who traced a computer malfunction to a bug (a moth) in the machine. Note the adjustments that must be made to fit the formulas version printed on one page. Describe the Fit to option. Use Figures 2-73 through 2-75 to illustrate displaying the formulas in the worksheet and fitting the printout on one page. Consider Other Ways to display formulas. Discuss More About the Fit To Option on page EX 121. CLASSROOM ACTIVITIES Figure 2-73 shows the formulas version of the worksheet. How might displaying the worksheet in this version make it easier to find and correct errors? QUICK QUIZZES What is displayed in the formulas version of the worksheet? (Answer: Formulas instead of values) How do you toggle between the values version and the formulas version of a worksheet? (Answer: Press CTRL+` (CTRL+ACCENT MARK)) How does Excel make a worksheet fit on one page when you choose the Fit to option? (Answer: Excel automatically reduces or enlarges the worksheet by the necessary percentage.) Changing the Print Scaling option back to 100%, EX 120 LECTURE NOTES Point out when the Print Scaling option must be reset. Review Steps 1 through 4 to change the Print Scaling option back to 100%. Describe the Adjust to box. CLASROOM ACTIVITIES Ask students to suggest circumstances when the Fit to option might be used. If necessary, point out that the Fit to option is especially convenient when a worksheet is just a little bit larger than a single page. Importing external data from a Web source using a Web query, EX 120 LECTURE NOTES Mention Excel’s capability of obtaining external data from World Wide Web sites. When data is subject to change (such as the current value of stocks in the Blue Chip Stock Club worksheet), this capability is invaluable. Define Web query. Discuss More About Web Queries. Use Table 2-7 to list the types of Web queries available when Excel is installed. Explain why the data returned by the stock-related Web queries is considered real-time. Use Figures 2-76 through 2-82 to illustrate getting external data from a Web source using a Web query. Consider Other Ways to use a Web query. Using Figure 2-80, describe Excel’s display of the data returned from the Web Page 18 of 21 Project 2: Formulas, Functions, Formatting, and Web Queries query. Use Figure 2-81 to characterize the External Data toolbar. Explain the purpose of the Refresh All button and the Query Parameters button. PROJECTS TO ASSIGN In addition to obtaining real-time stock quotes, major indices, and currency rates, Web queries can be used for many other purposes. For example, Web queries can be used to acquire the latest sales information from an e-commerce Web site. Web queries are suitable for almost any content that involves dynamic lists of data or tables of information. As an extra-credit project, encourage students to use a Web query to obtain up-to-date information on a topic of interest. Using a search engine and appropriate keywords, they should locate a Web site that has the required information readily available. Ideally, the site should have validity, reliability, and longevity. (Government or professional organization sites often offer a good source of useful information.) After locating a site, they should record the URL. Then, they should open a new Excel workbook and use the New Web Query command on the Import External Data submenu (Figure 2-76) to import the data. Have students print the and turn in the worksheet that results, including the URL they used to obtain their information. QUICK QUIZZES What types of external data can be returned by the built-in Web queries in Excel? (Answer: Currency rates, major indices, and stock quotes) How do you make the External Data toolbar appear? (Answer: Right-click any toolbar and then click External Data) Changing the worksheet names, EX 124 LECTURE NOTES Use Figure 2-80 to point out the tabs at the bottom of the Excel window. Explain how the sheet tabs are used to display sheets in a workbook. Use Figures 2-82 and 2-83 to describe changing the worksheet names. Note limitations on sheet names. Using Figure 2-83, explain how the tab split box can be used to display more sheet tabs and how the tab scrolling buttons can be used to move between sheets. Keyboard shortcuts (CTRL+PAGE UP or CTRL+PAGE DOWN) also can be used to move between sheets. A tab scrolling button can be right-clicked to display a list of worksheets in the workbook. To access a worksheet, simply click its name in the list. This is particularly convenient if a workbook contains so many worksheets that moving between them using the tab scrolling buttons would be inconvenient. Discuss More About Sheet Tabs. QUICK QUIZZES What is the maximum number of characters for a worksheet name? (Answer: 31 characters, including spaces) Where are the tab scrolling buttons located? (Answer: To the left of the sheet tabs) E-mailing a workbook from within Excel, E 2.63 LECTURE NOTES Describe e-mail. Discuss More About Obtaining an E-Mail Account. Explain the capability of e- mailing a workbook or worksheet directly from within Excel. Students will be able to complete this activity only if they have an e-mail address with Outlook, Outlook Express, Microsoft Exchange Client, or another 32-bit e-mail program compatible with Messaging Application Excel 2003 Page 19 of 21 Interface. Use Figures 2-84 and 2-85 to illustrate e-mailing a workbook from within Excel. Discuss Other Ways to e-mail a worksheet. Contrast sending a workbook as an attachment with sending a worksheet from within Excel using the E-mail button or by clicking the Mail Recipient command on the File menu. Consider some of the options available when sending an e-mail from within Excel. DISCUSSION TOPICS If you want to e-mail a workbook, you can either send it as an attachment or send it from within Excel. What circumstances might require e-mailing a workbook from within Excel? What circumstances might require e-mailing the workbook as an attachment? Does personal preference play a role? PROJECTS TO ASSIGN Explain to students that if they currently do not have an e-mail address, they can obtain a free one. Encourage students to use a search engine to find sources for free e-mail addresses, and follow the instructions to sign up. Saving the workbook and quitting Excel, EX 126 LECTURE NOTES Review Steps 1 and 2 to save the workbook and quit Excel. Discuss More About Microsoft Certification. Project summary, EX 127 LECTURE NOTES Briefly review the material presented in this project. If students have a SAM user profile, encourage them to log in to their SAM account and go to the assignments page for additional assignments. What you should know, EX 127 LECTURE NOTES Encourage students to use this section in preparing for tests and quizzes. Discuss More About The Quick Reference on page EX 128. Learn it online, EX 129 LECTURE NOTES These exercises ask students to use the Web for additional activities, information, and resources related to topics presented in this project. Have students use their browsers and the given URL to complete selected exercises. Apply your knowledge, EX 130 LECTURE NOTES This exercise gives students a chance to use what they have learned in this project with a document on the Data Disk. Exercise 1 can be reviewed and assigned at this time. Page 20 of 21 Project 2: Formulas, Functions, Formatting, and Web Queries In the lab, EX 132 LECTURE NOTES These exercises provide students with practice in using the skills developed in this project. Exercises 1 through 3 can be reviewed and assigned at this time. Cases and places, EX 140 LECTURE NOTES These exercises offer students the opportunity to learn more about Excel 2002 through open- ended activities with varying degrees of difficulty. Students can be assigned one or more exercises or be allowed to choose the exercises in which they are most interested. Excel 2003 Page 21 of 21 KEY TERMS arguments (EX 81) hiding cells (EX 109) AVERAGE function (EX 81) landscape orientation (EX 113) best fit (EX 107) MAX function (EX 82) blank cell (EX 80) MIN function (EX 84) character (EX 107) order of operations (EX 74) comma style format (EX 98) pixel (EX 107) condition (EX 104) Point mode (EX 75) currency style format (EX 98) portrait orientation (EX 113) debugging (EX 118) previewing the worksheet (EX 113) e-mail (EX 125) Range Finder (EX 89) equal sign (EX 74) smart tag indicator (EX 78) fixed dollar sign (EX 98) smart tags (EX 78) floating dollar sign (EX 98) spell checker (EX 111) formula (EX 73) values version (EX 118) formulas version (EX 118) Web query (EX 120) function (EX 81)