1 / 33

Exploring Microsoft Excel 2003

Learn how to use Microsoft Excel for web applications and gain proficiency in using relative and absolute references, filling cells, entering formulas, inserting hyperlinks, and saving worksheets as web pages.

anncole
Télécharger la présentation

Exploring Microsoft Excel 2003

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Exploring Microsoft Excel 2003 Chapter 2 – Gaining Proficiency: The Web and Business Applications Robert Grauer and Maryann Barber Committed to Shaping the Next Generation of IT Experts. 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 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 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 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 Exploring Office 2003 - Grauer and Barber

  6. Absolute and Relative Cell References Use absolute cell references for withholding rate and FICA rate Use relative cell references for each employees gross pay 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 Exploring Office 2003 - Grauer and Barber

  8. Example of Isolated Assumptions Assumptions are isolated and clearly labeled 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 Exploring Office 2003 - Grauer and Barber

  10. Pointing Use the mouse to select the cells to be included in the formula Notice the color coding between the borders around the selected cells and the formula in the formula bar Exploring Office 2003 - Grauer and Barber

  11. Using the Fill Handle Select cells E2:H2. Dragging the fill handle will copy all four cells to lower rows. Border around selected area. Release the mouse and formulas are copied Exploring Office 2003 - Grauer and Barber

  12. Inserting Comments Comments provide explanation for values and/or descriptions of formulas 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 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 Exploring Office 2003 - Grauer and Barber

  15. A Web Page Page is viewed through a Web browser Clicking the hyperlink will take you to the designated Web site Exploring Office 2003 - Grauer and Barber

  16. Inserting a Hyperlink If the cell is blank, type the hyperlink text in the Text to Display box Select the cell that will contain the hyperlink Enter the Web address (URL) of the site you wish to open Exploring Office 2003 - Grauer and Barber

  17. The Save As Web Page Command Enter the name for your Web page By default, the Single File Web Page format is selected. 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. Exploring Office 2003 - Grauer and Barber

  19. Round-Trip HTML Edit with Microsoft Excel button lets you start Excel and edit the worksheet Exploring Office 2003 - Grauer and Barber

  20. Selecting a Non-Contiguous Range Drag through cells to select destination range Hold the Ctrl key, then select additional cells Exploring Office 2003 - Grauer and Barber

  21. Click Format button to open Format Cells dialog Conditional Formatting Use this dialog to set criteria, in this case <0 Select cells to apply conditional formatting 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 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 Exploring Office 2003 - Grauer and Barber

  24. Web Queries Formulas in cells refer to the values in the Web query Results of the query are displayed here. Clicking a hyperlink takes you to that company’s Web site Stock ticker symbols of the companies in the portfolio Exploring Office 2003 - Grauer and Barber

  25. Creating a Web Query Enter cells containing the criteria for your web query, in this case, A5:A10 Exploring Office 2003 - Grauer and Barber

  26. Refreshing the Query Context-sensitive menu appears when you right-click in the query area External Data toolbar Refresh External Data command retrieves the latest data from the Web 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 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 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 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 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 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 Exploring Office 2003 - Grauer and Barber

  33. Questions? Exploring Office 2003 - Grauer and Barber

More Related