Saturday, May 10, 2014

Amylase lab: How to make an Excel graph

Jennifer, the Excel whiz student in BIOT312, demonstrated during class how she constructed a graph in Excel using her maltose data set from the amylase laboratory experiment.
1. This is a sample data set from absorbance measurements from various maltose standards.

2. To create a graph, select the maltose data in the table. Then go to Insert in the Excel menu and choose the Scatter plot (points only) as shown in the screenshot

3. The graph appears on the Excel sheet

4. Modify the graph title and delete the graph legend if desired.

5. To insert a trendline, right-click a data point and select the Add Trendline menu item.

6. Choose the desired options for the trendline. In this example: Linear trend, Set Intercept = 0, and Display Equation on chart.

7. This is the graph with the trendline. Drag the equation to a better location if desired.

8. The next task is to the use the trendline equation to determine the maltose level (mg) from absorbance measurements in the test samples. The test sample in the example below has an absorbance = 0.763.

9. Use the trendline equation to calculate the maltose level. You can use Excel to perform the calculation.

10. This example adjusts the resulting number to contain to two decimal places.

11. To add this data point to the trendline, right-click the trendline in the chart and choose Select Data in the menu.

12. Choose Edit in the pop-up window to add more data points to the graph

13. Extend the series of data point by changing the cell value in the Series X values and Series Y values. $A$7 in the X value and $B$7 in the Y value extends the data set to include the test sample.

14. Result: The graph has the new data point included.


  1. Everything seemed so far so good...up until step 9...cannot get the result of that formula ??? my version is much older and its mac...this may be the reason...HELP!

  2. Reyes, I figured out your problem. It's "Set Intercept Trendline", not set trendline.