The following instructions guide you through the process of creating a graph (called a "chart" in Excel) from a set of data, and using Excel to calculate and draw a line that best fits your data (called a "trendline" in Excel). The information determined from this best-fitting line will be used to analyze your data. In addition, you will also calculate the sum of the square of the residuals (SSR) when dealing with linear data to determine how well the function fits your data. These graphing techniques will be used in most of the labs this semester, so it is important that you understand them. You will also find that these graphing techniques are useful for analyzing data from other disciplines.

   Note that Excel has been customized on the lab computers in the Physics department. If you use another computer on campus, some of the default options may be different; however, these directions will still apply to those computers.

Creating A Graph

1. Set up columns for data in the spreadsheet, making sure that the first column contains the values to be plotted along the 'X' axis. Include a title for each column, including the units. Generally, you will only need to include the data that will actually be plotted, not your raw measurements - unless you wish to check your calculations. Be sure to save your work!

2. Click the left mouse button and drag the mouse over the data to be plotted. In most of our cases, you will not need to include the column title. However, in certain labs you will be plotting two sets of data; in that case you should include the column title. Important Hint: If you need to plot columns of data that are not adjacent, hold down the "Ctrl" key as you click and drag over the desired cells.

   

3. Click the ChartWizard button in the tool bar (see sample at right). When you have released the mouse button, the first step of the Chart Wizard appears.

4. ChartWizard Step 1 - Chart Type: You must select the type of graph to use. The default choice on our lab computers is the "XY (Scatter)" Chart Type; this is the style you will use for all your graphs. You will also note that the first Chart sub-type is selected; again, this is the one you will always choose. If you click and hold the "Press and hold to view sample" button, you will see a preview of your graph. Click the Next> button.

5. ChartWizard Step 2 - Chart Source Data: This step shows you the range of cells that you have selected for your graph. Generally, you will not have to change anything here. Note that there is a <Back button at the bottom of the dialog box; if you change your mind during any step, you can back up and choose another option. Click the Next> button.

6. ChartWizard Step 3 - Chart Options: A preview of your graph is displayed. You can add a title to the graph, and label the axes. Click in the white box below "Chart title", and type a descriptive title, but do not press the Enter key!! Your title should be descriptive; "Period as a function of Pendulum Length" is much better than "T vs. L". Click in the "Value (X) Axis" and the "Value (Y) Axis" boxes to label the X and Y axes, again remembering not to press the Enter key!! Your axis titles should include the units used. As you type each label, it will appear in the preview graph. When you are satisfied with the results, click the Next> button.

7. ChartWizard Step 4 - Chart Location: In this final step, you decide on the location of the completed graph. You can choose As New Sheet:, and the graph will be placed on a separate page; or, you can choose As Object In:, and the graph will be inserted on the same page as the data used to create it. Select one of the options, and click the Finish button.

   Each choice for the graph location has an advantage. Including the graph on the same sheet allows you to see the data and the graph at the same time. However, the printout of this graph doesn't look great. Putting the graph on a separate sheet produces a full screen graph that prints very nicely. If you decide to put the graph on the same sheet as the data, you can always move it to another sheet before printing by selecting the graph, and choosing Location... from the Chart menu. Try each method; you'll see the difference.

Adding A Best-Fit Line

8. Now that your graph is created, you'll need to analyze it. The first step is to add a best-fit line. Click the graph once to select it for editing. Click once on any one of your data points, then from the "Chart" menu, choose "Add Trendline...". (Windows 95 shortcut: Right-click on any data point, and choose "Add Trendline..."!). There are 6 possible best-fitting "trendlines", also known as regressions; Excel will determine which of the six can be used for your data (but it doesn't pick the correct regression for you!). We will be using "Linear" for straight-line data (in the form of y = mx + b), and "Power" (y = ax^b), "Exponential" (y = ae^bx), or "Polynomial" (of 2^nd order: y = ax² + bx + c) for curvilinear data. Select the appropriate regression (your instructor will assist you), and then click the Options tab. You can later change the type of fitting if you choose.

9. In the Options dialog, click the box next to "Display Equation on Chart", then click the OK button. You are then returned to your graph; the best-fitting line is included, as well as the appropriate equation for the line chosen. Excel calculates the values of the appropriate coefficients a, b, c, and m. You will note that more significant figures are presented than may be dictated by the accuracy of your measurements. It is from these coefficients that you will be performing your analysis.

10. At this point your graph is complete; click once outside of the graph to deselect it. Your instructor can show you how to edit portions of the graph if they need to be changed. If you find that a data point is incorrect, change the number, or delete the point, and the graph will update automatically.

Calculating the SSR For Linear Data

11. Before you begin, make sure that a blank cell is selected; do not choose a cell that contains any of your data! Then, from the Tools menu, choose Data Analysis... Scroll down the list, until you see Regression; click it once, then click the OK button.

12. The blinking cursor appears in the box next to Input Y Range. Click and drag the mouse over the values plotted along the y-axis of your graph (do not include the column title!). Note that the dialog box momentarily minimizes itself. When you release the mouse button, the addresses for the selected cells now appear in the appropriate field.

13. Press the tab button to move to the Input X Range field, and select the values plotted along the x-axis of your graph.

14. Now you need to choose some options. If you want the computer to "force" the regression line to go through the origin, click the box next to "Constant is Zero", placing a check mark there. If you change your mind, click the box again to remove the check mark.

15. By default, "New Worksheet Ply" is selected under "Output Options". This will place all of the calculations on a new sheet inserted into the workbook (it will be inserted before the sheet that contains your original data). A lot of numbers will appear, so you should leave this option as is.

16. Finally, click the box next to "Residuals" to perform that calculation. When finished, click the OK button. After a few moments, the results will appear on a new sheet

17. Scroll down the page until you see the "Residual Output" section. You'll need to widen the columns so that you can see all of the numbers and the column headers; do this by dragging the line between the column designations (A, B, C, etc.). The third column contains the Residuals for your data. You are going to add a fourth column to calculate the square of each residual.

18. Give the fouth column the title "Residuals^2" (unfortunately, it's difficult to use superscripts). Click the cell below this title to begin creating an equation.

19. Type an equal sign, then click once on the cell containing the first residual value. You'll notice that the address of this cell now appears in the equation cell, and that the cell with the residual is surrounded by "marching ants". To finish the equation, type ^2 (the carot, ^, is on the number 6 key). Your final equation should appear something like =C25^2. Press the Enter key to evaluate the equation.

20. It would be time consuming to repeat this process for each residual value. Fortunately, Excel allows an easy method of copying the equation you just created. Click once on the equation cell you just created. You'll notice that the lower right corner of the selected cell has a small "handle" on it. Click and drag this handle straight down, until you have selected all of the blank cells next to the residual values. Release the mouse button, and the equation is copied and evaluated.

21. Now you'll need to sum this column of Residuals². Click on the first blank cell below your last Residuals² value. Then click the AutoSum button (it has an uppercase Sigma - Σ - on it). All of your Residuals² values should be selected. Press the Enter key to calculate the sum. You might want to boldface this cell, and add some additional notation, so that it stands out.

Printing The Graph

Before your graph is printed, you should change a few options that will make your printed graph easier to view and analyze.
22. Double-click on the best-fitting line you created (not on the data points); this will bring up the "Format Trendline" dialog box. Click the down-arrow next to the "Weight" option to change the line thickness; the next thinner size works nicely, and looks better when printed. Note that this is the same method you would use to change the regression type for your best-fitting line! You can change the color of the line if you wish, but don't waste a lot of time "decorating" your graph. Click OK to accept the changes.

23. Sometimes we wish to extrapolate the line forward or backward, beyond the end points. In the "Format Trendline" dialog box, click the Options tab, and change "Forward Forecast" or "Backward Forecast" by the desired amount. Click OK to accept the changes.

    

24. To print your graph, click once to select it. Click the "Print Preview" button (it's the magnifying glass - see sample) to make sure you are printing the graph, and not your data (not necessary, since your lab report contains a data table). You can see how it will print out; if there's something you wish to change, click the Close button. Your name – and your lab partners' – should appear on the graph; to add the names, click the Setup... button, then the Header/Footer tab. Next click the Custom Header... button, and type your names in the "Center Section" box. Click OK until you return to the print preview. When you are ready, click the Print button.

©	St. Lawrence University	Department of Physics

Revised: 25 Aug 2021

Canton, NY 13617