using google sheets to help with data. sheets is a spreadsheet program that can interface with docs,...

Using Google SheetsUsing Google SheetsUsing Google SheetsUsing Google Sheets

To help with dataTo help with data

• Sheets is a spreadsheet program that can interface with Docs, or Slides

• A spreadsheet program has cells (little blocks) that we can put information and then relate the cells to each other.

• We can add text, numbers, or calculations

• To open Sheets, from “My Drive”, click “New” then “Google Sheets”

• Double Click on the “Untitled Spreadsheet” and type in a name of the document

• Notice across the top are letters, and along the left side are numbers

• Each cell has its own address such as C14

• Let’s start by typing “Distribution of Hair Color by Class” in B4

• Create a table as shown

• Click and hold the mouse on cell B5 and drag it to E9. There should be a block surrounding B5-E9

• It may be important to know how many students there were total. – Click on E10 and type “=SUM(“ – You can now drag a box around all

the cells you want to add up. Drag from C6 to E9

– Type “)” to close the parentheses– Press the Enter key

• Total students should show as 82

• Let’s average each type of hair color– Click on F6 and type “=AVERAGE(“

and drag a box from C6 to E6– Type “)” to close the parentheses– Press the Enter key

Pretty Cool huh?

• Instead of typing that for each hair color, let’s copy the formula. The program will automatically adjust for the other rows.– Click in F6 – Click “Edit” from the menu at the top– Click “Copy”– Drag a box around the empty cells F7 to F9– Click “Edit” then “Paste”

Types of GraphsTypes of Graphs Types of GraphsTypes of Graphs

A Picture is Worth a A Picture is Worth a Thousand WordsThousand Words

Line Graphs

• Line graphs compare two variables (One is usually time)

• Given one variable the other can easily be determined

• Show trends in data and show how one

variable is affected by the other as it increases or decreases

• They enable the viewer to make predictions

about the results of data not yet recorded

Bar Graphs• Make comparisons between

different groups (variables) very easy to see.

Bar Graphs

f10 f18 f26 pf10 pf18 pf260

50

100

150

200

250

300

350

400

450

500

550

600

650

700 f10 = 10 C.f.

f18 = 18 C.f.

f26 = 26 C.f.

pf10 = 5 C.p. and 5 C.f.

pf18 = 9 C.p. and 9 C.f.

pf26 = 13 C.p. and 13 C.f.

InterspecificCompetition

IntraspecificCompetition

A,BB

CA,CA,B

C

Treatment

Mea

nsh

ell

len

gth

( m

)

Scatter plot graphs

• Use horizontal and vertical axes to plot X-Y data points

• Scatter plots show how much one variable is affected by another. The relationship between two variables is called their correlation .

• Scatter plots usually consist of a large body of data. The closer the data points come when plotted to making a straight line, the higher the correlation between the two variables, or the stronger the relationship.

Scatter plot GraphsIf there is absolutely no correlation present

then R2 = 0

The closer R2 is to 1 or -1, the stronger the correlation, or the stronger the relationship between the variables

The closer R2 is to 0, the weaker the correlation

Scatter plot graphs

• A perfect positive correlation is given the value of 1

• A perfect negative correlation is given the value of –1

Scatter plot graphs

• Make a column that totals each of the hair types from G6 to G9

• This is discrete data that requires what kind of chart?– That’s right, a bar chart– In Sheets, they call it a column chart

• Highlight G6-G9• Click on the insert chart icon

on the menu• Click “Charts”• In the ovals, choose “Column”

by clicking on it• Choose the “Clustered

Column” which is the first choice of pictures of the charts

• OK, you should have a chart now, but we need to clean it up.

• Choose “Customize” from the tabs on the top.

• Double Click “Add Title” and fill in “Hair Color By Class”

• Click on “Chart Title” and choose “Horizontal (Category) Axis

• Click in the block below (Click here to add title), and type “Hair Color”

• Click on “Chart Title” and choose “Vertical (Value) Axis

• Click in the block below (Click here to add title), and type “Counts”

• Right Click on the numbers that are on the X-axis to bring up a pop-up menu (or hold Control and click the numbers)

• Choose “Select Data …”• Click the icon (square) at the bottom,

right labeled “Category (X) axis labels:”

• Click on “Black” in the worksheet• Click and drag B6 to B9• A box should be drawn around the

various hair colors• Press “Enter”• Click “OK”

• We can manipulate the data now and the chart will change accordingly

• Highlight from B6 to G9• Click “Data” from the top menu• Click “Sort”• Sort by “Column G” and click

“Descending”• Look what happened to the graph

• Let’s copy the graph into a document

• Type “Distribution of Hair Color by Class” at the top of the Word document

• Press the enter key

• Click on any part of the Sheets spreadsheet to view it.

• Click on the graph once so it is enclosed by a box

• Click “Edit” then “Copy”

• Click on the Word document and click “Edit” then “Paste”

• You now have the graph in Word.

How Awesome is that?!