EXCEL GRAPHING FOR SCIENCE!!!

(Ramp Lab as example)

I measure a ball rolling down a ramp. I get a set of points for distance and time.

I want to graph them, and maybe even get the best fit line.

FIRST:

Decide which is the INDEPENDENT (Cause) and DEPENDENT (effect)

This is your X & Y.

In a NEW excel spreadsheet:

In Row 1, label the columns A & B with your variables, including units, in this case

TIME and DISTANCE.

Type in your data: DO NOT INCLUDE ANYTHING BUT NUMBERS!!

TIME    (s)            DISTANCE(cm)                    

0

0

0.6

20

0.92

40

1.23

60

1.41

80

1.62

100

1.73

120

1.871

140

2.01

160

2.15

180

2.25

200

Now…….. HIGHLIGHT YOUR NUMBERS! You can include the top labels.

You need at least 2 columns to make a correct line graph.

You can plot more than one Y… but only 1 X… You can pick more than one y to highlight using the CONTROL key (Windows) or COMMAND key (MAC).

Hit the bar graph icon or go to INSERT menu, CHART.

******* PICK SCATTER GRAPH NOT LINE GRAPH IN SCIENCE/MATH!!!

The third sub graph is almost always what you want… scatter with data points connected by lines. Other choices may be more appropriate.

image-0.pict

Your data is arranged by columns (chart Wizard step 2)

Series Tab: Identify the NAME of your series (y values) by typing it in, or having it refer to a cell.

image-1.pict

Chart Wizard Step 3: Options:

Title Tab: Chart Title should refer to both measurements:

(Distance in cm as a function of Time in sec).

Value x & y are the bottom and side labels for the graph.

(Time (sec) for x, and Distance (cm) for y )

image-2.pict

Axes Tab: Both turned on

Gridlines Tab: In science we usually want both MAJOR gridlines turned on.

image-3.pict

Legend Tab: Only use if there is more than one series. Remember the NAME of the legend will have been set in the series tab in step 2.

Data Labels:  Only turn on if you want numbers ON the graph, usually too confusing.

NEXT FINISH Step 4 Almost always you want the chart as a new sheet.

image-4.pict

Next: Double Click on the different parts of the graph to change it!

Especially important is to double click the NUMBERS of the axes to set a meaningful maximum and minimum.

Under FORMAT AXES, Scale:

UNCLICK the check boxes and type in your own minimum and maximum. The major units are the gridlines of your graph.

image-5.pict

Often, double clicking the background to change it from a grey color, and double clicking the line to make it a dark color is more helpful as well.

 Here’s what this graph looks like so far:

image-6.pict

I could click on it, then copy and paste it into a Word document if I wanted!

How to find a best fit line?

Click on the line. Go to chart Add trend line.

If you think the line is linear….. pick linear

image-7.pict

In this case…. It is a parabola, I think so I pick polynomial, order 2.

Then for options: give it a name, and decide whether you want to show the equation and the correlation coefficient. Remember that the closer the r is to 1 the better the fit.

An important decision is to decide to FORCE the trend line to go through zero.

It is also a good idea to have it predict out a couple of values:

image-8.pict

So Here’s my graph!

image-9.pict

Notice, I have double clicked on my trend line to make it dashed, and the equation shows up at the top!