Using Excel to Make a Graph and Analyze Data
1. Making a spreadsheet
Open Excel. [These instructions are for version 2002; modifications may be necessary for other versions, but the principles will be the same.]
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 C1. Type =LN(B1). (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 C1 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 2, =LN(B1) will instead read =LN(B2). The program is Asmart@ in this way.
[A little more about formulas: how do you know what functions are available? The fx button 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 the icon on the menu bar that looks like a bar graph. (Or, click on Insert and then on Chart.)
The AChart Wizard@ will guide you through the process:
Click on the AXY (Scatter)@ icon under Chart Type.
Then click on the first chart example.
Instructions are given on the screen at this point, but briefly:
Click on the ASeries@ tab.
Click on AAdd.@
You can fill in the AName@ form with your name for the graph.
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.)
[Note: the Chart Wizard box should show which data are included in the graph. It does this by indicating a range of data in the format A1:A7, which would mean all the values in cells A1 through A7.]
Click on the colored box again on the (minimized) Chart Wizard.
Repeat this action for the y-values.
Click Next.
Now there are more options:
$ For simple graphs (only one series of data plotted), it is best to omit the legend (click the check box to unmark it).
$ Under ATitles,@ you can provide labels for the axes.
$ You can decide what kind of AGridlines@ look best. But please include either both vertical and horizontal in your graph, or none.
Click Next.
For our purposes, it is best to let the graph be an object embedded in the spreadsheet.
[For some reason, the default background for the graphs is grey, which can make it harder to read when printed out. You can remove the background by right-clicking on a blank portion of the graph, choosing the AFormat Plot Area@ option from the dialog box, and checking Anone,@ and then OK.]
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.)
To draw the best straight line through the data, right-click on one of the data points and choose AAdd Trendline.@ In the dialog box that follows, the first sample graph should be selected. Click on the AOptions@ tab, and check the ADisplay equation on chart@ box. This box can be relocated by clicking on it and dragging. You may not see enough significant figures in the slope and intercept values. If this is the case, right-click on the equation box, select AFormat Data Labels,@ select ANumber,@ choose AScientific,@ and adjust the decimal places to an appropriate value. (You have to use your judgment.*)
Voila! After you press AOK,@ you should have your spreadsheet data, graph, and straight line equation all on one page. (You may have to move the graph box around, which can be done by clicking on it and then dragging the mouse.)
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.