1
Tutorial on Excel and Microsoft Equation Editor
Entering data in Excel
1) Open Word and create a document, just a blank page. Put your name at the top. You don’t need a title.
2) Open Excel. Choose “Excel workbook”.
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…. Not sure how wide to make the column? Click on format, column, autofit selection… the width of the column should automatically adjust so that all the text fits in one cell.
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
Excel might display the 6.0 as "6". Get the decimal in there: In the home column, under number, change general to number in the scroll menu, and then press the button with the blue arrow to change the number of decimals. If you have the old version of Excel, click on the cell, go to "format" menu, then "cells," then "number," then choose the number of decimal places.
6) Save the Excel file just in case it crashes or we have a fire drill or something.
Using Formulas in Excel -- it always starts with an = sign
7) Now we want to convert those masses to kg.
8) At the top of column C, type "mass of object (kg)". Change the width of the column so it fits.
9) 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
10) 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
10b) 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.
10c) 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.
Select "copy" from the edit menu.
Click the mouse somewhere in column B, below the weight data.
Select "paste" from the edit menu. 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. When you select this, it will ask you to choose how to paste. Choose "values". Now the numbers should be pasted normally.
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.
Click on, “Format,” and then “Cells,” and then choose “Border.”
Choose both where you want the borders and which style you want.
Here’s another way to get there. Click on “Home,” as shown below:
Then find and click on this symbol in the menu that appears:
From there, follow the directions above. 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 Word, 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, Excel 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".
4) Click on " marked scatter.”
5) Click on, “Chart Layout,” as seen below:
Choose to have the chart title above the chart. You can click on “Chart Options” to change the font. Click on your graph where the box for the title is, and title it, “Weight vs. Mass.”
6) Click on “Axis Titles” and choose to have the x-axis title below the axis and the y-axis title “rotated.” Title the x-axis “mass (kg)” and the y-axis, “weight (N).”
7) Now let’s change the scale of each axis to show the data best. Under “Axes,” point to, “Horizontal Axis,” and then click, “Axis Options.” Here you can 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. Do the same for the “Vertical Axis.” Under “Axis Options,” you can also click “Ticks” to make little tick marks on the axis.
8) Now we’ll add some gridlines. Click on “Gridlines, then point to the axis you want to change. Choose the gridlines you’d like to be displayed.
9) This graph doesn’t need a legend (key). Click on “Legend,” and choose, “No legend.”
Now you should have a beautiful graph! Hooray!
10) One more tip – if you want to change what data is included on the chart, click on “Charts,” in the toolbar above, and then under, “Data,” choose, “Select.” Select the data you want, and then click, “Ok.”
11) Now let's get the best fit line.
Select (click on) the graph, and then choose “Trendline,” from the “Chart Layout” toolbar above. Choose "linear". You should have a best fit line on your graph now.
12) Click on the line. Make sure the trendline is selected, not just the graph.
13) Under the "format" menu at the top of the page, choose "trendline.” Choose, "options", then select "Display equation on chart" and "display R-squared value on chart". -- This puts the equation of the line on the graph and the R2 value. Excel probably pasted those things right onto the line. Carefully click on them and drag them over to some white space where they're easier to view.
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.
Now select the whole graph and trendline and paste it into your Word document. When you have pasted the graph into Word, have your teacher initial here: ______
Don't go on until your teacher has initialed.
Typing subscripts and superscripts in Microsoft Word
Type "t2". Then highlight the 2. Then click on “Home,” as shown below.
For superscript, click on the A2 and for subscript, click on the A2.
Into your word 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.