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 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.

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.

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

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.

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

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

Select Primary Axis and expand the Series options menu.

Select Series 2 Y Error Bars

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 (-)

Finally click on OK or press Enter and the error bars will appear on the 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

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