simple_graphing_with_excel_tutorial__2020-2021_

Simple Graphing with Excel (Science 9 Pathways) This tutorial will teach you how to make a basic bar graph. This will also serve as an introduction to Excel so you are more familiar with it in the future. Answer the following questions on a separate sheet of paper with your partner. Section 1: Making a Bar Graph Suppose you are investigating the question: “How does the sex of a rabbit affect its body length?” 1) What is the independent variable? 2) What is the dependent variable? After the experiment, you collect the following data in your lab notebook: Rabbit Identity Body Length (cm) Rabbit Identity Body Length (cm) Male #1 15.6 Female #1 16.9 Male #2 18.2 Female #2 15.5 Male #3 20.5 Female #3 14.8 Male #4 13 Female #4 16 Male #5 24.5 Female #5 18.3 Male #6 22.3 Female #6 19.5 3) Look at the data and make an educated guess: do females or males have the longer bodies on average? 4) Calculate the average body length for each of the sexes, by adding up the values of all 6 trials and dividing by 6. You may use a calculator. Part 1a: Making a Data Table Now, we are going to put our data in Excel. Replicate the table in Excel as shown below. Notice that we have our different experimental or treatment conditions (i.e. the different ‘levels’ of the independent variable) along the top. Notice also that the individual measurements from the different trials are listed underneath. (In this experiment there were basically 6 trials because there were 6 measurements for each of the sexes.)

Feel free to play around with the different font and table formatting options in Excel to make your table look nice. You can try: - Changing the font - Changing the ‘fill’ colour of your cells - Making rows/columns bigger or smaller Part 1b: Making a Data Table with Averages Now, make another simpler table, like the one below. Put the averages that you calculated in #4, in the appropriate spaces. Part 2: Making a Bar Graph Finally we are going to make our bar graph! Select the four adjacent cells containing the male/female labels and the averages (in your graph from part 1b) and click “insert  chart  column. Experiment with the other chart types (still only selecting those four cells). Click on chart design and experiment with “add chart element” and “quick layout” to do the following until your graph looks similar to the one shown below. - Add a title to your chart. - Label your x axis with the name of your independent variable. - Label your y axis with the dependent variable (and units in brackets). - Remove the horizontal gridlines from the graph. - Double-click on the numbers on the y-axis and change the “Bounds” so that the Minimum is equal to “0” instead of the automatic value of 15.5. - Feel free to play around with fonts and colours as well. Calculating Averages with Excel In an empty cell, try typing: =average( then selecting a group of cells and pressing enter. If you select all 6 of the male body lengths, this should give you the answer you had in #4! Then you can repeat Male Female Average Body Length (cm) Answer from #4 Answer from #4

Part 2: Adding Error Bars Click on your graph, and then Add Chart Element  Error Bars  More Error Bars Options. Do not click on “Standard Deviation!” This will give you the wrong size bars. On the sidebar, for Error Amount, click Custom  Specify Value. Erase the positive error value. Then, click the white box, and select both standard deviation values you calculated earlier. Repeat for negative error value. Click “OK”. Your graph should now look somewhat like this: If your error bars are the same height for male and female, ask Ms. Au for help. A scientist would then analyze the error bars and see whether the difference in their averages was ‘significantly different’.

See “Results Elaboration” document for more information on how to report these values.