520 likes | 1.05k Views
Excel. Basic Charts & Graphs. 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. Sources. Kids Count Data Center http://datacenter.kidscount.org /
E N D
Excel Basic Charts & Graphs
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
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
Resources • Pew Research Center • http://www.pewresearch.org/ • United States Census: Housing Topics • http://www.census.gov/housing/ • Icon Finder • https://www.iconfinder.com/
Excel Interface • Ribbon • Commands • Workbook • Worksheet(s)
The Worksheet • Columns • Rows • Cells Example shows Data has column headers and how many variables?
Entering Data • Ready • Enter • Edit
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.
Formatting Data • Tell excel if contents in cell is: • Number • Text • Date • Currency • Right Click > Format Cells • Try It with data you entered…
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…
Insert Tab • User Insert Tab for Charts
Before You Start • Have you gathered your data? • Recorded sources to cite? • Will you import data? • Copy Data? • Manually enter data from survey?
Open Sample File • Chart & Graph Example • Save to Desktop and Open File
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
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
More Worksheet Options • Add a Tab Color • Hide Worksheets
Remove duplicates • Find duplicate data with conditional formatting • Filter for Unique Values • Use Remove Duplicates Command
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
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
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
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
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
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
What’s wrong with chart? Dates should display on x-axis…
Edit Chart • Right Click Chart > Select Data
Select Data Source • Edit Horizontal Axis Labels • Select Edit > Select Dates on Excel Sheet • Remove date from Legend Entries
Chart Tools • Design • Change chart type • Select Data • Edit Layout and Styles • Move Chart
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
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”
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
Changing Chart Type • Chart Tools > Design Tab • Type Group > Change Chart Type • Choose Column
Activity • Practice all steps with “Mothers” Parental time data • Use a column chart again
Using Fill Options • Best for Column/Bar charts • Easier to see type of data presented • Great for infographic
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
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
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
Saving Chart/Graph as Image • Right Click on Chart > Copy • Open Paint or Photoshop • Paste • Save as .jpg • Images can be inserted into Illustrator
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