Excel Tutorial BIO 204

1
Excel Tutorial BIO 204

• Additional background in Knisely
• 1st Edition pp.145-165
• 2nd Edition pp. 175-205
• Assignment due in lab

2
Excel

• Excel is a Microsoft spreadsheet program found in
  standard MS-Office software packages.
  Spreadsheets are useful for entering, organizing,
  graphing, and analyzing data and will be used
  throughout this course and others in the Biology
  Department.
• Excel will be used in BIO-204 to organize and
  graph data to help you draw conclusions about
  major trends in the data.
• This tutorial assumes you have some basic
  computer knowledge and vocabulary. If you are
  unfamiliar with the terminology or specific
  skills please see your TA or ATUS for help.
• The more you use Excel, the more you will learn.
  So...
• Play around with it. Explore different menu
  options, right mouse clicks,
• and different tabs in the windows. Explore and
  enjoy, some of the stuff is pretty cool.

3
After completing this tutorial you should...

• Be familiar with standard functions and general
  layout of Excel.
• Be able to enter data in a workbook.
• Be able to use formulas to do simple calculations
  in Excel.
• Be able to plot data using Chart Wizard.
• Understand what properly formatted figures
  tables look like and know how to format your own
  figures tables.
• Be able to draw simple conclusions about the main
  trends in data presented in a figure.
• Marvel at the opportunities that Excel will bring
  to your life!

4
Getting through the tutorial

• Keep this tutorial open in one Window and Open
  Excel in another (see next page).
• As you work through the tutorial you will be
  instructed to perform certain tasks in Excel.
• Go through the tutorial step-by-step and do each
  activity in the order described here.
• If you get caught up at any one step you may find
  it useful to open up the Help menu in Excel and
  search for help on that activity.
• Alternatively, if youre really stuck or want
  some extra help here are some ideas
• Attend a free workshop offered by ATUS
  (https//west.wwu.edu/training/)
• Go to your TAs office hours.
• Ask your roommate.
• You will be required to turn in your datasheet as
  you entered it in Excel, your final figure and a
  table, and a response to the final question at
  the end of the tutorial.

5

• To start
• Open Excel
• Save the worksheet to your U-Drive (Your U-drive
  is a network drive accessible on and off campus
  that you can use as a student at WWU)
• File Save (SAVE OFTEN!)
• Use the Save in drop-down menu to navigate to
  your (U) drive
• Click your U-Drive, type in a name for the file
  and save

6

• Excel allows you to create ledger-like
  spreadsheets that can perform automatic
  calculations.
• Each Excel file is a workbook that can hold many
  worksheets. The worksheet is a grid of columns
  (designated by letters) and rows (designated by
  numbers). The letters and numbers of the columns
  and rows are in gray buttons at the top and left
  sides of the worksheet.
• The intersection of a column and a row is called
  a cell, the cell address of which is the column
  letter and the row number.
• Cells can contain either text, numbers, or
  mathematical formulas.

7

• You are investigating the effect of distance away
  from low-tide on the abundance of two different
  intertidal species along a transect (a linear
  sampling scheme).
• The data used to describe these two organisms
  distributions have been fabricated to illustrate
  the points of this tutorial.

Species 1
Species 2
Transect line
ocean
Low tide
Transect length 10 meters
8

• Step 1 Enter the following data into Excel
• Start by clicking on cell A1. The cell is now
  highlighted. Type the word Distance. Use the
  arrow keys to move to other cells to finish
  entering the other table data.
• Enter YOUR LAST NAME in parentheses in cells A13
  and A14. You would not ordinarily do this for a
  figure in a report, it is simply for this
  assignment only.

Notice that when a cell is highlighted, you can
also see the contents of the cell in the formula
box.
Enter your own last name in these cells.
9
Step 2 Enter formulas to calculate totals and
averages.

• Put away your calculator!
• Excel can compute simple and many complex
  calculations for you. You just need to know how
  to communicate with the program. There are many
  ways to start enter formulas, we will try a
  couple different ways.
• You want to sum up all of the organisms you
  counted to get total number of organisms for each
  species.

• Click on cell B12.
• Type sum(
• Now you need to highlight the cells that you want
  Excel to add up.
• Highlight cells B2B11. Do this by placing the
  cursor in cell B2, then click and hold the left
  button while moving the cursor to cell B11.
• Press Enter.

10
Step 3 Copy the formula from B12 into C12.

• There are many ways to copy and paste. Feel free
  to use whatever you know. Heres one way.
• Right click cell B12.
• Click Copy
• Right Click cell C12
• Click Paste
• NOW, click once on cell C12. Look at the formula
  bar at the top, notice that Excel changed the
  column from B to C in your formula, thereby
  referencing the correct cells in column C that
  you want to use to sum the totals for species 2.

11
Step 4 Calculate the average number of
individuals for the first 5 meters and the second
5 meters.

• Heres another way to enter a formula.
• Click on cell B13.
• Click the fx button next to the formula box.
• The Insert Function window appears.
• Search for the average function, type average
  into the top box.
• Click on AVERAGE
• Click OK
• Note, that the AVERAGE function in Excel,
  calculates the Mean value.

12
Step 4 Continued

• The Function Arguments window appears. Notice the
  cells that Excel suggests are not correct for
  calculating the average number of individuals in
  the first 5 m.
• Move your mouse back to the workbook and select
  cells B2B6.
• Click OK.
• Instead of copying and pasting the formula for
  Species 2, TRY THIS
• Click cell B13.
• Move the arrow to the small black box in the
  bottom right corner.
• The arrow should change to a
• Click the small black box and drag it over 1
  cell.
• Release. The formula has been copied over.
• To make sure double-click cell B13. Youll see
  the formula and the selected cells. PRESS ENTER.

Dont worry about this, if you are curious. Click
it.
13
Step 5 Enter formulas to calculate the average
for the second 5 meters.

• Enter a formula into cell B14 to calculate the
  average for cells B7B11.
• Copy the formula from B14 into C14.
• You may not be able to see the entire contents of
  cells A13 and A14.
• Move your arrow to the top of the columns so that
  it is touching the line between columns A and B.
• The arrow should change shape now, widen the
  column by either clicking and moving the column,
  or double-click, which will widen it to fit the
  longest text.

14
Step 6 THINK.

• Before you make a figure, think about the
  question you are trying to answer
• Is there a difference in the average number of
  organisms for the two species between the first
  five meters and the second five meters of the
  transect?
• Now ? What kind of graph will best show what you
  want to know? Bar? Scatterplot? Pie?
• So, lets make a figure with distance on the
  x-axis, and average number of organisms on the
  y-axis.
• Then well plot, as vertical bars, the average
  number of organisms we calculated for each
  species for each 5 meters.
• HINT It helps to think about what it should look
  like.
• Look at the data THINK and maybe even make a
  quick sketch

Number
Distance
15
Step 7 Highlight the cells you want to graph and
make a scatter plot using Chart Wizard.

• Highlight cells B13C14.
• Click the Chart Wizard button on the toolbar.
• The Chart Wizard window appears.
• Chart Type Click on Column
• Chart sub-type Click on the upper left box that
  shows just separate bars (no 3d).
• Click Next.

When you are finished with the assignment, try
making different chart types just to see what
they are like. For a challenge, see if you can
make an XY scatter plot of all of the data (not
averages or totals). Maybe even add a trendline.
16
Step 7 Continued adding series names and
x-axis labels

• You should be on the Data Range tabbed form
  (check tab, upper left). At the bottom of the
  screen, where it says Series n click on the
  Columns radio button. This tells Excel that
  data from each species is listed vertically.
• Now click the Series Tab.
• Notice that the Series box shows that the
  species are plotted separately.
• In the Series box, click Series 1.
• In the Name box, type Decorator crab
• In the Series box, click Series 2.
• In the Name box, type Purple seastar
• It looks good, but the x-axis could be better
  labeled. Notice that there is a box for the
  x-axis.
• Click the Small box to the right, and highlight
  cells A13A14.
• Press Enter (the labels are updated).
• Click Next.

17
Step 7 Continued formatting titles

• Notice several tabs at the top of this window.
• Type in the chart title, and (x) axis and (y)
  axis labels as seen below.
• Always label your axes and include units when
  appropriate.
• The title for a figure ALWAYS goes BELOW the
  figure, notice that Excel doesnt understand
  this. Youll move it later.
• A figure title should always start with the word
  Figure and a number representing the order at
  which the figures appear in a report or paper.
• The figure title should be a short descriptive
  sentence or statement that tells the reader
  enough information about what is shown so that
  the figure could stand alone without supporting
  text, and still make sense. Dont forget the
  period at the end!
• For this assignment you must include YOUR
  LASTNAME in parentheses at the end of the title.
  You would not ordinarily do this for an
  assignment.

Type this chart title
18
Step 7 Continued - more formatting

• Click the Gridlines tab.
• Un-check the Major gridlines option.
• Gridlines are distracting for most figures and
  are often removed.
• Click the Legend tab.
• The legend is helpful in this example and a right
  alignment is OK.
• Explore the other tabs in this window so that you
  are familiar with the functions that you didnt
  use for this assignment.
• Click Next
• This window asks whether you want the figure
  (object) to live in the current worksheet that
  your data is in or in a new one.
• Select As object in
• Click Finish

19
Step 8 FIX-IT! Properly formatting the Figure
so it looks good.

• Great job! But the figure could benefit from
  additional formatting, so lets make some
  changes!
• First, resize the figure so that you can see it
  better. This step is important!
• To resize, select the figure, then click and drag
  the small box at the bottom right corner and make
  the figure larger and change its shape.
• You should be able to clearly read all axes,
  including the scale on the y-axis.
• Many of the formatting tools can be found by
  right-clicking inside the chart, in the white
  space.
• Right-Click in the chart area.
• Select Format Chart Area.
• Select the Patterns tab.
• In the Border box, select None.
• Select the Font tab.
• The font in your figure should match the font in
  your paper or report.
• Select Times New Roman, Regular style size 12 (or
  as low as 10 if necessary).
• Click OK.
• Hey! What happened? You will probably notice
  that Excel cuts off the last letter of the y-axis
  label after you switch to Times New Roman font.
  Dont worry about this. As a trick, try typing
  an additional s at the end of the label (it
  will cut off the second s, but show the first
  one!)

Explore some of the other right-click options.
20
Step 8 FIX-IT! Remove gray background and move
the title to bottom of the figure.

• By default, Excel uses a gray background. Figures
  that you include in your papers should have NO
  color in the background.
• Right-click the gray part of the figure (plot
  area).
• Click Format Plot Area
• In the Area box, select None
• But before you click OK,
• In the Border box, select None
• Click OK
• Click once on the Chart Title.
• A gray box appears around the title.
• Click on the gray box and drag the title to the
  bottom of the figure.
• Now, to make room for the title below, click once
  in the plot area and drag the figure up.

So far, your figure should look similar to this
one.
21
Step 8 FIX-IT Change the data series to
grayscale.

• Typically, figures should be formatted so that
  they are readable when printed in black and
  white.
• Try to remove colors and, if necessary, change
  the shading or patterns of your data series so
  you can more easily distinguish them.
• Right-click one of the bars for the crab species.
• Click Format Data Series.
• In the Area box, select a dark gray color
• Click OK.
• For the seastar species, change the color to the
  lightest gray.

22
Step 9 Change the y-axis scale so that the
maximum is 10 individuals.

• The scale of both axes are adequate, however,
  lets change the y-axis so that the maximum is 10
  organisms.
• Right-click the y-axis.
• Click Format Axis
• Notice several tabs in this window.
• Click the Scale tab.
• Change the Maximum to 10
• Notice that there are other aspects of the Y
  scale that can be changed from this window.
  Likewise, if necessary, the X axis scale can be
  detailed if you right click on it.
• Click OK.

and one more time for good measure make sure
the plot area is large enough so the data and all
labels can be easily read and interpreted.
23
CONGRATULATIONS!!

• SWEET! Youve just made a properly formatted
  figure these general formatting guidelines will
  apply to many of the Biology classes you take
  here.
• Look it over what do you think? Does it make
  sense? Can you see any trends in the data?
• Work through the Figure checklist/rubric included
  in the next slide to make sure you havent
  forgotten anything.
• Its easiest to make changes to your figure
  before you move it into a Word document.

24
Figure checklist/rubric
This rubric is similar to the one on your
upcoming Results section assignment, so make sure
you understand how to do all of these things!
___Without gridlines ___White background ___Withou
t border around plot area ___12 pt font ___Times
New Roman font type ___Regular style font (no
bold) ___Caption at bottom of figure ___Caption
is a title that states what the figure is showing
(use title provided) ___Correct capitalization is
used for caption ___Caption begins with "Figure
X. ..." and ends with "." ___Caption includes
your last name ___Legend is included and
correctly labeled ___Color is adjusted for
printing in black and white ___x-axis labeled
correctly ___y-axis labeled correctly ___Figure
is large enough to read/interpret ___Axis is
scaled to 10
25
Next copy the figure to a Word document

• Open a new Word document.
• Double check your formatting
• Click in the white space above your legend to
  select the figure.
• Press the Ctrl key, and then the C button (This
  is a short-cut way to copy something).
• Paste your figure into the Word document.
• Right-click, Click Paste.

26
Format a table

• Note 1 Raw data will not typically be included
  in a lab report. We will do the following to
  practice the proper approach to formatting tables
  for scientific papers.
• Note 2 The table could be built in Word alone,
  but it is sometimes better to build it in Excel,
  especially if formulas are involved and then copy
  it to Word and do final formatting and adding a
  caption there. These formatting commands work
  the same in Excel and Word.
• Note 3 As in figure formatting, the formatting
  of tables should be done to highlight and clarify
  the data you want to show. There are certain
  rules (see the grading rubric several PowerPoint
  slides hence) but much of formatting tables is
  subjective what looks the best. The following
  are suggestions.
• In your Excel worksheet highlight all of your
  data, cells A1C14.
• Right-click, Click Copy.
• Now move to a blank cell below, A26 and,
• Right-click, Click Paste Special (not Paste
  alone), then Values. This will copy all your
  data to a new table, including the averages (not
  the formulas) you previously calculated. This
  table can be formatted without worrying about
  changing your figure which references the
  original table.

27
format a table

• Now we can clarify some of the information in the
  table
• Click on A26, move the cursor to the end of
  Distance and add (meters)
• Click on B26, replace Species 1 with Decorator
  crab
• Click on C26, replace Species 2 with Purple
  seastar
• Dont forget! You will need to resize these
  columns to fit the new headings.
• Highlight B25 C25 together right click and
  click on Format Cells, choose the Alignment tab,
  then click to select the Merge Cells box
• In the merged cell, type Number of Individuals
• Highlight the entire table and center-justify all
  cells by clicking on the icon on the
  spreadsheet menu bar.
• We want to add some horizontal lines to separate
  parts of the table (tables in papers can have
  horizontal lines but not vertical lines)
• Highlight cells A25-C26. Right click, choose
  Format Cells, then the Border tab. Click on the
  medium thick line, 5th one down in the right
  column in the Style box to the right. Now click
  on the areas above and below (but not between)
  the four texts in the box to the left. Press
  OK. This will place a heavy line above and below
  the column labels.
• Highlight cells A37-C39. Repeat the operation in
  the last step, but this time also click on the
  area between the texts in the left box as well
  as above and below. Press OK. This will add
  horizontal lines separating the last three rows.

28
and copy it to a Word document

• Your table should look something like the figure
  to the right.
• Now highlight all cells in the new table,
  A25C39.
• Right-click, Click Copy.
• Open your Word document, move to below your
  figure, press Enter a couple of times and then
  paste your table into the document.
• Right-click, Click Paste.

• The table should look about as it did in Excel.
• Note Although gridlines show separating all the
  cells in both Excel and Word, these will not show
  up when you print either document only border
  lines that you add will show.
• Click on the line above the table and type in the
  following caption Table 1. Raw data on marine
  invertebrate distribution in the intertidal zone.
  Distance refers to meter intervals on a transect
  above mean low tide.
• Examine the grading rubric on the next page to
  make sure everything is okay.

29
Table checklist/rubric
This rubric is similar to the one on your
upcoming Results section assignment, so make sure
you understand how to do all of these things!
___12 pt font, or 10 if necessary ___Times New
Roman font type ___Regular style font (no
bold) ___Caption at top of table ___Caption is a
title that states what the table is showing (use
title provided) ___Correct capitalization is used
for caption ___Caption begins with "Table X. ..."
and ends with "." ___Appropriate column and row
headings ___Units are provided in column/row
headings ___Table is large enough to
read/interpret ___Lines spaced to fit headings or
data ___Appropriate use of gridlines
30
Finally, interpret the figureWhats going on???

• Look at your figure and THINKWhat was the
  question you wanted to answer? What are the
  trends in the data? How does the figure support
  your findings?
• BIG QUESTION In the space below your figure and
  table, respond to the following question and
  explain your reasoning with information from your
  figure.
• Is there a difference in the average number of
  individuals for the two species between the first
  five meters and the second five meters of the
  transect?

31
What to turn in.

• Due at the beginning of your first 204 lab.
• Turn in a one-page word document that includes
• Your properly formatted figure and raw data
  table.
• Your response to the Big Question.
• YOURE DONE!!