BIO270 Laboratory: Using Excel to Present Data
Often the easiest and most informative way to present the data you recorded during lab is in the form of a graph. The following handout describes how to create different types of graphs in MS Excel and how to make sure the relevant information appears on the graph.
A. Mean and Standard Deviation
When recording data during a lab, you will often perform multiple trials in order to reduce the effect of error in procedure or measurement. Finding the mean and standard deviation of the data will help you know how accurate and precise your measurements are.
1.Enter the data from each trial into one column, creating a table of your data. Include a row for the means and standard deviation of each trail. For example:
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
2. Note that each cell in Excel is on a grid. The upper, left cell on the grid is A1. If the above table starts at A1, it would extend over to column H and down to row 4. If you want to calculate the mean for a set of data, in this example the 5 trials at one minute, you can use the function option (Fx) in Excel to help you do this. Put your cursor in the cell that should contain the mean for the first set of data (cell G2 in the example) and click on the Fx button on the menu bar. In the left hand column choose "statistical" and in the right hand column choose "average". A box will pop up that asks you to enter the numbers that you want to average. Highlight the numbers you would like to average together and hit enter.
3. In order to calculate standard deviation of a data set, use the following commands. Place your cursor in the cell where you would like the standard deviation value to be (cell H2 in the above example). Click on the Fx button on the menu bar, choose "statistical" on the left hand side and "STDEV" on the right hand side. Again, a box will pop up and you should highlight the cells that you would like to calculate standard deviation for. When there cells are highlighted, hit enter.
4. Once a cell contains a formula, you can extend it to other cells by highlighting the cell with the formula and the cells to which you would like to extend the formula. In the example for the mean, the formula is already in G2. You would highlight G2 to G4. Go to Edit, choose Fill from the dropdown menu and select the direction in which you want to extend the formula (in the example, it would be Down). The highlighted cells will automatically calculate the mean for their respective data sets. You can do this for any formula in Excel. Or, in the cell with the formula, there is a tiny black box in the lower right hand corner. You can click on this box and drag the formula down or across depending on where you would also like to use this formula.
5. If you would like to transfer the table you created in Excel to Word, just highlight the cells in the table, select copy, and paste it in the Word document. Remember to save your work.
1. Highlight the data that you want to plot Excel will take the first column or row and place it on the X (horizontal) axis, so make sure that the first column or row contains the independent variable. The Y (vertical) axis will contain the dependent variable, which will usually be the mean of your trials. To highlight non-adjacent rows or columns, hold the CTRL button down while you highlight the data that you want to include in the graph.
2. Click on the Chart Wizard button in the toolbar (it looks like a graph with red, yellow and blue bars) or you can click on INSERT and CHART on the drop down menu.
3. Excel will open a dialogue box and ask you to choose the chart type. Select the XY (scatter) plot on the left and click on the picture with the random, unconnected dots on the right. Click Next.
4. The next dialogue box should contain a graph of your data. Make sure that each axis has the correct data. Remember that the independent variable should be on the X axis and the dependent variable on the Y axis. Click Next.
5. Now Excel will ask you to title the graph and label the axes. The title of your graph should be descriptive and indicative of what the figure is showing the reader. Each axis needs a label of what it represents (time, distance, volume, etc.) and the units of measurement. Include a legend only if needed to distinguish different experimental groups or trials.
Notice the other tabs in this dialogue box (Axes, Gridlines, Legend, Data Labels). You may want to look under Gridlines and decide whether inserting more (or fewer) gridlines will help the clarity of your graph. If you only have one dependent variable, you can go to Legend and uncheck the Show Legend box. When you have adjusted the settings for your graph, click on Next. Use these tabs to eliminate shading, unnecessary gridlines, borders, etc.
6. The final dialogue box will ask you if you would like the graph inserted into your spreadsheet or on a new spreadsheet. If you place the graph on a new spreadsheet, you can print it out as a whole page. If you insert it into the current spreadsheet, you can cut and paste the graph into a Word document as part of your lab write up. Click Finish when you are done.
7. Optional if requested by your instructor only: Once your graph is finished, you can go to the CHART drop down menu and select ADD TRENDLINE. Excel will calculate a best fit line for the data points. Do this only if instructed by your fearless leader.
8. You can now make changes to your graph by clicking on any part of the graph that you want to change. If you have more than one dependent variable and kept the legend as part of your graph, remember to label the legend.
9. Save your work. It would be a good idea to save the spreadsheet with the data as well.
1. Follow steps 1-2 for creating a scatter plot.
2. In the Chart Type dialogue box, choose Line graph from the list on the right and the Line or Line with markers displayed picture on the right. Click Next.
3. The next dialogue box will show you the graph. Make sure your data points are represented accurately. If you have only one dependent variable, click on the Series tab, select Series 2 and remove it.
4. <![endif]>Go through steps 5-9 above. Do not insert a trendline on a line graph!
1. Follow steps 1-2 for creating a scatter plot.
2. In the Chart Type dialogue box, choose Column. Note that in Excel, a bar graph has horizontal bars. For data collected from Bio 270, you will use a column graph. Choose the chart sub-type that will present the data simply and accurately. This should not be a 3 dimensional graph, no matter how pretty it looks.
3. The next dialogue box will show you the graph. Make sure your data points are represented accurately. If you have only one dependent variable, click on the Series tab, select Series 2 and remove it.
4. Go through steps 5-9 above. Do not insert a trendline on a column graph.
In addition to plotting the mean, we can represent the standard deviation on the graph by inserting error bars. The error bars will extend one standard deviation above and below the point representing the mean on the graph.
1. Right click on one of the data points on the graph and select Format Data Series.
2. The Format Data Series dialogue box will appear. Go to the Y Error Bars tab. You will usually display both plus and minus error bars. Choose the custom error bar option from the bottom of the dialog box. When you choose custom, there are 2 boxes you need to fill in, one for the "+" value and one for the "-" value. In the "+" column, highlight your column of standard deviations on the excel data sheet. For the "-" values, highlight the same standard deviation values from the excel sheet. This will place error bars that extend one standard deviation from the mean in both directions.
3. Save your work.