1 / 36

Microsoft Excel – Advanced Features

Microsoft Excel – Advanced Features. While Waiting ~ . Create an Excel Spreadsheet Enter five names of students Scores on three tests Average these tests scores Create a graph. Advanced EXCEL- By the end of this session ~ .

sunee
Download Presentation

Microsoft Excel – Advanced Features

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. Microsoft Excel – Advanced Features SPS Technology Department

  2. While Waiting ~ • Create an Excel Spreadsheet • Enter five names of students • Scores on three tests • Average these tests scores • Create a graph SPS Technology Department

  3. Advanced EXCEL- By the end of this session ~ Standard 1:Demonstrate proficiency in the use of computers and applications as well as an understanding of concepts underlying hardware, software, and connectivity. • You will know and be able to: • Use multiple sheets within a workbook and creates links between worksheets. • Import and export data between spreadsheets and other applications, and embed spreadsheets within other files/documents. • Create and use pivot tables, Printing Headings on all pages, Summing Ranges, SubTotals Features, Advance Filter, Advance Charting Features, Count Function, LOOKUP Function, Formula Auditing • Identify the use of spreadsheet skills in various careers. SPS Technology Department

  4. You will demonstrate this by . . . • Creating an activity log • Sorting data within log • Using the Subtotal feature in Excel • Selecting and graphing data • Printing out using advanced features SPS Technology Department

  5. = A1+B1 = A2+B2 = A3+B3 = B2/$E$15 = C2/$E$15 = D2/$E$15 Value in cells become relative when formula is copied. The formula changes in relationship to where it is being copied. Value in cells remain absolute when formula is copied Relative Reference –vs.- Absolute Reference SPS Technology Department

  6. Freeze Panes • Highlight row underneath the row you want to freeze • Highlight column to the right that you wish to freeze • Highlight the cell just below and to the right of the headings you want to freeze • Menu Bar > Window > Freeze Panes • Watch video from On Freeze Panes Microsoft Excel Tutorials SPS Technology Department

  7. Printing Heading Watch Video from On Print Repeating Title Rows Microsoft Excel Tutorials SPS Technology Department

  8. Printing Headings on All Sheets SPS Technology Department

  9. Summing Ranges SPS Technology Department

  10. Summing Cells on Multiply Worksheets • =Sheet1!B3+Sheet2!B3+Sheet3!B3 Sheet Name ! Cell Reference SPS Technology Department

  11. Using the Subtotal Feature in Excel SPS Technology Department

  12. Highlight data> Menu bar> Data>Subtotals… SPS Technology Department

  13. Dialog box for Subtotal SPS Technology Department

  14. Outline Structure SPS Technology Department

  15. Watch Video on Subtotals from Microsoft Excel Tutorials SPS Technology Department

  16. The Advanced Filter Feature in Excel SPS Technology Department

  17. Advanced Filter FeatureWill display only those items in a list that meet a certain criteria. • Demonstration of Advanced Filter Feature SPS Technology Department

  18. SPS Technology Department

  19. SPS Technology Department

  20. SPS Technology Department

  21. SPS Technology Department

  22. Records that meet a criteria SPS Technology Department

  23. Remove Filter MenuBar>Data>Filter>Show All SPS Technology Department

  24. Advanced Charting Features Watch video from Create Graphs and Charts Microsoft Excel Tutorials SPS Technology Department

  25. Count Function • COUNT • Counts the number of cells within a range that contain numbers. • COUNTA • Counts the number of cells within a range that are not empty whether they contain numbers or text. • COUNTBLANK • Counts the number of cells within a range that are empty. • COUNTIF • Counts the number of cells within a range that meet the given condition. SPS Technology Department

  26. SPS Technology Department

  27. LOOKUP FUNCTIONS • Locate a value in a table • VLOOKUP – looks up data in a particular column in the table • HLOOKUP – looks up data in a particular row in the table. SPS Technology Department

  28. VLOOKUPV stands for Vertical • Looks for a value in a column to the left in the same row. • Looks up 5.77 in column A, and returns the value in same row of column B • =VLOOKUP(5.77, A1:B6, 2) • =VLOOKUP(“Text”, A1:B6, 2) Column where to look Cell address or cell value range SPS Technology Department

  29. =VLOOKUP(“BOWLES”,A1:D10,2) Will return a value of green =VLOOKUP(“BOWLES”,A1:D10,3) Will return a value of Rivera SPS Technology Department

  30. HLOOKUP H stands for Horizontal • Column must be sorted in descending order • Looks for a value in the top row • =HLOOKUP("School",A1:D31,7) Or could be labeled A1 Range Row to look in SPS Technology Department

  31. =HLOOKUP(“School”,A1:D10,4) Will return a value of Boland =HLOOKUP(A1,A1:D10,7) Will return a value of Brightwood SPS Technology Department

  32. Formula Auditing • Display the relationships between formulas and cells • You can display both precedent cells and dependent cells of a formula • Menu Bar > Tools >Formula Auditing>Trace Precedents or Trace Dependents SPS Technology Department

  33. Menu Bar>Tools>Formula Auditing>Trace Precedents SPS Technology Department

  34. Menu Bar>Tools>Formula Auditing>Trace Dependents SPS Technology Department

  35. Pivot Tables and Charts • An interactive, Excel Report that summarizes, manipulate, and analyzes data. • Must set up table in a specific format • Excel Tutorials from • http://www.schooldatatutorials.org/ • Click on Pivot Tables and Charts link SPS Technology Department

  36. Your Turn… Manipulating a Time Log • Create an activity log • Sort data within log • Use the Subtotal feature in Excel • Select and graph data • Print out using advanced features SPS Technology Department

More Related