1 / 45

WELCOME TO

WELCOME TO. PRESENTING EXCEL By Tamara Otley. Overview. Learn about average range graphing in Excel Importing graphs into PowerPoint Learn how to import Pivot Tables Learn about clustered stacked column charts Chart Animation Auto creating PowerPoint from Excel

vala
Download Presentation

WELCOME TO

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. WELCOME TO PRESENTING EXCEL By Tamara Otley

  2. Overview • Learn about average range graphing in Excel • Importing graphs into PowerPoint • Learn how to import Pivot Tables • Learn about clustered stacked column charts • Chart Animation • Auto creating PowerPoint from Excel • I will ask for volunteers at the end to show off their PowerPoint!

  3. Average Range Graphing

  4. Average Range Graphing Open Workbook “Exercise 1 Average Range Graph.xlsx”

  5. Average Range Graphing • Notice… • Notice blanks on either side of the months • Notice the same column by the average range has the same data as January and December respectively.

  6. Highlight cells A23-O24 • Insert • Column • 2-D clustered column

  7. Average Range Graphing

  8. Average Range Graphing Change Horizontal Axis Labels • Right click on graph • Select data • Under Horizontal Axis Labels, click Edit • Click graph icon • Select cells B22-O22EnterOK

  9. Average Range Graphing Axis Options • Right click on months • Format Axis • Under Interval between labels, click specify interval unit and enter 1 • Under Vertical axis crosses, click on At Category number and enter 1 • Under Position Axis, click on On tick marks • Close

  10. Average Range Graphing Add In Average Range • Right click on graph • Select data • Under Legend Entries  click Add

  11. Average Range Graphing High Values • For Series Name, click on cell A26 • For Series Values click on the spreadsheet icon • Highlight cells B27-O27Click Ok

  12. Average Range Graphing Low Values • Under Legend Entries  click on Add • Leave Series name blank • For Series Values  click on spreadsheet icon • Highlight cells B28-O28  click ok • In Select Data Source window  click ok

  13. Average Range Graphing Creating Area • Right click on one of the Series 4 columns • Change series chart type • Select Area  click Ok • Right click on one of the Average Range columns • Change series chart type • Select Area  click Ok

  14. Average Range Graphing Format Plot Area • Right click on plot area • Fill  Solid fill  Blue (Choose color) • Boarder Color  Solid line Black • Close

  15. Average Range Graphing Format Grid Lines • Right click on vertical (value) axis major gridline • Format gridlines • Under Line color  choose solid line, then make it the same color as your background

  16. Average Range Graphing Format Series4 Area • Right click on Series 4 • Format data series  Fill  Solid fill • Choose same color as background • Note: Make sure 2011 and 2012 columns are a different color than your background or your average range.

  17. Average Range Graphing Format Legend • Click on Series 4 on the legend  hit delete • For more definition for the average range make the boarders solid black • Right click on legend  format legend  Bottom

  18. Average Range Graphing

  19. Paste Our Graph into PowerPoint • Keep Excel open • Open PowerPoint In Excel: • Right click on graph  Copy In PowerPoint: • Paste • Paste Special • Paste link

  20. Paste Our Chart into PowerPoint In Excel: • Select cells A22-N24 • Right click • Copy In PowerPoint: • Paste • Paste Special • Paste link

  21. Importing a Pivot Table • Open Workbook “Exercise 2 Pivot Table.xlsx” • Create a pivot table • Insert • PivotTable • PivotTable OK • Drag Case Number into Values field • Drag Case Type into Row Labels • Fix column and row width • Height

  22. Importing a Pivot Table • Select cells A4-B35 • Copy In PowerPoint: • Click on new slide • Paste • Paste Special • Paste link

  23. Importing a Pivot Table • Change: Grand Theft Auto to Auto Theft in Raw Data • Update Pivot Table • Notice change on PowerPoint

  24. Clustered Stacked Column Chart

  25. Clustered Stacked Column Chart Open Workbook “Exercise 3 Clustered Stacked Column Chart.xlsx” • Notice: Cells A52-G91 (Data spaced apart) • Notice: Cells O52-P89 (No spaces) • Notice: Cells P36-AA41 (2011 data)

  26. Clustered Stacked Column Chart • Highlight cells C52-G88 • Insert  Column (graph)  Stacked Column

  27. Clustered Stacked Column Chart • Drag graph up to the top of the page and enlarge to fit the space • Right click on a bar • Format Data Series • Change Gap Width to 10% • Close

  28. Clustered Stacked Column Chart Now add in “Dummy” Secondary Axis for labels • Select cells H52-H88 • Hold <Ctrl> • Select cells A52-A88 • Copy • Select graph • Paste  Paste Special • Make sure New Series, Columns, Series Names in First Row, Categories (x Labels) in First Column are selected then click OK

  29. Clustered Stacked Column Chart • Click on legend • Click on “Axis” and hit Delete • Right click on legend • Format Legend • Top • Close

  30. Clustered Stacked Column Chart • Right click on x axis • Format Axis • Alignment • Custom Angle=-45o • Close

  31. Clustered Stacked Column Chart • Right click Y axis • Format Axis • Number • Click Number and make Decimal places = 0 • Close

  32. Clustered Stacked Column Chart

  33. Clustered Stacked Column Chart Now customize! • Change background • Change bar colors • Add data labels • Change to 3D • Be creative!

  34. Clustered Stacked Column Chart Add Average Range as an upper and lower line • Right click on graph • Click Select Data • Click Add • For Series Name  select cell O52 • For Series Value  select cells O53-O89 • Click Enter  Ok • Click Add again • For Series Name  select cell P52 • For Series Value  select cells P53-P89 • Click Enter  Ok  Ok

  35. Clustered Stacked Column Chart • Right click on Range High series • Change Series Chart Type • Click “Line”, then Ok • Repeat for Average Range series • Right click on Range High series • Format Data Series • Click Line Color  Solid  choose a color • While window is open, click Average Range series and make the same color as Range High  click Close • Delete Range High from Legend

  36. Clustered Stacked Column Chart How do we add this graph to our PowerPoint?

  37. Chart Animation

  38. Chart Animation • In PowerPoint • Click Insert, Chart, 3D Clustered Column, OK • On spreadsheet that pops up, change Categories to 2008-2011, change Series to Robbery, Burglary and Auto Theft and put in numbers that make sense to you • Click Animation tab • Click dropdown arrow next to “No Animation” • Hover over choices to see preview • Make your selection

  39. IMPORTANT - Keeping the Links • Save and close your PowerPoint presentation • Open your PowerPoint presentation • Security Notice pops up • Click Update Links

  40. IMPORTANT - Keeping the Links • Edit Links to Files

  41. IMPORTANT - Keeping the Links From here you can • Update the link (Update Now) • Change the source (Change Source) • Break the link to make your PowerPoint static (Break Link)

  42. IMPORTANT - Keeping the Links Be careful when you port your PowerPoint • Give yourself plenty of time for the PowerPoint to regenerate date from the links • Make sure you have time to reestablish all the links if some are broken during porting • Final destination MUST have access to the links

  43. Volunteers • Show us your work • Tell us how you will be able to use this in your agency

  44. Auto Creating PowerPoint from Excel • Open Automatically Create PowerPoint From Excel.xlsm • Click on and review Part I Crime Sheet • Click on and review Part II Crime Sheet • Click on Charts sheet • Customize Callouts for Part I and Part II Crimes • Click “Create PowerPoint” button

  45. THANK YOU Tamara Otley Fullerton Police Department Phone: (714) 738-5349 Email: totley@fullertonpd.org

More Related