Graphing Data by Spreadsheet

Uploaded by bozemanbiology on 25.11.2012


n this video I'm going to show you how easy it is to graph data using a spreadsheet.
I'm going to be using Microsoft Excel, but you could do the same thing in Open Office,
Numbers for Mac. You could even use Google spreadsheet if you want to do that. And so
let's say I do an experiment where I vary the amount of fertilizer that I give a number
of plants. And then I measure how much they grow. And this is the data that I get. So
you can see that I'm varying it from 2 grams of fertilizer all the way up to 12 grams and
then I'm getting varied amounts of growth. And so if you want to you could copy the Excel
spreadsheet, I'll put a link to it down below the video and you could try doing this on
your own or you could watch me and try one of your own. And so I'm going to switch right
now to Microsoft Excel and you can see that I have the data right here. So I've got the
fertilizer in this first column and then I've got plant growth in the second column. I don't
need to sort these columns, however I could do that if I wanted to in Excel. Basically
what I do is I want to select the data that I want to include in my chart or my graph
and then I'm going to go to insert chart and what you'll find is that nothing happens because
I haven't chosen what type of a graph that I want to do. So I'm going to choose a scatter
plot. A scatter plot is great when we're looking at two sets of number and we want to compare
those. And I'm just going to use the marks scatter plot that looks like this. You can
see that this is way easier than trying to do it by hand. It's already going to figure
out the scale and the axis for you. And so it's much quicker for sure to do this in a
spreadsheet. But changing the graph is sometimes a little difficult so I'll walk you around
that in Excel. First of all I only have one data set so I could just select this plant
growth and I could delete that. You can see here that we've got a title. That title is
not very descriptive. And so to do that, I'm going to give it a better title. So I'm going
to select that box and I'm going to say Relation of fertilizer \'85 Okay, so you can see that
my data looks pretty good. There some space up here that I maybe want to get rid of. And
so to do that in here I'm going to chart layout, so it's right next to chart, I'm going to
click on chart layout and then I can change my axis. And so I'm going to go with my vertical
axis down to axis options and so you can see here that I can set my minimum and maximum
numbers. And so my minimum, since my values start at 18 I could start with a minimum of
15 and the maximum of 100. And you can see that it's going to give me more of that graph.
If I were to do that again for my horizontal axis, so let's go to options, you can see
my low value I could set to 2 and my high value remember is just going to be 12. And
so you can see I'm using more of the graph. What am I missing now? Well I don't have labels.
So I don't have labels here on my x-axis and I don't have labels on my y-axis. To do that
I'm going to go to axis title. And so on the horizontal axis I could add a title below.
So now I can put in here my fertilizer. You want to make sure that's measured in grams.
And I can't see that, it's too small. So I could go back to home here and then I can
change the font size so you can see it. And now let's go over on the y-axis. So I'm going
to go back to chart layout. I'm going to go to axis on the vertical and let's make it
rotated title. And we'll make that, and get right in here, and that's going to be plant
growth. Measure that in centimeters and same thing. I can select all off that. We go back
to home here and we can increase the font size so you can see it. There we go. So we've
got a pretty good graph. We've got our major numbers kind of scaled for us on the side.
What am I missing? I'm missing a trend line. So I'm missing that best fit line. And this
is why I love using Excel to do this. It took me awhile to figure this out. Basically you
select the data first and then I'm going to chart and I'm going to add a trend line. And
you can see that it's going to put in a straight line. But it doesn't look like a linear relationship.
It looks like there's a curve, and so I could try a logarithmic or even a polynomial, that
looks pretty darn good. And so it's going to give me a nice bell, a nice best line.
And so that's my graphs. Super easy to do in a spreadsheet. Super fast especially when
you have a large amount of data you can crank it really, really quickly. And so that is
graphing by spreadsheet. If you'd like me to show you how to graph by hand you could
click on this right here or if you'd like to subscribe to my channel, I'd love that
by clicking right here on subscribe. And I hope that was helpful.