Online Lab Submission
Graphing Linear Relationships
Creating and interpreting graphs is a skill that must be included and cultivated in the general chemistry curriculum. Since there isn't an expectation that freshmen students possess a working knowledge of Excel, the general chemistry laboratory is the perfect place for students to use Excel to display their lab data via a "guided tour" (described under the More Information link). The Chem21Labs program displays a graph of the student's data as an embedded part of the lab report on the webpage . . . . students can "compare" their Excel graph to the Chem21Labs graph as an initial check that they followed the directions provided by their "guide." Next, the student enters the information they collected during the "tour" . . . . slope, y-intercept, R2, standard deviation . . . . into the Chem21Labs lab report webpage. The student receives immediate feedback on the correctness of their values and either continues with the lab submission process or takes a second "guided tour" through Excel to remedy any errors.
In addition to the default plot of y vs. x (where the y vs. x button is enabled), two optional buttons ( 1/y vs. x and ln(y) vs. x ) can be clicked to plot the data as indicated on the button. These relationships are helpful in understanding PV = nRT relationships as well as whether a reaction follows 1st or 2nd order kinetics. Also, as in the Beer's Law plot shown below, the lab instructor can specify if the trendline must cross the origin.
Finally, the More Information link (click link below) contains useful information about graphing lab data with Excel. This expandable section of text can be collapsed using the Hide Information link (accessible when the section is expanded).
Graphing with Excel
- Open Excel by clicking on Start, All Programs, Microsoft Office, Microsoft Office Excel
Place your "x" values (the following [show=] syntax is used in Chem21Labs to "redisplay" a value that resides in the database . . . . the student will not see the [show=] text, they will see their actual lab data) [show=11], [show=21], [show=31], [show=41], [show=51]) in cells A1 – A5 and your "y" values ([show=12], [show=22], [show=32], [show=42], [show=52]) in cells B1 – B5. To enter values into cells, click the cell and type the number then press ENTER – enter only numbers in the spreadsheet. You can also copy / paste the values (see table to the right) by clicking on [show=11] and dragging / highlighting until the cursor is on [show=52] and all values are highlighted. Copy (Ctrl + C) the values to your clipboard, click in Cell A1 in Excel, and Paste (Ctrl + V) the values into Excel.
- Click on the INSERT tab and then click on the SCATTER chart in the CHARTS area. Click on the SCATTER WITH ONLY MARKERS icon.
- This will place a chart on the page. To make sure the chart is graphing your data correctly, right-click in the white area on the right side of the chart and click the Select Data link.
- Click Series 1 to highlight it and then click the Edit button. Make sure the X values are '=Sheet1!$A$1:$A$5' and the Y values are '=Sheet1!$B$1:$B$5'.
- Click OK and then click OK again.
- Click the graph to select it and then click the '+' icon that appears to the right. Check the Trendline option.
- To place the Equation and R2 value on the graph, mouse over the space to the right of the word 'Trendline' and click the arrow that appears. Click MORE TRENDLINE OPTIONS and then click Display Equation on Chart and Display R2 Value.
- Your graph will be displayed along with the equation for the 'best-fit' line through the 5 data points. Report the slope of the line below. If the slope in the Excel equation doesn't have at least 3 Sig Figs, click in an empty Excel cell and enter the following starting with the equal sign: =slope(B1:B5,A1:A5). Press the Enter Key and the slope will appear.
- Add a Chart Title and Axes Titles (click the chart, highlight the Design tab, click the Add Chart Element button).
To see a video on how to create a graph, determine the slope and y-intercept of the best-fit line, format the title and axes, save it as an image file, and upload it to Chem21Labs, click Graphing with Google Sheets or Graphing with Excel.