1
Tutorial on Google Drive Sheets
Entering data in Excel
1) Open Drive and create a document, just a blank page. Put your name at the top. You don’t need a title.
2) Open Sheets.
3) At the top of column A, type “mass of object (g)”.
Doesn’t fit, does it? Widen column A by moving the cursor to the top of the column until it’s on the border between A and B. Then click and drag the column to the right, making it wider until all the words fit. [If you have trouble with this or anything else, ask your neighbor or your teacher.]
4) At the top of column B, type “weight of object (N)”. Widen column B until it all fits.
5) Enter in the data below. Make sure you skip a line just like it does below.
mass of object (g) / weight of object (N)120 / 1.3
250 / 2.4
340 / 3.7
445 / 4.6
560 / 5.2
610 / 6.0
Sheets might display the 6.0 as "6". Get the decimal in there: In the row of commands, underneath the FORMAT, there is a .0 with an arrow under it (one to the left and one to the right). The one with the arrow to the left gets rid of decimal places, and the one to the right will add them. Click on the one to the right just once, so you have one decimal place.
Using Formulas in Sheets -- it always starts with an = sign
6) Now we want to convert those masses to kg.
7) At the top of column C, type "mass of object (kg)". Change the width of the column so it fits.
8) In box C3 (next to the 1.3 from column B), type " = A3/1000". (Don't type the quotation marks!)
Alternatively, you could do this:
In box C3, type "=".
Then click on box A3 (the 120).
Then type "/1000".
The number 0.120 should appear in box C3. (If it shows up as 0.12, fix it like in step 5.)
Filling down
9) We could type that formula again for the next five boxes in column C. But let's Fill Down instead:
Click on C3, then move the cursor until it is at the bottom right hand corner of the cell. The shape of the cursor will change to a black + shape. When this happens, click and drag down to C8. The column should be filled with mass in kg. Neat, huh?
Averaging and other functions
10) Lets calculate the average for each of the columns of data we have. In A9, write “=average(A3:A8)”. Instead of doing this again, lets fill sideways, using the same principles as described in Filling Down.
10b) In your next free column, lets calculate weight / mass. At the top of the column, write, “gravity.” Change the width of the column so it fits. Then next to your data, write the equation “=B3/C3”. Then fill down to fill the rest of the column.
10d) Now let’s say instead of dividing by a different mass each time, you always want to divide the weight by the same mass. If you click on each cell you’ve calculated acceleration in, you’ll see the equation has changed each time to reference the different cells used (B3/C3, B4/C4, and so on). You can hold the cell reference constant in the original equation you type by adding a dollar sign. So, try this back in your original equation: “=B3/C$3” This means the number will always stay constant in your equation. If you also put a $ in front of the A ($C$3) then both the A and the 3 would stay constant. It’s a neat trick to know when you need to reference the same value over and over in an equation.
10e) But what we want to do here is divide each data point by its corresponding mass, so go back and get rid of the $.
Note: There are many other functions you can do in addition to average. You can search through them to see what else is available by going to Insert and clicking on Function. It will show you all of the available preset equations, as well as what to write in the cell to perform each function.
Copying columns
11) Now let's try copying and pasting some columns. If the column doesn't have a formula, it's easy. Let's copy and paste column A.
Click and drag until the whole column's selected. That's A1 through A8.
Press Command + C (not the + sign, just command and c).
Click the mouse somewhere in column B, below the weight data.
Press Command + P Voila!
12) It's trickier when you're copying boxes that have a formula in them. Let's copy and paste column C, which came from a formula. Do the same thing you did in #11. When you paste the column somewhere new, the values will change. The formulas are screwing things up.
13) We want to copy the numbers in column C, but not the formulas inside the boxes.
Solution? Copy as usual, but when you paste, choose Paste Special in the "edit" menu. Click on Paste numbers only. This will paste with the values, without the equations assigned to them.
14) Ok, go back to our original four column table. Let's put borders on the table.
Click and drag across the boxes so everything is highlighted, from A1 to D9.
In the row of commands, roughly above column F, there is the image of a 2x2 box.
Click on the image, then choose which option you’d like.
Your table should now have borders. If you don't do this, sometimes when you paste the table into Word, it just comes out as numbers without table lines and it's hard to read.
15) Paste the table into your document, make any needed adjustments, and save your word document if you haven’t already.
16) Have your teacher initial here: ______Don't go on until your teacher has initialed.
Graphing
Let's graph some data. We want mass in kg on the x-axis, weight in N on the y-axis.
1) For some reason, Sheets insists that the two data columns be (1) right next to each other (2) with the x-data first, y-data second. So you need to copy the mass (kg) column and paste it into column F. Then paste the weight column into column G. Do NOT copy and paste the average row (row 9).
2) Next, highlight the two columns you want to graph. Select only the numbers, not the column headings. (This is cells F3 to G8.)
3) Under Insert menu, select "chart". You can also press the chart symbol, which is 3rd to last on the right side of the command row.
4) Sheets will recommend a chart type to you, or you can click on Chart Type, if you don’t see the one you want. We will usually be making a scatter plot, without the dots connected.
5) To change the title and label the axes, double click on each, type the name, then press enter. For title, do “Weight vs. Mass.” For the x-axis, type “Mass (kg),” and for the y-axis, type “Weight (N).” You can also change the font size and style if you’d like in each box.
6) Now let’s change the scale of each axis to show the data best. Click on the graph. When you do so, a blue border will appear. In the upper right corner of the graph, click on the downward pointing arrow, then click Advanced Edit. If you scroll down, you can see all the different items you can edit for your graph.
7) Under “Axis,” in the Min and Max boxes, you can write different values change the maximum and minimum values for the axis. By changing the major and minor units, you change what numbers are visible on the axis (try it and see how the axis changes). You can do the same for the vertical axis by clicking on the box that says Horizontal, and select Left Vertical. This can be really useful if your axis starts or ends far lower / higher than you need it to, so you can customize your graph to show your data in the best way possible.
8) Now we’ll add some gridlines. Scroll a little further down. There should already be Major gridlines, but you can also add minor gridlines (type 1 in the box and see what happens). You can also change the color of the gridline box with the second box to the right for both the major and minor gridlines. To do the same for the vertical axis, again, click on the box that says horizontal and click Left Vertical.
9) If you had more than one line of data and needed to edit the key, you would scroll further down and edit the section called Series.
Now you should have a beautiful graph! Hooray!
10) Now let's get the best fit line.
Scroll all the way to the bottom in the advanced edit window. There is a box that says “trendline.”
11) Click on the box that says none, and click “Linear.” A line should have appeared on your graph, along with a blue box that says “Trendline for Data Series 1” on the far right side.
12) Now scroll further down to edit options for the trendline. Click on the box that says Show R2. A number should have appeared below “Trendline for Data Series 1.”
R2 is a measure of how "linear" your line is.
R2 = 0: your points are scattered and there is no linear trend whatsoever.
R2 = 1: your points form a perfect line.
12) Under the trendline box in the edit options, there is a box that says label (it is defaulted to none). Click on the box for “Use Equation.” An equation should now be on your graph.
Now select the whole graph and paste it into your Drive document. When you have pasted the graph into Word, have your teacher initial here: ______
Don't go on until your teacher has initialed.
**If you are having trouble copying and pasting the graph into your document, instead of copy and paste, create a screen shot by pressing command + shift + 4. Select the area of your graph, and you should hear a camera clicking sound when the screen shot is taken. Then, in your Drive document, click Insert, Image. Select the screen shot image (it usually saves to the desktop), and voila! This problem doesn’t exist in Excel, you an just copy and paste easily into Word.
Typing subscripts and superscripts in Drive Documents
Type "t2". Then highlight the 2. Then click on “Format”.
For superscript, click on the A2 and for subscript, click on the A2.
Into your Drive document type this: "V initial squared looks like this: vi2. " (To "un-superscript" something, select the A2 again. This will undo the superscript.)
Here’s what your word document should look like:
Pat Smith
mass of object (g) / weight of object (N) / mass (kg)120 / 1.3 / 0.12
250 / 2.4 / 0.25
340 / 3.7 / 0.34
445 / 4.6 / 0.445
560 / 5.2 / 0.56
610 / 6.0 / 0.61
V initial squared looks like this: vi2.
------
Extra Fill Down Practice, if you still have time:
The Fibonacci numbers are: 1, 1, 2, 3, 5, 8, 13, 21.... Get it? The next number is the sum of the previous two numbers. Let’s call the 1st number 1, the 2nd number 1, the 3rd number 2, the 4th number 3...
Your task is to find the 300th Fibonacci number.