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

Trend Lines
Calculating Slopes
Plotting Derived Data
Error Bars Showing Precision
Error Bars Showing Standard Deviations

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

EBSPFile.xlsx

 

 

 

 

 

ICT IN BIOLOGY

Custom Search

Using MS Excel 2013 : Going Further with Graphs

Entering Error Bars on a Scatter Plot

The same method (see Error Bars Showing Standard Deviations) can be used to put error bars on data with trend lines. This time however the menu gives you the option of x as well as y error bars.

In this example students were investigating the water potential of pieces of sweet potato tissue cut to a precise length (5 cm) and soaked in a range of sucrose solutions for 24 hours.

First the data is entered into the spread sheet. The spread sheet is then used to calculate the change in length of the tissue samples by setting up a second table.

Table01

By entering the equation =B4-5 the cell will calculate the difference between the initial length (5cm) and the final length. Press Enter for the result. The same calculation can be repeated down the column by holding down the left mouse button and pulling down on the bottom right hand corner of the cell.

Table02

Then repeated for all the data by pulling this highlighted area across the table in the same way

Table03

Finally the mean change in length and the standard deviation of the samples can be calculated too. Note : it is not necessary to calculate the percentage change in length as the tissues were all the same lengths to start with.

MeanStDeV

A scatter plot graph is drawn of the mean change in length against sucrose concentration and a trend line is added.

Graph01

Double click on the data point to open the Chart Elements menu.

Graph02

Select Primary Axis and expand the Series options menu.

Table03

Select Series 2 Y Error Bars

Table04

 

Select the data that is to be used for the upper error bar. Click on the upper bar (+) of the box in which you have to select the cells containing the data for the error bars (in this case the standard deviation).

Left click on the first cell and hold the button down whilst you run along the line for standard deviations. You will see the cell coordinates appear in the box.

Repeat, using the same data, for the lower error bar (-)

Table05

Finally click on OK or press Enter and the error bars will appear on the graph.

Graph

 

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