Bio1A03 Excel Tutorial 2 PDF

Preview

Summary

excel guide...

Description

Biology 1A03 - Excel Tutorial #2 for Office 365 How to Create a Standard Curve using Excel Microsoft Excel is free to download for students through the McMaster Microsoft Office 365 Portal. You can login and download Excel here: https://office365.mcmaster.ca. We recommend that you download and install Excel on to your computer for the best experience and access to more functions. The following tutorial will teach you how to create a standard curve using this version of Excel. Recall that to create a standard curve, we measure the absorbance of solutions with known concentrations. Once we graph that data, we can use the equation of the resulting line of best fit to determine the unknown concentrations of solutions. We can measure the absorbance in a spectrophotometer and enter this value into the equation of the line to get the concentration of the solution. Standard curve values might look something like the sample data in the following table: Table 1. Sample practice data of the absorbance values as measured for an amylase experiment standard curve using a spectrophotometer.

Tube

Amylase concentration (mg/ml)

Absorbance (620 nm)

1

0

0.00

2

5

0.25

3

10

0.43

4

15

0.66

5

20

0.87

6

25

1.04

These instructions are provided using sample data from another experiment as an example. For this exercise, practice creating a standard curve with the sample data in the amylase table above. Afterwards, use the methylene blue absorbance data that you collected in Table 1B-2 to complete your own data analysis and generate a standard curve for both Trial 1 and Trial 2. For this data analysis exercise, methylene blue concentration should be plotted on the X-axis since it is the independent variable. Absorbance is dependent upon the concentration of methylene blue in the solution, so it is plotted on the Y-axis.

CREATING A SCATTER PLOT GRAPH 1. Enter your data into an Excel spreadsheet. Highlight your data and then choose “Insert”, then “X Y (Scatter)”, and then "Scatter" (this is the first scatter plot option). A scatter plot of your data should then appear.

Figure 1. Creating a scatter plot graph.

FORMATTING A SCATTER PLOT GRAPH 2. Create a title for your graph. For this exercise, you can choose to include a title above your graph or below in a figure caption. To add a title below your graph, it is easiest to do it directly in word once your graph is complete. If a default title appears, you can click on it to type in a new appropriate title. Alternatively, if no title appears, you can click on the chart and then select the "Chart Design" tab, then select "Add Chart Element", then "Chart Title", and then "Above Chart". 3. Use the same pathway above to add X and Y-axis title to the graph. (Chart Design -> Add Chart Element -> Axis Titles -> Vertical/Horizontal Axis Title). Click on the title on the graph to edit it and type in a new appropriate title, including units where necessary. 4. Delete the series legend if one appears. 5. Click on the vertical axis gridlines (the grey lines on the figure). Delete the lines using the delete (backspace) key on your keyboard. Alternatively, right click on the lines and select "Format gridlines" and then select "No line". 6. Do the same to remove the horizontal grid lines if present.

Figure 2. Adding an appropriate chart title.

Figure 3. Adding appropriate x and y-axis titles.

Figure 4. Removing axis grid lines (step 1).

Figure 5. Removing axis grid lines (step 2).

10. The data symbol used for publication and reproduction is typically a black circle, but this may not be the default in excel. Click on a data point. This should select all points on the graph. Right click and select “Format Data Series”. If the option is format data point, you have selected only one point and not the whole data series. 11. Select "Marker" instead of Line. Under “Marker Options”, you can change the type to a circle. 12. Under "Fill", you can change the colour of the circle to black. Your data points should now all be black circles. 13. Under "Line" you can change the outline of the circle to black or remove the line altogether.

Figure 6. Selecting your data points to format.

Figure 7. Formatting your data points.

FORMATTING THE AXES ON A SCATTER PLOT In this example, the data on the x-axis in the sample graph does not exceed 25. The Xaxis on the sample graph should not far exceed 25 either. We can set this up by formatting the X-axis. 12. Right click on the x-axis and select “Format Axis”. Change the horizontal axis scale maximum value to an appropriate value for your data set. In this example, the horizontal maximum value is 25. Set the maximum value to 25 or a value just above (particularly when using decimal points). 13. Adjust the major unit to an appropriate scale. In this case, 5 is appropriate. Choose a scale so that values are frequent enough to read the graph, but not too frequent that the numbers are overlapping and you can't read them. 14. Set the major tick marks to "Outside". (You can also adjust the minor units and add tick marks where appropriate.) 15. Repeat steps 12-14 for the y-axis if needed.

Figure 8. Selecting the x-axis scale to format.

Figure 9. Formatting the x-axis scale.

ADDING A LINE OF BEST FIT AND EQUATION OF THE LINE AND R2 VALUE TO A SCATTER PLOT You will now need to add a line of best fit on the graph in order to use the standard curve. The problem with simply joining the points is that measurement error is not compensated for (joining the points would not necessarily result in a straight line). The best line to fit our data is a "least squares fit" or regression line. Excel will fit a line of best fit to our data points with a line that minimizes the deviation between the predicted value at each point and its measured value. There are several ways of doing this; the simplest is to add a “trendline”. The output of this method will give us the slope of the line (m) and its y-intercept (b) from the formula y = mx+b. It will also return a value for R², the coefficient of determination. This value will tell us how well the data is predicted by the line. A line is seen to be a good fit if the value of R² > 0.95. 16. Select the graph by clicking on it. Click on the "Chart Design" tab and select "Add Chart Element", followed by "Trendline" and then "Linear". 17. Right click on the trendline and select "Format Trendline". Check off the boxes next to "Display equation on chart" and "Display R-squared value on chart" to add the equation of the trendline and the R2 values, respectively. 18. You should move the block containing the formula and R2 coefficient off the body of the graph. You can do this by clicking on the equation to select its box and, holding down the mouse key, drag it to a new location, under the title for example. Excel has now drawn the trend line or regression line on your scatter plot and the formula which describes the line together with the R² coefficient is displayed on the graph. Your graph should now be complete! If you have the absorbance value of any “unknown” concentration of the same solution (e.g. methylene blue in your case), and it is within the range of concentrations of this linear line of best fit, you can use the formula of the line of best fit to determine the concentration of your “unknown”. This would be done by inserting the absorbance value of the unknown into the equation of the line (this is the y- value), and then solving for the “x” value (this is the concentration of the solution.

Figure 10. Adding a linear trendline.

Figure 11. Formatting your trendline.

Figure 12. Displaying the R2 value and equation of the trendline....