Exploring Office 2003 - Grauer and Barber

1. Exploring Office 2003 - Grauer and Barber 1 Exploring Microsoft Excel 2003

2. Exploring Office 2003 - Grauer and Barber 2 Objectives Gain proficiency in using relative and absolute references Explain the importance of isolating the assumptions in a worksheet Use the fill handle to copy a range of cells Use pointing to enter a formula

3. Exploring Office 2003 - Grauer and Barber 3 Objectives (continued) Insert a hyperlink into an Excel worksheet Save a worksheet as a Web page and then view the page in a Web browser Import data from a web query into a workbook; refresh the query to obtain current information Describe the Today() function and its use in date arithmetic

4. Exploring Office 2003 - Grauer and Barber 4 Case Study: The Proper Tip The opening case study focuses on how to use a spreadsheet to calculate the proper tip. Students will create a table, with bill amounts ranging from $10 to $200 along the side and percentages representing service levels ranging from 10% to 20% along the side All assumptions should be isolated. This includes the lowest amount in the table, the increment between rows of the table, and the percentages used for each level of service. All formulas should contain cell references, not references to the actual amounts and percentages. If they use a mixed cell reference when calculating the first cell, i.e. $10 with a 10% tip, they can create the formula one time and copy it throughout the worksheet. Format all dollar amounts with a dollar sign and two decimal places and all percentages as percent format with no decimal places.All assumptions should be isolated. This includes the lowest amount in the table, the increment between rows of the table, and the percentages used for each level of service. All formulas should contain cell references, not references to the actual amounts and percentages. If they use a mixed cell reference when calculating the first cell, i.e. $10 with a 10% tip, they can create the formula one time and copy it throughout the worksheet. Format all dollar amounts with a dollar sign and two decimal places and all percentages as percent format with no decimal places.

5. Exploring Office 2003 - Grauer and Barber 5 Cell Referencing Absolute reference: remains constant throughout a copy operation Specified with a dollar sign before the column and row, i.e. $B$4 Relative reference: adjusts during a copy operation Specified without dollar signs, i.e. B4

6. Exploring Office 2003 - Grauer and Barber 6 Absolute and Relative Cell References It is very common to use absolute references to refer to assumptions, since they normally remain the same for each row in the table. For values that change in each row, in this case, an employee’s gross pay, use relative references. Notice that assumptions are clearly labeled and isolated from the body of the worksheet. This makes editing the values at a later date much easier.It is very common to use absolute references to refer to assumptions, since they normally remain the same for each row in the table. For values that change in each row, in this case, an employee’s gross pay, use relative references. Notice that assumptions are clearly labeled and isolated from the body of the worksheet. This makes editing the values at a later date much easier.

7. Exploring Office 2003 - Grauer and Barber 7 Isolate Assumptions Base your formulas on cell references, not values The cells containing the values (assumptions) should be clearly labeled and set apart Change the assumptions in the worksheet and see the effects instantly Also minimizes the chance for error: you change the assumptions in one place

8. Exploring Office 2003 - Grauer and Barber 8 Example of Isolated Assumptions It is very common to use absolute references to refer to assumptions, since they normally remain the same for each row in the table. For values that change in each row, in this case, an employee’s gross pay, use relative references. Notice that assumptions are clearly labeled and isolated from the body of the worksheet. This makes editing the values at a later date much easier.It is very common to use absolute references to refer to assumptions, since they normally remain the same for each row in the table. For values that change in each row, in this case, an employee’s gross pay, use relative references. Notice that assumptions are clearly labeled and isolated from the body of the worksheet. This makes editing the values at a later date much easier.

9. Exploring Office 2003 - Grauer and Barber 9 Using Excel Effectively Enter cell addresses in formulas and functions by pointing Use the mouse to select the cell(s) More accurate than typing cell references Use the fill handle to copy Select the cell(s) and drag to copy to a destination range Insert comments When creating formulas, cell addresses can be entered by typing them into the cell or by using the mouse to point to the desired cell. Pointing is much more accurate. Later, when students learn how to enter references from other worksheets and/or workbooks, pointing will be even more helpful. When demonstrating how to use the fill handle, pay particular attention to the shape of the cursor. It must be a crosshair to use the fill handle to copy cells. Students often end up selecting a cell range rather than using the fill handle. Remind them they must release the mouse button to copy the cells. Also, the fill handle can only be used to copy to contiguous cells. Comments can be used to explain why certain values were chosen, the logic behind formulas, the creator of the workbook, along with the date it was created, or any other free-form text entry. In that way, they are much like creating comments when writing computer code.When creating formulas, cell addresses can be entered by typing them into the cell or by using the mouse to point to the desired cell. Pointing is much more accurate. Later, when students learn how to enter references from other worksheets and/or workbooks, pointing will be even more helpful. When demonstrating how to use the fill handle, pay particular attention to the shape of the cursor. It must be a crosshair to use the fill handle to copy cells. Students often end up selecting a cell range rather than using the fill handle. Remind them they must release the mouse button to copy the cells. Also, the fill handle can only be used to copy to contiguous cells. Comments can be used to explain why certain values were chosen, the logic behind formulas, the creator of the workbook, along with the date it was created, or any other free-form text entry. In that way, they are much like creating comments when writing computer code.

10. Exploring Office 2003 - Grauer and Barber 10 Pointing

11. Exploring Office 2003 - Grauer and Barber 11 Using the Fill Handle The cursor will change to a black crosshair. Students sometimes confuse the crosshair with the larger plus sign that indicates that a cell is selected.The cursor will change to a black crosshair. Students sometimes confuse the crosshair with the larger plus sign that indicates that a cell is selected.

12. Exploring Office 2003 - Grauer and Barber 12 Inserting Comments Comments are indicated by a small triangle in the upper right corner of a cell.Comments are indicated by a small triangle in the upper right corner of a cell.

13. Exploring Office 2003 - Grauer and Barber 13 Hands-on Exercise 1 Title of Exercise: Payroll Objective: Develop a spreadsheet for a simplified payroll to illustrate relative and absolute references. Use pointing to enter formulas and the fill handle to copy formulas. Input file: Payroll Output file: Payroll Solution

14. Exploring Office 2003 - Grauer and Barber 14 Excel and the Internet Insert a hyperlink into a worksheet Hyperlink: a reference to another document Save a workbook as a Web page A “web page” is another name for an HTML document Download information from the Web through a Web query Hyperlinks are typically references to other documents on the Web, that is, other Web pages. They can be links to other Microsoft Office files, locations within the same document, or links to any other document stored on a server that the user’s machine can access. The computer must either have an active Internet connection or must have a modem properly installed and configured for the hyperlink to work, if the hyperlink is to an external document.Hyperlinks are typically references to other documents on the Web, that is, other Web pages. They can be links to other Microsoft Office files, locations within the same document, or links to any other document stored on a server that the user’s machine can access. The computer must either have an active Internet connection or must have a modem properly installed and configured for the hyperlink to work, if the hyperlink is to an external document.

15. Exploring Office 2003 - Grauer and Barber 15 A Web Page

16. Exploring Office 2003 - Grauer and Barber 16 Inserting a Hyperlink

17. Exploring Office 2003 - Grauer and Barber 17 The Save As Web Page Command

18. Exploring Office 2003 - Grauer and Barber 18 Some Internet Terms HTML (HyperText Markup Language): a standard language for creating Web pages Round trip HTML: allows you to edit a web page in the application that created it An Excel document can be saved as a Web page, then edited in Excel. Single File Web Page: all elements of a Web page are saved as a single file. HTML is not a programming language. It is simply a markup language that describes how a document will appear when viewed through a browser. It uses tags to define the elements of the page. The browser interprets the tags and formats the document the way you see it on the Web. The Single File Web Page is new to Excel 2003. Prior versions of Microsoft Office allowed you to save documents as Web pages, but stored the documents as an html file with folders containing the elements of the page. When you uploaded the Web pages to your Web server, you had to upload the folders with the elements or the page did not function properly. This feature will make saving documents as Web pages much easier, but it will also give the Web developer less flexibility in editing the document after it has been posted.HTML is not a programming language. It is simply a markup language that describes how a document will appear when viewed through a browser. It uses tags to define the elements of the page. The browser interprets the tags and formats the document the way you see it on the Web. The Single File Web Page is new to Excel 2003. Prior versions of Microsoft Office allowed you to save documents as Web pages, but stored the documents as an html file with folders containing the elements of the page. When you uploaded the Web pages to your Web server, you had to upload the folders with the elements or the page did not function properly. This feature will make saving documents as Web pages much easier, but it will also give the Web developer less flexibility in editing the document after it has been posted.

19. Exploring Office 2003 - Grauer and Barber 19 Round-Trip HTML Clicking the Edit with Microsoft Excel button allows you to start Excel to edit the worksheet. You will then be back in Excel. Any changes must be saved. Students will not see the changes in their browser until they click the Refresh button, which will retrieve the most current version of the document from the server.Clicking the Edit with Microsoft Excel button allows you to start Excel to edit the worksheet. You will then be back in Excel. Any changes must be saved. Students will not see the changes in their browser until they click the Refresh button, which will retrieve the most current version of the document from the server.

20. Exploring Office 2003 - Grauer and Barber 20 Selecting a Non-Contiguous Range This is not included in the body of the text, but is covered in the hands-on exercise. Users can use the Ctrl key to select non-contiguous cells. This is a very handy feature when the worksheet is irregularly shaped or when you don’t want to copy a formula to an otherwise empty row. In this case, we did not want the formula in rows 10 or 13.This is not included in the body of the text, but is covered in the hands-on exercise. Users can use the Ctrl key to select non-contiguous cells. This is a very handy feature when the worksheet is irregularly shaped or when you don’t want to copy a formula to an otherwise empty row. In this case, we did not want the formula in rows 10 or 13.

21. Exploring Office 2003 - Grauer and Barber 21 Conditional Formatting Conditional formatting is also covered in the exercise rather than in the chapter. It is a very useful feature for quickly identifying cells where the values fall within a given range. As an example, given the gradebook exercise that was used in Chapter 1, all students with averages above 90 could be formatted in one color and all students with averages below 70 could be formatted in another color. This would allow the professor to easily identify those students who were doing very well and those that needed assistance.Conditional formatting is also covered in the exercise rather than in the chapter. It is a very useful feature for quickly identifying cells where the values fall within a given range. As an example, given the gradebook exercise that was used in Chapter 1, all students with averages above 90 could be formatted in one color and all students with averages below 70 could be formatted in another color. This would allow the professor to easily identify those students who were doing very well and those that needed assistance.

22. Exploring Office 2003 - Grauer and Barber 22 Hands-on Exercise 2 Title of Exercise: Creating a Web Page Objective: To insert a hyperlink into an Excel workbook; to save a workbook as an HTML document, then subsequently edit the Web page Input File: Statement of Earnings Output File: Statement of Earnings Solution

23. Exploring Office 2003 - Grauer and Barber 23 Web Queries Allows Excel to retrieve information from the Web Requires an active Internet connection Created with the Import External Data command Can be updated anytime with the Refresh command

24. Exploring Office 2003 - Grauer and Barber 24 Web Queries The Web query is most often associated with a database management system. An Excel Web query is very similar, but instead of using SQL and a relational database, you use the Web as your database. Excel allows you to specify the data source and the criteria that must be met. As with a database query, the information displayed by the query can be updated without recreating the query, making it useful for stock portfolios, fantasy baseball or football leagues, or any other application that requires frequently retrieving data from the Web.The Web query is most often associated with a database management system. An Excel Web query is very similar, but instead of using SQL and a relational database, you use the Web as your database. Excel allows you to specify the data source and the criteria that must be met. As with a database query, the information displayed by the query can be updated without recreating the query, making it useful for stock portfolios, fantasy baseball or football leagues, or any other application that requires frequently retrieving data from the Web.

25. Exploring Office 2003 - Grauer and Barber 25 Creating a Web Query The criteria you specify must match the criteria in the Web data source. Otherwise, the query will not return the correct values. When creating a new Web query, you may need to go to the Web site a few times to see how the data is listed.The criteria you specify must match the criteria in the Web data source. Otherwise, the query will not return the correct values. When creating a new Web query, you may need to go to the Web site a few times to see how the data is listed.

26. Exploring Office 2003 - Grauer and Barber 26 Refreshing the Query Queries are not automatically updated every time you open the worksheet. Instead, they will display the last saved values. To update, you need to use the Refresh command. This command will retrieve the latest data from the Web, using the criteria you have already provided.Queries are not automatically updated every time you open the worksheet. Instead, they will display the last saved values. To update, you need to use the Refresh command. This command will retrieve the latest data from the Web, using the criteria you have already provided.

27. Exploring Office 2003 - Grauer and Barber 27 Date Arithmetic Excel stores all dates as integers Serial numbers, beginning with January 1, 1900 The difference between dates is determined by subtracting one number from another Today() function always returns the current date Occasionally, a student will claim they entered a date, yet you’ll see an integer. Check the formatting on the cell.Occasionally, a student will claim they entered a date, yet you’ll see an integer. Check the formatting on the cell.

28. Exploring Office 2003 - Grauer and Barber 28 Hands-on Exercise 3 Title of Exercise: Web Queries Objective: Include a Web query into a worksheet to retrieve current stock prices from the Internet. Use the Today() function to illustrate the use of data arithmetic Input file: Stock Portfolio Output file: Stock Portfolio Solution

29. Exploring Office 2003 - Grauer and Barber 29 Summary Absolute, mixed, and relative references Isolate your assumptions from the rest of the worksheet Enter cell references into formulas by pointing to them with the mouse Use the fill handle to copy a formula to adjacent cells The Insert Comment command creates the equivalent of a screen tip

30. Exploring Office 2003 - Grauer and Barber 30 Summary (continued) Insert hyperlinks into Excel worksheets Save workbooks or worksheets as Web pages The Single File Web Page format stores all of the elements of a web page as a single file. Use Web queries to retrieve information from the Web Dates stored as serial integers Today() function always returns the current date

31. Exploring Office 2003 - Grauer and Barber 31 End-of-chapter Exercises Multiple Choice Practice Exercises Exercise 1 – Alternate Payroll Exercise 2 – The Sports Statistician Exercise 3 – Web Pages and Hyperlinks Exercise 4 - The Workout Schedule Exercise 5 – An Exercise in Conversion Exercise 6 – Web Queries Exercise 7 – Buying a PC Exercise 8 – Mixed References

32. Exploring Office 2003 - Grauer and Barber 32 End-of-Chapter Exercises (continued) Practice Exercises (continued) Exercise 9 – NBA Statistics Exercise 10 – Financial Forecast Mini Cases Accounting 101 – Straight Line Depreciation Wishful Thinking CD Portfolio Your Net Worth The Birthday Problem

33. Exploring Office 2003 - Grauer and Barber 33 Questions?