Excel

Data set 

How can students analyze their data and create tables and figures in excel?

Due Date: 9/16/2024  11:30am

Learning Objective: Students will learn how to create graphs and tables in excel to properly present data in a scientific format. 

Materials: Three datasets provided by the TAs; files are downloadable from Canvas and computer access.

Instruction:  Following the demonstration and discussion lead by the TAs please use the datasets provided to present data visually in both a graphic and table format.  Please choose the best option to present your data visually in graph format and average rows to present data in a table format.

Introduction: Typically, when you collect data in the field, you will enter the data by building an excel file. For this lab, the excel data files has been provided to you the form of three separate datasheets. Please review the files, read the following instructions to create tables in Microsoft Word and figures in Microsoft Excel, and then complete the assignment at the end of this document. Data organization is a key component of scientific research, as well properly displaying the data you collected to show trends. 

Steps to create figures in Microsoft Excel:

• Build excel spread sheet

  Columns should be labeled appropriately

• Enter data

  Enter individual data points for data collection
  Double check data to make sure there are no mistakes

• Create graph

  Select the columns or rows you are graphing
  Select “insert” on the excel file and then select the chart (graph) type you would like to use. Please view multiple options before selecting the chart type to best represent your data.
  Click on the chart to format the chart.
  Click on your data point on the chart to add or delete chart elements including:

  Axes
  Axis title
  Axis legend
  Add or change color
  Title graph – use a meaningful title that can stand alone explaining what the graph represents.

  Average data for tables and graphs using excel

  Select a blank cell on the excel file at the end of the row or column you would like to average.
  From the menu select “home” and then “autosum”
  Click on “autosum” & then “average” and select the data within the column or row you would like to average.
  Please note there are additional functions you can select in addition to average.

Steps to Create Tables in Microsoft Word

• Go to “Insert”, click on “Table”, then select “insert table” and select the appropriate number of rows and columns. It is easy to add or delete rows or columns after creating the table by highlighting a row or column, right-clicking on your mouse and select insert or delete.

Assignment: Please use the three datasets given to individually complete the following assignment.  Please do not forget to give your tables and charts titles that explain the dataset and label all axes on your graphs. You only need to turn in the graphs and tables.

Datasets: Your excel has three tabs, one for each of the datasets below.

1. Nest Depredation
2. Create a table that shows the average number of nests depredated by nest type on Day 4, Day 8 and Day 12
3. Create a graph that shows how nest predation differed by nest type
4. Sacramento Mountain Salamander
5. Create a graph that depicts how the number of salamanders on the surface varied with % humidity.
6. Chihuahuan Desert Vegetation Variables
7. Create a graph that compares % Canopy Cover (%CC), % Bare Ground (%BG) and % Grass for both habitat types.
8. Create two pie charts to compare % Grass, %BG and % CC for edge vs center plots – to do this you will need to average %CC,%BG and % Grass for each plot.