1 / 42

Microsoft Excel – Intermediate Level

Microsoft Excel – Intermediate Level. While Waiting (Do Now). Launch Excel Type in your name in cell A1 Enter three additional names in same column and salaries in the next column Resize Column Rename worksheet Change worksheet tab to color. Standard Addressed.

esben
Download Presentation

Microsoft Excel – Intermediate Level

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 – Intermediate Level SPS Technology Department

  2. While Waiting (Do Now) • Launch Excel • Type in your name in cell A1 • Enter three additional names in same column and salaries in the next column • Resize Column • Rename worksheet • Change worksheet tab to color SPS Technology Department

  3. Standard Addressed Standard 1: Demonstrate proficiency in the use of computers and applications as well as an understanding of concepts underlying hardware, software, and connectivity. SPS Technology Department

  4. Intermediate Excel By the end of this session you will know and be able to: • Enter formulas and functions; use the auto-fill feature in a spreadsheet application. • Produce simple charts and graphs from a spreadsheet. • Use various number formats (e.g., scientific notation, percentages, exponents) as appropriate. • Differentiate between formulas with absolute and relative cell references. • Customize formatting of charts or graphs created in spreadsheet applications. • Utilize if-then, freeze frame, and data validation SPS Technology Department

  5. You Will Demonstrate This By • Creating a grade book with an appropriate chart SPS Technology Department

  6. Page Set-Up OptionsMenu Bar>File>Page Setup . . . • Page tab • Sheet tab • Margins tab • Headers/Footers tab SPS Technology Department

  7. SPS Technology Department

  8. SPS Technology Department

  9. SPS Technology Department

  10. SPS Technology Department

  11. Advanced Formatting Features • Alignment • Text Wrapping • Orientation of cells SPS Technology Department

  12. Wrap text within Cell SPS Technology Department

  13. Creating Charts using Excel SPS Technology Department

  14. Types of Charts/Graphs • A Bar charts show the differences amongst similar things at a point in time • A Pie charts allows us to compare parts of the whole with each other • A Line chart is a good way to look at changes over time or over space SPS Technology Department

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

  16. To Create a ChartHighlight Cells you want to include SPS Technology Department

  17. On Standard Toolbar click Chart Wizard SPS Technology Department

  18. Select the Chart that fits your purpose. SPS Technology Department

  19. SPS Technology Department

  20. Legend SPS Technology Department

  21. Chart Location On different worksheet On same worksheet SPS Technology Department

  22. You can resize and move chart SPS Technology Department

  23. Online Resources – Examples of Charts/Graphs • Line Graphs http://www.nccei.org/wordguide/linechart.html • Column Chart http://www.bcschools.net/staff/ExcelHelp.htm#Chart • Simple Charts http://www.sps.springfield.ma.us/ProfDev/payne/chartseasy.html SPS Technology Department

  24. Using the Fx Functionto Perform Calculations SPS Technology Department

  25. Summing Ranges SPS Technology Department

  26. Calculating Advanced Formulas using Excel Functionson Formula Bar fx SPS Technology Department

  27. Using Functions in Excel SPS Technology Department

  28. Average – Also Called the Mean SPS Technology Department

  29. Using Formulas in a Worksheet • In the formula bar click on Fx Another way to access the Formula Function Feature SPS Technology Department

  30. Sum Function SPS Technology Department

  31. Range you wish to sum Column Range A1:A25 Row Range: A1:E1 SPS Technology Department

  32. Mean, Median, Mode & Count • The Mean is the Average • The Median is the number in the middle of a set of numbers; that is, half the numbers have values that are greater than the median, and half have values that are less. • The Mode returns the most frequently occurring or repetitive value in a range of numbers • The Count gives a numeric value to the number of cells in a range SPS Technology Department

  33. = B2+B3 = C2+C3 = D2+D3 = B2/$E$15 = C2/$E$15 = D2/$E$15 Value in cells become relative when formula is copied Value in cells remain absolute when formula is copied Relative Reference –vs.- Absolute Reference SPS Technology Department

  34. Checks whether a condition is met.Returns one value if TRUE, and another value if FALSE. IF Function • =IF(A1>=60,"Pass","Fail") Conditional Operator True value will produce Pass False Value will produce Fail Value in Cell Greater Than or Equal to 59 SPS Technology Department

  35. Conditional FormattingHighlight Range> MenuBar> Format>Conditional Formatting . . . SPS Technology Department

  36. Freeze Panes • Highlight row underneath the row you want to freeze • Highlight column to the right that you wish to freeze • Menu Bar > Window > Freeze Panes SPS Technology Department

  37. Printing Headings on All Sheets SPS Technology Department

  38. Create a Drop-Down List Referred to as a Data Validation List • Input in a range a list of frequency used data. Either in same worksheet or different worksheet. • Menu Bar • Data • Validation . . . . . SPS Technology Department

  39. Data Validation Dialog Box Choose List =$A$1:$A$25 Range were values are found SPS Technology Department

  40. SPS Technology Department

  41. Your Turn… • Create a grade book with an appropriate chart SPS Technology Department

  42. Intermediate Excel Teacher Name: ________________________________________ Student Name:     ________________________________________ SPS Technology Department

More Related