Using Excel to Make a Graph and Analyze Data (Short Version)
Using Excel (2007) to Make a Graph and Analyze Data
1. Making a spreadsheet
Open Excel
Note that the screen is divided into boxes, called Acells.” Each cell is designated by coordinates given at the margins; the upper, leftmost square is labeled A1. By moving the cursor and clicking on any cell, you can enter a number. (You can also move around with arrow keys.)
Make a table of your data, for example, putting x values in column A and y values in column B, so that corresponding x and y values are paired in the same row.
Data can easily be manipulated. For instance, suppose you want to take the (natural) logarithm of each y value. You will leave the y values as they are, but put the logs in another column. Click the cursor so that it highlights cell C2. Type =LN(B2). (You will see what you type in the AFormula Bar@ and in the cell.) Press the Enter key. You will see the numerical result in the cell. However, the cell also still has the formula, as can be seen by clicking on the cell (so that it is outlined) again: the formula in that cell will show up on the Formula Bar. What this means is that if you change the original y value (in column B), the corresponding ln of the new number will appear in column C because it is referenced (through the formula you typed) to whatever is in the cell in column B.
Now you want to find the logarithm of all the other values. You could do it the same way, but it is easier to copy the original formula into all the remaining cells. Click on the C2 cell, move the cursor to the little square at the lower right of the cell where the cursor should become a crosshair (this is known as the Afill handle@), click, and drag down to the end of the series. The range will be outlined and when you release the mouse button, all the proper values are entered. If you click on individual cells, you will see the formula that was used for each cell; it is based on the original one, but changes appropriately according to its position, so in row 5, =LN(B2) will instead read =LN(B5). The program is Asmart@ in this way.
[A little more about formulas: how do you know what functions are available? The Formulas tab on the Toolbar at the top will give many functions of various types that can be clicked on and used. You can also define your own functions, such as multiplying the contents of different cells. For instance, if you wanted the product of numbers in cells A1 and B1, you can write (in, say cell C1), =(A1)*(B1). To take the reciprocal of the numbers in column A, write =1/(A1). To square a number, use =(A1)^2. Formulas such as these can be copied down a whole column in the same way as described before.]
2. Making a graph:
You should already have a series of x,y data entered in the spreadsheet.
Click on an empty cell not adjacent to any filled cell. Click on the Insert tab on the menu bar. Then select “Scatter” and click on the first graph box shown.
The main menu tabs to be used at this point are labeled Design and Layout.
Click on the Design tab, and then on the Select Data Source tab and the on the Add tab.
Click on the colored box at the right of the x-value entry line, and then use the mouse to outline the range of x-values you wish to plot. (Click on the top cell, and drag the cursor to the bottom and release.) Click again on the colored box.
Repeat this action for the y-values.
Click OK twice to exit the menu boxes.
At this point you should see a graph of your data.
To format the graph further, choose the Layout tab from the menu bar.
Click on the Axis Titles tab to label the axes. (You type the label into the heading box and when you press Enter, it will appear as a label on the axis.) You can do something similar for a graph title.
Click on the Gridlines tab, which will provide detailed options.You can decide what kind of gridlines look best. But please include either both vertical and horizontal in your graph, or none.
For simple graphs (only one series of data plotted), it is best to omit the legend (R-click on it to delete).
For our purposes, it is best to leave the graph embedded in the spreadsheet.
3. Finding the equation for a straight line.
When you have a graph in which the points form a straight line, you can calculate the equation for the line. (There is no point in doing this for data that clearly forms a curved line.)
You can start from a Trendline tab in the menu bar, but an easier way to get the equation is to right-click on a data point and choose Add Trendline. From the menu box, check Linear, and also check the Display equation box. You will then see the equation, which can be moved around in the graph by clicking and dragging.
If the equation parameters do not contain enough significant figures (e.g., slope = 0.0003), right-click on the equation, choose Format Trendline Label, and from the menu select Number, which allows you to add decimal places.
One of the great things about spreadsheets is that once you have one set up that you like, you can use it to analyze similar data without repeating all the work. When you change the x, y data values, the data in the other columns will change automatically, the graph will change automatically, and the equation for the line will change automatically. Try it!
______
*When using this for analysis of real data (as opposed to textbook problems), it is desirable to have the program calculate error ranges using statistical formulas. This eliminates guesswork about significant figures. I can give you more information about this if you have a need for it.