1 / 18

Exploring Microsoft Excel

Gain proficiency in Excel with concepts like absolute/relative references, isolating assumptions, Web integration, and date arithmetic.

cecelial
Download Presentation

Exploring Microsoft Excel

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 Chapter 2 Gaining Proficiency: The Web and Business Applications By Robert T. Grauer Maryann Barber Exploring Microsoft Excel 2002 Chapter 2

  2. Objectives (1 of 2) • Gain proficiency in the use of 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 Microsoft Excel 2002 Chapter 2

  3. Objectives (2 of 2) • Insert a hyperlink into an Excel worksheet • Save a worksheet as a Web page • Learn the concept of “round trip html” • Import data from a web query into an Excel workbook • Describe the Today function and date arithmetic Exploring Microsoft Excel 2002 Chapter 2

  4. Overview • Review relative, absolute, and mixed references • Isolate your assumptions • Learn to use the mouse to enter and copy formulas quickly • Integrate Excel and the World Wide Web • Learn the Today function • Learn the use of date arithmetic Exploring Microsoft Excel 2002 Chapter 2

  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 • The F4 key is a quick way to cycle through absolute, relative, and mixed cell references Exploring Microsoft Excel 2002 Chapter 2

  6. Getting the most from Excel • Point to cell address for formulas or functions • Use the mouse to select the cells you want to include • More accurate • Using the fill handle to copy • Use the mouse to select the cell(s) you want to copy and drag to copy to a destination range Exploring Microsoft Excel 2002 Chapter 2

  7. Hands-on Exercise 1 • Objective: Develop a spreadsheet using relative and absolute cell references • Compute the gross pay • Complete the remaining calculations • Copy the formulas • Compute the totals • Format the spreadsheet • Complete the formatting • The completed workbook Exploring Microsoft Excel 2002 Chapter 2

  8. Isolate Assumptions • Base your formulas on cell references, rather than 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 Microsoft Excel 2002 Chapter 2

  9. Excel and the Internet • Three basic Internet capabilities: • Insert a hyperlink into a worksheet • Save a workbook as a Web page • Download information from the Web through a Web query Exploring Microsoft Excel 2002 Chapter 2

  10. Some Internet Terms • HTML (HyperText Markup Language): a universal standard that can be recognized by all major web browsers • A “web page” is a common name for an HTML document • Round trip HTML: allows you to edit a web page in the application that created • An Excel document can be saved as a Web page, then edited in Excel. Exploring Microsoft Excel 2002 Chapter 2

  11. Save As Web Page Exploring Microsoft Excel 2002 Chapter 2

  12. Hands-on Exercise 2 • Objective: To insert a hyperlink into an Excel workbook; to save it as an HTML document; to edit the Web page in Excel • Compute the net earnings • Compute the percent increases • Format the worksheet • Conditional formatting • Insert the hyperlink • Save the Web page • Start Windows Explorer • View the Web page • Edit the Web page Exploring Microsoft Excel 2002 Chapter 2

  13. Web Queries • A web query allows Excel to go to a specific site on the Web to retrieve information • Assumes you have an Internet connection • Created using the Import External Data command • Query can be updated anytime by using the Refresh command Exploring Microsoft Excel 2002 Chapter 2

  14. Web Queries Exploring Microsoft Excel 2002 Chapter 2

  15. 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 Microsoft Excel 2002 Chapter 2

  16. Hands-on Exercise 3 • Objective: To include a Web query into a worksheet; to use the Today() function • Open the existing workbook • Complete the Web query • Compute the gain/loss • Copy the formulas • Format the worksheet • Refresh the query Exploring Microsoft Excel 2002 Chapter 2

  17. Summary (1 of 2) • 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 Exploring Microsoft Excel 2002 Chapter 2

  18. Summary (2 of 2) • Insert hyperlinks into Excel worksheets • Save workbooks or worksheets as Web pages (HTML documents) • Use Web queries to retrieve information from the Web • Dates stored as serial integers • Today() function always returns the current date Exploring Microsoft Excel 2002 Chapter 2

More Related