BIO111: Antibacterial Foods – Using the Scientific Method and Graphing with Microsoft Excel

Introduction
People tend to think that scientific research has some sort of magical and overly complicated
methodology that must be used in order to “discover” new things. In reality, scientists are
trained to observe phenomena around them very closely, which may elicit many unanswered
questions in their minds. The logical way to answer these questions is to experiment, collect
data, and then to resolve the question or find a new question that needs to be investigated
further. This way of doing research is known as “The Scientific Method” and follows a series of
recognized steps:

1. An observation or set of observations is made.

2. A question is elicited.
3. An answer is posed in the form of a hypothesis (an idea or tentative explanation for
something).
4. The hypothesis gives rise to predictions. In making predictions, scientists are using
deductive logic. They are inferring a set of outcomes from a general principle using “if…
then” logic.
5. The prediction(s) can be tested by experiments or additional observations.

When scientists take measurements and/or conduct experiments they obtain data and results.
These data must be presented in ways that aid in their interpretation, for example as graphs,
diagrams and tables. This lab will serve as an introduction to using the scientific method to
conduct research since it is the method followed in most of your labs this semester. You will
gain practice in making an observation, formulating a hypothesis, making a prediction, testing
your prediction, collecting data, presenting data and analyzing data.

In this case you will analyze your data using a statistical analysis of the mean and standard
deviation. The mean, also called the “average” is simply the sum of all the data point values
divided by the number of data points taken. So the mean of 17mm, 15mm, 12mm, 19mm and
10mm would be (17+15+12+19+10)/5 = (73)/5 = 14.6. The standard deviation of a data set is a
measure of the variance of each data point around the mean. Thus, if there is a large variation
of the numbers, the standard deviation will reflect that by being large; if the data points are
tightly grouped, standard deviation reflects that with a small number. Excel programs will
calculate the standard deviation for you. For our example, the standard deviation is ± 3.65.
These statistical measures can be compared between groups to see if we have confidence, or
statistical significance, that our data represents the average and normal variations of “ideal”
data.

Since the presentation of data in the form of graphs is so important this semester, the pre-lab
and post-lab activities will give you an opportunity to practice graphing. In order to successfully
complete the pre- and post-labs, as well as to confidently manage data presentation in your Bio
111 labs this semester, you will need to become thoroughly familiar with pie charts, bar graphs,
scatter plots and line graphs.

1/15/2020 3:00 PM 1
Pre-Lab activity: Practice with Graphs

Learning Objectives
 Choose the best graph to represent a data set
 Become familiar with constructing a graph

Make sure that you are familiar with the following types of graphs (you may need to do some
internet research):
 Pie Chart
 Bar Graph
 Scatter Plot
 Line Graph

1. For each of the data sets described, give the best choice of a graph to display the data (from
the choices listed above). You do not have to plot graphs but think of which type of graph
you would use most effectively to communicate the data to others.
a. Hourly precipitation levels in a tropical rainforest recorded over a 24-hour time
period.

b. One hundred ground squirrels in a national forest were captured and weighed
and then measured for running speed to test the hypothesis that squirrel body
weight affects running speed.

c. Samples of water were collected from a local pond and numbers of different
kinds of protists* were recorded for comparison.

d. Students at Front Range Community College were surveyed to determine the

percentage of students who commuted to campus by private vehicle, carpool,
public bus, bicycle or walking.

e. The relationship between the average number of eggs laid by chickens appears
to decrease as the total number of chickens in the chicken coop increases.

* Protists are a diverse group consisting mostly of microscopic unicellular (one-celled)

organisms. Many different species of protists are commonly found in pond water.

1/15/2020 3:00 PM 2
2. Construct the most appropriate graph using the raw data displayed in Table 1 below.
Refer to the checklist at the bottom of the page.
Table 1: Paramecium* population levels
Time Number of
(hours) Paramecium in
a. Which 30 minute (such as 1 to 1.5 hours) test tube
interval has the highest rate of reproductive 0 10
growth? (Hint: rate is calculated as change 0.5 12
over a period of time.) 1 15
1.5 21
2 29
2.5 42
b. Explain one possible reason why the 3 57
population of Paramecium showed a 3.5 76
dramatic drop in numbers after 5 hours. 4 85
4.5 89
5 90
5.5 70
6 49

* A Paramecium is a single-celled protist that lives in fresh water. For this experiment, 10
Paramecia were placed in a test tube with water. Reproduction is generally asexual by a
process called binary fission – simple splitting of one unicellular organism into two organisms.
The number of live individuals was counted at 30 minute intervals.

Checklist for constructing a graph:

 Select the most appropriate graph (line, scatter or bar)
 Identify the independent variable (for placement on X axis) and dependent
variable (Y axis)
 Determine the scale for the X axis
 Determine the scale for the Y axis
 Label the X and Y axes with units of measurement (always metric) in
parentheses if necessary
 Number the major ticks
 Plot the points on the graph – connect or not?
 Write a title at the top of your graph
 Write a legend below your graph

1/15/2020 3:00 PM 3
Part 1: Lab Activity: Antibacterial Foods
Learning Objectives
 Begin using the steps of the scientific method

Introduction
Many foods have a long history as part of the culinary traditions of various ethnic cultures,
especially those cultures native to areas with very warm climates. Several studies have been
published showing at least some level of antibacterial activity among various herbs and spices.
All of these plants produce secondary metabolic compounds, such as allicin in garlic and
capsaicin in jalapeno (as well as other hot peppers), that, in the wild, act as defense
mechanisms against herbivores.

In today’s laboratory you will be using the Scientific Method to investigate the antibacterial
properties of several common herbs and spices, and perform a quick analysis to see if the anti-
bacterial properties of any of the ingredients are significantly different from the positive
controls.

Day One
Materials
 sharpie
 2 agar plates
 sterile swabs
 bacteria (growing in nutrient broth) onion garlic
 food sample set
 filter paper discs
winter-
 ruler Antibiotic green
 forceps
 antibiotic discs (positive control)
 plastic disposable transfer pipettes
 disinfectant

Hypothesis Development
Develop a hypothesis regarding the antibacterial properties of lemon
oregano
the foods (look at Figure 1 for the specific food samples): grass

filter Grape
disk fruit
Procedure
Preparation of bacterial lawns (refers to Figure 1 and 2).
1. On the BOTTOM half of each agar plate, use a sharpie to
divide the plate into 4 equal sections and label the
sections as shown Figure 1.
Figure 1. Labeling your plates.

1/15/2020 3:00 PM 4
 2. Dip a sterile swab in the E. coli bacterial suspension and thoroughly saturate the swab.
Remove the excess liquid by firmly rotating the swab against the inside wall of the test
tube above the bacterial suspension.
3. Swab the surface of the agar plate labeled E. coli, covering the entire surface to produce
a bacterial “lawn” (full coverage of the plate). See Figure 2.

4. Allow the culture to be

absorbed by the agar by
leaving the lid of the agar
plate ajar for 5 minutes. (If
the plate is too wet, the
foods will diffuse in the
liquid and give an
inaccurate sized zone of
inhibition).

Figure 2. Streaking
a bacterial lawn.

Preparation of food ingredients for testing

1. Food samples have been prepared into sample solutions at labeled stations.
2. At each station there are filter paper wafers and forceps. Use the forceps to dip a wafer
into the food sample, then wipe off excess sample on the side of the beaker. Too much
sample will run across the agar plate.
3. Once you have wiped your disc, place it in the center of the appropriately labeled quadrant
of the plate.
4. Using forceps, obtain an antibiotic disc from the dispenser and place the disc in the middle
of the bacterial lawn in the section labeled “antibiotic” on the plate. This is the positive
control. On the section labeled “filter disc” on the other plate, place a dry disc of filter
paper. This is the negative control.
a. What is the purpose of positive and negative controls?
5. Write your Lab Group “name” on both plates. Give the plates to your instructor, who will
tape the whole class’s plates together and incubate them at 37 oC for 48 hours.

1/15/2020 3:00 PM 5
Clean up
1. Slide used swabs into the paper they were removed from and place in red biohazard
bag.
2. Gloves and any materials that did not touch the bacterial culture or lawn can go in the
trash.
3. Return all other items to the appropriate location (where you found them).
4. Wash your bench tops with the cleaner provided.

Day 2
Materials
 Plates from Day 1
 Rulers

Procedure
1. Measure the “zone of inhibition”: measure, in mm, the
diameter of the region around each sample where there is NO
bacterial growth (see Figure 3).
2. Enter the data in Table 1 below. Figure 3. Measuring zones of
3. Each group should then enter their data into a class table, inhibition on a bacterial lawn.
either on the board or on an Excel spreadsheet set up by your
instructor.
4. Collect the class data before leaving class so that you can calculate the means and standard
deviations for each treatment group and for the positive control (Table 2, see instructions
below).

Clean up
1. Place agar plates in the red biohazard bags.
2. Return rulers to the cart.
3. Wash your bench tops with the cleaner provided.

1/15/2020 3:00 PM 6
Table 1: Inhibitory Effects of Various Food Items on the Growth of E. coli. This will be class
data.
Food Diameter of zone of Diameter of zone of
inhibition (mm): Group Data inhibition (mm): Class Data
1. Onion

2. Garlic

3. Wintergreen oil

4. Antibiotic disc
(positive control)
1. Lemongrass oil

2. Oregano oil

3. Grapefruit oil

4. Filter disc (negative

control)

Analysis
Instructions for calculating means and standard deviations, and for graphing your data, in Excel
(more information on graphing in different versions of Excel can be found in the appendices to
the lab manual):
1. Turn on the computer. If you are using an FRCC laptop:

a. Make sure that the wireless icon (on the upper right corner above the keyboard,
next to the volume/mute button) is blue or white (not yellow). If not press it once.
2. Log in to the computer – if you are using an FRCC computer:
a. Your user name is your S# with a capital S (ex: S12345678)
b. Your password begins with “Student”, then your birthdate (two-digit month (e.g. 10),
two-digit day (e.g. 02), four-digit year (e.g. 1980) – full example password is
Student10021980.
3. Check the laptop battery level (lower right hand panel). If it is less than half, return your
laptop and plug it in. Get a new computer and repeat steps 1-3.
4. Open Excel [click on the Start button in the lower left corner of the screen; All Programs;
Microsoft Office folder; Microsoft Excel].
5. In cell A1, type a heading for your treatments (Foods).
6. In cells A2-A9, type in the treatments and controls (onion, garlic, antibiotic, etc.).
7. In cell B1, type a heading for your data (Zone of Inhibition).
8. In cell B2-B9 type in the E. coli measurements from one lab group. In cells C2-C9 type in the
E. coli measurements for a second lab group. Continue until you have entered
measurements for all lab groups. If there are 6 lab groups, you should have data in rows 2-9
of columns B-G.

1/15/2020 3:00 PM 7
9. Click on cell H2. Type =AVERAGE(B2:G2) and hit enter. You should now have the average
(mean) for that treatment in cell H2.
10. Click on cell I2. Type in =STDEV(B2:G2) and hit enter. You should now have the standard
deviation for that treatment.
a. The standard deviation tells you how different the data are from the mean/average.
If the standard deviation is small, all of the data closely similar to the mean. If the
standard deviation is large, then the data vary a lot more.
11. Repeat steps 5 and 6 for rows 3-7 to calculate the average and standard deviation for each
of your treatments.
12. Record the means and standard deviations in Table 2. Report the standard deviations as +
the standard deviation (e.g., 10±2).

Table 2: Inhibitory Effects of Various Food Items on the Growth of E. coli. This will be class
data.
Food Mean + Standard Deviation of the diameters of
zones of inhibition (mm)
1. Onion

2. Garlic

3. Wintergreen oil

4. Antibiotic disc (positive

control)
1. Lemongrass oil

2. Oregano oil

3. Grapefruit oil

4. Filter disc (negative

control)

13. To graph the class mean data, select the data in column H.
14. Click on the Insert tab in the ribbon. Within the Chart option, select
“Column” (it is actually a bar graph). Click on the Column button to
reveal the chart sub-types. Choose the first option (2D Column).
15. You will now need to change your axis settings, add axis titles, and add a
figure legend (title) to your graph.
a. Click on a blank area of the graph to bring up the ‘Chart Tools’
header. Click on the Design tab. Click on the grey arrow on the bottom/right side of
the Chart Layout area. Different selections will drop down. Choose one with a Title
and both Axis Labels.
b. Click in the Title text box. Change the title to Effect of Foods on Bacterial Growth.

1/15/2020 3:00 PM 8
 c. Click in the axis text boxes. Change the independent variable to Treatment (food).
Change the dependent variable to Mean Diameter of Zone of Inhibition (mm).
d. Click the Legend, then delete it.
e. To change the settings or range on your horizontal axis, right click on the axis
numbers, then select Select Data. In the box that comes up, click Edit under
Horizontal (Category) Axis Labels. Click in the box that comes up and type or drag to
select the cells that have the names of your treatments.
16. Finally, add error bars.
a. Click on the blank space of your graph and then click the green plus sign to the
right of your graph to open the Chart Elements.
b. Click the box next to “Error Bars” and then click the black arrow to the right of “Error
Bars” to click More Options. The Format Error Bars panel will open on the right side
of your screen.
c. Click the right-hand icon at the top of this panel to get Error Bar Options.
d. At the bottom of this panel, select “Custom Amount” and click Specify Value.
e. Click in the box for the Positive Error Value and type or drag to select your standard
deviation values for your data (I2:19). Do the same in the Negative Error Value box.
17. If you are using a school laptop, log out and turn off the computer. Return the laptop to its
matching numbered tray on the cart and plug it in.
18. You will need to take your Excel file home to do additional graphing and printing. All files
left on a school computer are erased when the computer restarts.
a. Save the file on a thumb-drive OR
b. Email the file to yourself and your group members.
c. No group work, all students must complete their own graphs.

1/15/2020 3:00 PM 9
Part 2: Graphing with Microsoft Excel
(2016 version – see appendices for other versions)
Because the BIO 111 lab exercises during the semester will often require construction of a
graph, it is a good idea to become proficient in MS Excel or another data manipulation software
package.

Learning Objectives
 Gain experience with MS Excel
 Use your own and other’s data to graph and calculate Pearson’s coefficient

Procedure
1. Turn on the computer. If you are using an FRCC laptop:

a. Make sure that the wireless icon (on the upper right corner above the keyboard,
next to the volume/mute button) is blue or white (not yellow). If not press it once.
2. Log in to the computer – if you are using an FRCC computer:
a. Your user name is your S# with a capital S (ex: S12345678)
b. Your password is the word “Student” followed by two-digit month (e.g. 10), two-digit
day (e.g. 02), four-digit year (e.g. 1980) – full example password is Student10021980.
3. Check the laptop battery level (lower right hand panel). If it is less than half, return your
laptop and plug it in. Get a new computer and repeat steps 1-3.
4. Using the data below, follow the directions for using Excel to make a graph. (reference for
data: http://www.cdc.gov/tobacco/data_statistics/Factsheets/index.htm, updated 2016)
Age (years) % that smoke cigarettes
18 17
24 20
44 18
65 9

5. Open Excel [click on the Start button in the lower left corner of the screen; All Programs;
Microsoft Office folder; Microsoft Excel].
6. Click on cell A1 and type a heading for your X variable (independent variable, age). If your
label falls outside of the cell, click on an empty cell and then place the cursor over the line
separating the two columns. When this symbol appears, double click and the column will
expand to the correct size.
7. Starting in cell A2, enter the measurements for your X variable in column A.
8. Click on cell B1 and type a heading for your Y variable. Enter the measurements for your Y
variable in the cells below that heading.

1/15/2020 3:00 PM 10
9. Choose all numbers entered by clicking and holding down the left mouse
button on A2 and pulling down and across until all numbers and only the
numbers, except the first is changed to a dark background.
a. Do not select text.
10. Click on the Insert tab in the Ribbon. The different types of Charts
available will be visible on your screen. Select chart type - in this case,
Scatter. Click on the Scatter button to reveal the chart sub-types – in this
case, the first one (points only).
a. (If you had entered your data in rows, you would need to tell Excel by selecting your
chart and clicking on the Design tab on the Ribbon and then clicking on the Switch
Row/Column button.)
11. To change or add a title and labeled axes to your graph, click on a blank area of the graph.
This will bring up a Chart Tools header (underlined in green) that has two tabs: Design and
Format.
a. Click on the Design tab.
b. Click on the grey arrow on the right/bottom side of the Quick Layout area. Different
selections will drop down; choose the one with a Title and both Axis Labels (usually
the first selection).
c. Click in the Title text box. Change the title to % Smoking vs. Age
d. Click in the axis text boxes. Change the independent variable to Age (Years). Change
the dependent variable to % Smoking.
12. Click the Legend to the right of the graph, then delete it. Your graph would look something
like this (but do not copy!):

13. Adjust the axis values for this graph:

a. Right click on the axis numbers, then select Format Axis.
b. Under Bounds, change the Minimum to 5.
c. Note how this changes the axis origin.
14. In addition:
a. In Format Axis select Units and change the Major value to 5. Repeat this for the other
axis.
b. Change the Gridlines. Right click on both axis numbers, then select Add Minor
Gridlines.

1/15/2020 3:00 PM 11
15. Add the Pearson’s coefficient. This is a statistical measure of the strength of the relationship
between two variables. The closer the r value is to -1 and +1 the stronger the relationship: 0
indicates no correlation, -1 indicates a negative (inverse) correlation (one variable goes up
and the other goes down. downward sloping line), while +1 indicates a positive (direct)
relationship (both variables increase, upward sloping line). Calculate the Pearson’s
coefficient:
a. Place your cursor in an empty cell near your data set.
b. Click on the Formulas tab on the Ribbon.
c. Click on the fx on the left side of the Ribbon to bring up the “Insert Function” box.
d. Under “Or select a Category” click on Statistical. Under “Select a Function”, scroll down
to find and click on Pearson.
e. Click on OK.
f. In the next box, Pearson, choose the numbers in the first column for “Array1” (the range
of cells that contain your x variable data, A2:A5 or whichever cell contains your last
entry – or you can click in the box and use your mouse to select the data).
g. Repeat for the y variable data in “Array2” with the numbers in the second column.
h. Click on “OK”.
i. The Pearson’s value is shown in the cell you selected in step a.
j. Position the cursor after the word “Age” in the title text box on the graph. Press enter to
begin a new line and add the Pearson’s value (“r=x”, x is your Pearson’s value).
16. Add a trendline, slope equation and R2 value:
 A trendline is a graphic representation of trends in data series, such as a line sloping
upward to represent increased sales over a period of months. A trendline is always
associated with a data series, but a trendline does not represent the data of that data
series. Instead, a trendline is used to depict trends in your existing data or forecasts of
future data.
 The slope equation gives the equation that describes the trendline (y-intercept and slope).
 The R-squared value indicates how closely your data fit the trendline – a value closer to 1
indicates a better fit.
To add a trendline, slope equation, and R-squared to your graph:
a. Right click on a data point on the graph and select Add Trendline.
b. Select a Linear.
c. Before closing the window, scroll down and select Display Equation on chart and
Display R-squared value on chart.
d. Click on the equation text box and move it to a readable location. Your graph will now
look something like this (again, don’t copy):

e. If all the data points were on the line,

what Pearson’s value would you expect?

1/15/2020 3:00 PM 12
17. To Print or Save your graph:
a. Click on the Office button on the top left hand corner of the window. The menu that
opens give you options for saving or printing.
i. If you want to print the graph as an object in the page, so that the data table
and the graph both are printed, be sure that you do not have the graph
highlighted (you have not clicked the cursor inside the graph prior to printing).
ii. To print just the graph (it will take up the whole page), click on a blank spot on
the graph to highlight it prior to printing.
18. Repeat the graphing process for the next three sets of data (Graphs 2-4 below), selecting
the appropriate graph type for the data. Not all graphs are scatter graphs! Note that
Pearson’s coefficient is only appropriate for data that can be graphed in a straight line
scatter graph.
19. If you are using a school laptop, log out and turn off the computer. Return the laptop to its
matching numbered tray on the cart and plug it in.

NOTE 1: Due to a quirk in Microsoft Excel, when creating a LINE graph in Excel, it is best to
choose SCATTER and then select a SUBTYPE with lines.

NOTE 2: On the data below, evaluate which is the dependent and which is the independent
variable. It is easiest to graph in Excel if you place the independent variable in the left-hand
column

NOTE 3: You must select the most appropriate type of graph for the data below.

NOTE 4: Each student much do their own graphing work. Group graphs are not accepted.

NOTE 5: Critique your graph: Does it make sense?

Average offspring Age of Daphnia

Graph 2. Researchers were studying the reproductive
per day in days
rate of the water flea, Daphnia. They counted the
number of offspring produced by female Daphnia of 0 6
different ages. Their data are shown in the table. 1.8 8
4 14
4.2 16
3.4 18
3 21
1.6 24.5
1.4 26.5
2.4 28.5
1.2 30
1.3 32.5

1/15/2020 3:00 PM 13
 % Favorite Flavor
16 Vanilla
Graph 3. A class did a survey of the classmates’ 25 Chocolate
favorite types of ice cream. They then 9 Mint chip
calculated the percentage of favorite types. 14 Strawberry
20 Praline and cream
5 Butter Pecan
11 Truffle

Number of
Time
Graph 4. Graph the Paramecium* population levels on p. 3 of Paramecium
(hours)
this lab. Earlier you were asked to read the lowest and highest in test tube
rate (slope) of reproductive growth in a 30 minute interval by 0 10
this population of Paramecium. (Hint: rate (slope) is calculated 0.5 12
as change over a period of time). A rate, or slope, can also be 1 15
calculated in Excel.
1.5 21
1. Construct the graph in Excel.
2 29
2. Select a cell in Excel, then select fx, type “slope” in the
“Search for a function” box, click Go, double click Slope in 2.5 42
the Select a function box. 3 57
3. Click the Known_ys box and type or select 30 min of y data. 3.5 76
Do the same for Known_xs with the matching 30 min of x 4 85
data. Click OK to see the slope calculation. 4.5 89
4. Note: You do not have to calculate every rate, if you visually 5 90
have an idea when the lowest and highest rate (slope) of 5.5 70
reproductive growth in a 30 minute interval by this 6 49
population is, you can start with that portion of the data as
“known” y and x. You should use a few other sections of data to check your assumption.

1/15/2020 3:00 PM 14