ODWS icon

The Open Door Web Site
HOMEPAGE BIOLOGY HOMEPAGE CHEMISTRY PHYSICS ELECTRONICS HISTORY HISTORY OF SCI & TECH MATH STUDIES LEARNING FRENCH STUDY GUIDE  PHOTO GALLERY
IB BIOLOGY HOMEPAGE TOPIC CHAPTERS SCIENTIFIC INVESTIGATIONS DRAWING IN BIOLOGY DRAWING TABLES ERROR ANALYSIS IN BIOLOGY ALL ABOUT GRAPHS STATISTICS SCIENTIFIC POSTERS MOLECULAR IMAGES
WS

 

Going Further with Graphs Index

Calculating Slopes
Plotting Derived Data
Error Bars Showing Precision
Error Bars Showing Standard Deviations
Entering Error Bars on a Scatter Plot

ICT in Biology Index

Using MS Excel as a Calculator
Using MS Excel as a Database

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

Excel icon

TrendLineFile.xlsx

 

 

Preparing the Graph

The graph produced needs to be distributed along the horizontal axis.

Graph01

Use the Format Axis option to change the bounds of the horizontal axis.

Graph02

Place 20 as the minimum and 70 as the maximum on the horizontal axis.

Graph03

Finally label the axes correctly and give your graph a full title.

Graph04

 

 

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.

Excel01

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.

Graph

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

Excel03

This results in a modification of the graph

Excel04

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

Excel06

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.

Graph07

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.

Graph08

 

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

SITE MAP
WHAT'S NEW?
ABOUT

PRIVACY

COPYRIGHT

SPONSORSHIP

DONATIONS

ADVERTISING

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

Hosted By
Web Hosting by HostCentric


SiteLock