The Open Door Web Site

Going Further with Graphs Index

ICT in Biology Index

IB Biology Homepage

 Note about this Tutorial This tutorial has been updated. It was originally published using Excel 2003. The new images are screenshots from Excel 2013. There may be very slight differences for those of you using Excel 2016 but, essentially, the layout is much the same as Excel 2013. Download the Excel Data File used for this tutorial to practice with TrendLineFile.xlsx

 Preparing the Graph The graph produced needs to be distributed along the horizontal axis. Use the Format Axis option to change the bounds of the horizontal axis. Place 20 as the minimum and 70 as the maximum on the horizontal axis. Finally label the axes correctly and give your graph a full title.

ICT IN BIOLOGY

Custom Search

Using MS Excel 2013 : Going Further with Graphs

Trend Lines

Graphs are not only a way of presenting data, they can also be used a way of analysing data.

The simplest way is by using a trend line or line of best fit.

Enter your data on a spread sheet and select the variables that you want to plot.

Then select the Insert Menu then Chart Tools. Select Scatter (XY) or Bubble Chart.

See to the left for how to prepare the graph

The graph reveals that one of the points is an outlier. Its position is significantly different from the main trend in the data.

This data point can be removed from the graph by editing the data in the spread sheet.

This results in a modification of the graph

To add a trend line to this graph, use the trend line function of MSExcel. First click left on one of the data points. This will cause the whole series of points to be highlighted. From the menu select Add Trend Line

A series of trend line options will be presented. Select the one which looks the most appropriate and fits the data best. This may take a few trials.

In this example the trend line permits us to do two things:

• First we can see how well the points fit the line. The scatter of the points either side of the line shows how much variation there is in the data. This is a measure of the uncertainty.

• Second we can use the curve to estimate the optimum temperature of this enzyme. The fact that one of points (40°C) had to be excluded as an outlier will mean this estimate is less sure as it is near a critical part of the curve.

Next: Calculating Slopes

The Open Door Web Site is non-profit making. Your donations help towards the cost of maintaining this free service on-line.

Donate to the Open Door Web Site using PayPal

 © Paul Billiet 2018Any questions or problems regarding this site should be addressed to the webmaster Hosted By