Excel

1. Excel Basic Charts & Graphs

2. Objectives • Quick Excel Interface Review • Learn commands used to insert a chart/graph • Learn how to edit a chart/graph • Learn how to save chart/graph as an image for use on Illustrator

3. Sources • Kids Count Data Center • http://datacenter.kidscount.org/ • American Fact Finder • http://factfinder2.census.gov/faces/nav/jsf/pages/index.xhtml • U.S. Census Bureau • http://www.census.gov/ • Google Public Data Explorer • http://www.google.com/publicdata/directory

4. Resources • Pew Research Center • http://www.pewresearch.org/ • United States Census: Housing Topics • http://www.census.gov/housing/ • Icon Finder • https://www.iconfinder.com/

5. Excel Basics

6. Excel Interface • Ribbon • Commands • Workbook • Worksheet(s)

7. The Worksheet • Columns • Rows • Cells Example shows Data has column headers and how many variables?

8. Entering Data • Ready • Enter • Edit

9. Try It Enter data provided below Mother’s average time spent a week doing child care, house work, and unpaid work • 1965: • Childcare = 10.2 hrs.., Housework = 31.9 hrs.., Paid Work = 8.4 hrs. • 1975: • Childcare = 8.6 hrs., Housework = 23.6 hrs., Paid work = 14.9 hrs.

10. Formatting Data • Tell excel if contents in cell is: • Number • Text • Date • Currency • Right Click > Format Cells • Try It with data you entered…

11. Working with Charts/Graphs

12. Why Use Charts or Graphs? • Show series of numeric data in a graphical format • Easier to understand large quantities of data • See relationship between different data • Use on an infographic…

13. Insert Tab • User Insert Tab for Charts

14. Chart/Graph Examples on an Infographic

17. Before You Start • Have you gathered your data? • Recorded sources to cite? • Will you import data? • Copy Data? • Manually enter data from survey?

18. Open Sample File • Chart & Graph Example • Save to Desktop and Open File

19. Lay Out Data

20. Backup Your Data • Create copy of existing worksheet • Work from worksheet copy • Save workbook with new name Demonstration + Activity: On sample excel file make a copy of Parental Time Use Worksheet

21. Rename worksheets • Helps keep track of worksheet contents • Limit of 31 Characters • Worksheet with Raw data should contain “Raw data” reference Demonstration + Activity: Rename the original Raw data worksheet and worksheet copy

22. More Worksheet Options • Add a Tab Color • Hide Worksheets

23. Edit Data

24. Remove duplicates • Find duplicate data with conditional formatting • Filter for Unique Values • Use Remove Duplicates Command

25. Demo & Activity • Use Conditional Formatting, Remove duplicates, options • Clear rules from selected sheets when you remove duplicates • Home > Styles > conditional formatting > clear rules from entire sheet

26. Charts

27. Bar/Column Graphs • Show amounts or # of times a value occurs • Vertical bar or rectangle • Best to show differences in data being compared • Taller the bar, the greater # of times value occurs

28. Line Graphs • Plot changes in data over time • Vertical and Horizontal Axis • Time plotted on x-axis • Other data on vertical y axis • Used to predict future results

29. Scatter Plot Graphs • Show trends in data • Useful for large data sets • “Best Fit” line – closer the points, stronger correlation • Left to Right = Positive correlation • Right to Left = Negative correlation

30. Pie Charts/Circle Graphs • Chart one variable at a time • Best used to show % • Pie represents 100% • Slices represent data values in % • E.g.. Company profits each month as percentage of yrs. total profit

31. Insert a Chart • Open Sample Data • What type of chart is best? • Select data to be charted • Insert Tab > Charts • Choose Chart Type • Chart is inserted • Use Line Graph

32. What’s wrong with chart? Dates should display on x-axis…

33. Edit Chart • Right Click Chart > Select Data

34. Select Data Source • Edit Horizontal Axis Labels • Select Edit > Select Dates on Excel Sheet • Remove date from Legend Entries

36. Chart Tools • Design • Change chart type • Select Data • Edit Layout and Styles • Move Chart

37. Edit Layout & Style • Chart Layouts Group • Select Layout 3 • Adds a Chart Title option and moves legend below x axis • Chart Styles Group • Choose any style of choice

38. Layout Tools • Edit Axis Labels • Labels Group > Axis Title • Add Horizontal Axis - “Years” • Add Vertical Axis- “Hours” • Rename chart title: “Father’s Parental Time Use”

39. Format Tools • Format individual chart/graph elements • Menus provide formatting options depending on item selected in chart • Resize As Needed – Larger is Better • Drag corner • Size Group

40. Changing Chart Type • Chart Tools > Design Tab • Type Group > Change Chart Type • Choose Column

41. Activity • Practice all steps with “Mothers” Parental time data • Use a column chart again

42. Using a Picture Fill

43. Using Fill Options • Best for Column/Bar charts • Easier to see type of data presented • Great for infographic

44. Activity • Find the following pictures/icons online: • Dollar sign • Baby • House • Search Google or Icon Finder • Insert each picture as a fill for columns chart

45. How To Insert A Fill • Select chart series, click any column • Right Click > Format Data Series • Fill > Picture or Texture Fill • Insert from > File • Find .jpg file • Stack and scale with units

47. Remove Picture Fill • Right Click Bar > Format Data Series • Click Fill, and then under Fill, click Automatic. • Clicking “No Fill” removes all formatting for element

48. Saving Chart/Graph as Image • Right Click on Chart > Copy • Open Paint or Photoshop • Paste • Save as .jpg • Images can be inserted into Illustrator

49. Summary • Learned basics about Excel Interface • Learned how to insert and edit a Chart • Learned how to use a Picture Fill • Learned how to save charts as Images

50. Questions?