Basic Graphing and Linear Regression in Microsoft Excel:

Note: This guide was written for the PC version of MS-Excel 97/Windows 95/Windows NT but other versions should work similarly. The screenshots were taken with a monitor resolution of 1024x768 - they will look best on a monitor with this or higher resolution.

1. Start Excel. On some computers, there will be a shortcut to Excel on the computer desktop and you can simply click this icon. In other cases you will have to click on START, move the pointer to the PROGRAMS menu, find EXCEL and click to start the program.

   Once started, you will see the toolbar with the spreadsheet below it. Picture of the spreadsheet after this step.

2. Enter your data. In the first column (column A) type your first X value and press ENTER. The highlighted box should move from cell A1 (column A, row 1) to A2 (column A, row 2). Type your second X value and press ENTER. Continue until you have entered the complete set of X values. Move the cursor over cell B1 (column B, row 1) and click. In the second column (column B) type your Y values. Picture of the spreadsheet after this step.

3. Graph you data. Place the cursor over cell A1 again and click to highlight this cell. Now, hold down the left mouse button and drag the cursor over the entire data set (in this case, to cell B10), then release the cursor. The data should appear inverted (white numbers on a black background) to show they have been selected.

   Move the cursor to the menu bar and choose CHART from the INSERT menu. The chart wizard (designed to guide you through the graphing process) should open. In the first step choose a XY (Scatter) graph type without connected dots. Picture of the spreadsheet after this step. Press the next button.

   In the next panel, a preview of the graph will be shown. Click on the SERIES tab and in the box labeled NAME, type a label for your data. In this case, I wrote Zinc calibration data (Excel added the equal sign and quotation marks). Picture of the spreadsheet after this step. Click the next button.

   In the next panel you can customize the graph a little. At the very least, you should add a X and Y axis title by clicking the TITLE tab. The titles (including units) should be entered in the VALUE (X) AXIS and VALUE (Y) AXIS spaces. You can also change the chart title if you wish by typing in the CHART TITLE box. I will leave the auto-entered title. Picture of the spreadsheet after this step. Click the next button.

   In the last panel, you get to place the graph next to your data or on a separate "page" (worksheet). Choose to add it as a separate page by clicking AS NEW SHEET. Click the finish box. Your new graph will appear on the screen and a new tab will appear at the bottom left corner of the screen labeled CHART 1. Close the chart toolbar that appears. You can select your data by clicking SHEET 1 and you graph by clicking CHART 1. Picture of the spreadsheet after this step.

4. Print your graph (optional). Choose PRINT from the FILE menu. In the dialog box that appears, you will usually just click OK to print the graph. Sometimes you may have to change the printer location or properties depending upon where you are printing your graph.

5. Add a linear regression ("best fit") curve if desired. While in the graph (chart 1) window, choose ADD TRENDLINE from the CHART menu. Under the TYPE tab, choose a linear curve. Picture of the spreadsheet after this step.

   Click on the OPTIONS tab and check the boxes to display the equation and the R² value. You can also enter a name for the fit (in this example, I wrote Linear Fit), and choose to force the fit through the origin (if this is a valid data point - usually you should not force the data through the origin). Picture of the spreadsheet after this step. Click OK when done.

   The linear curve will appear on your graph, together with the equation and correlation coefficient. In some cases, the number of digits displayed for the intercept (in this case, 2.9333) or slope (in this case, 0.7126) will be insufficient for accurate calculations based on this graph. This happens when very small numbers are generated for slope or intercept.

   In this example, we do have sufficient number of digits, but, if we wanted to change this we can select the equation by clicking once while the cursor is over the equation. A box should appear around the text. Picture of the spreadsheet after this step.

   Click the right mouse button while the cursor is over the box and select FORMAT DATA LABELS from the short menu that appears. In the this dialog box, click on the NUMBERS tab, select SCIENTIFIC numbers and change the NUMBER OF DECIMAL PLACES to 3 or 4. By clicking on other tabs you can change other formatting parameters for this label. Picture of the spreadsheet after this step. Click OK when done.

   The equation for the fit will now be changed to scientific notation. You can drag the equation around your graph by holding down the left mouse button while the cursor is over the equation and moving it. Release the mouse button when you’re happy with the location. Add a title to your graph (if you haven't done this already) by clicking on the title area and typing (your title might include your name, section number and date). Picture of the spreadsheet after this step.

6. Print the graph as described above.

This page created 02.07.00 by Simon J. Garrett.