1 / 18

Exploring Microsoft Excel 2003

Learn the key concepts of Excel cell referencing, isolated assumptions, using Excel effectively, and integrating Excel with the web.

yerkes
Download Presentation

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 Vol 1 2/e- Grauer and Barber

  2. 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 Vol 1 2/e- Grauer and Barber

  3. 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 Vol 1 2/e- Grauer and Barber

  4. 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 Vol 1 2/e- Grauer and Barber

  5. Example of Isolated Assumptions Assumptions are isolated and clearly labeled Exploring Office 2003 Vol 1 2/e- Grauer and Barber

  6. 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 Vol 1 2/e- Grauer and Barber

  7. 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 Vol 1 2/e- Grauer and Barber

  8. 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 Vol 1 2/e- Grauer and Barber

  9. Inserting Comments Comments provide explanation for values and/or descriptions of formulas Exploring Office 2003 Vol 1 2/e- Grauer and Barber

  10. Excel and the Internet • Insert a hyperlink into a worksheet • Save a workbook as a Web page • Download information from the Web through a Web query Exploring Office 2003 Vol 1 2/e- Grauer and Barber

  11. Round-Trip HTML • 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. Exploring Office 2003 Vol 1 2/e- Grauer and Barber

  12. Round-Trip HTML Edit with Microsoft Excel button lets you start Excel and edit the worksheet Exploring Office 2003 Vol 1 2/e- Grauer and Barber

  13. Selecting a Non-Contiguous Range Drag through cells to select destination range Hold the Ctrl key, then select additional cells Exploring Office 2003 Vol 1 2/e- Grauer and Barber

  14. 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 Format  Conditional Formatting Exploring Office 2003 Vol 1 2/e- Grauer and Barber

  15. Web Queries • Allows Excel to retrieve information from the Web • Requires an active Internet connection • Data  Import External Data • Created with the Import External Data command • Can be updated anytime with the Refresh command Exploring Office 2003 Vol 1 2/e- Grauer and Barber

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

  17. 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 Vol 1 2/e- Grauer and Barber

  18. 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 Vol 1 2/e- Grauer and Barber

More Related