Excel Tutorial � BIO 204

Excel Tutorial – BIO 204 Additional background in Knisely: 1st Edition pp.145-165 2nd Edition pp. 175-205 Assignment due in lab 1 Excel • Excel is a Microsoft spreadsheet program found in standard MSOffice 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 data in several lab exercises. • There will be a practical question on the final lab exam requiring you to build a graph from data using Excel. You will have ample opportunity to practice using this program. Be sure to do so: don’t let your lab partners do all the work! • 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. 2 Excel • NOTE: The following instructions and all spreadsheets used in Bio 204 will use the Microsoft® Office 2003/2005 version of Excel. If you use a Mac or Vista, you are on your own, as these versions are somewhat different. If you are new to spreadsheets, avoid problems by using the 2003/2005 version (all ATUS-administered computer lab computers default to this version – if in doubt, after opening Excel, choose Help > About Microsoft Excel to see which version you have). See your TA or ATUS for help with your version. • 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. 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 you’re 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 TA’s 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 Transect line Species 2 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 B2:B11. 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. Here’s 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. • • • Here’s 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 B2:B6. 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. You’ll see the formula and the Don’t worry about this, if you are curious. Click it. 13 selected cells. PRESS ENTER. • • • • • • • • • + + 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 B7:B11. 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, let’s make a figure with distance on the x-axis, and average number of organisms on the y-axis. • Then we’ll 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 B13:C14. 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 A13:A14. 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 doesn’t understand this. You’ll 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. Don’t 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 didn’t 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 let’s 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 rightclicking 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. Don’t 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. Number of Organisms 8 7 6 5 4 3 2 1 0 Average 1st 5 m Transect Figure 1. The average number of crab species and seastar species in each half of a 10 meter transect on Bayview Beach in Whatcom County, WA. Average 2nd 5 m Crab Species Seastar Species – – – 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. Number of individuals Decorator crab 8 6 4 2 0 Average 1st 5m Transect Figure 1. The abudance of decorator crabs and purple seastars in each half of a 10 m transect on Bayview Beach in Whatcom County, WA (YOUR LAST NAME). Average 2nd 5m Purple seastar 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, let’s 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! You’ve 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 haven’t forgotten anything. • It’s 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 ___Without 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 A1:C14. – 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” • Don’t 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, A25:C39. – 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 figure…What’s going on??? • Look at your figure and THINK…What 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 prompts and explain your reasoning with information from your figure. – Compare the average number of individuals of Decorator Crabs and Purple Sea Stars in the first five meters to those in the second five meters of the transect. – Describe the specific differences you can see. 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. • YOU’RE DONE!! 32