Proficiency in Excel is one of the fundamental skills all aspiring wildlife biologists need. I therefore thought it would be a good idea to write my first post describing a task I recently had to accomplish using Excel.
I was working on a project that collected data differently depending on whether a species was a focal species or not. For focal species (the spreadsheet on the right), we recorded each individual in a new row and tracked their detection though time (a 1 in the columns named block means the species was either seen or heard during that time interval). For additional species (the spreadsheet on the left), we did not track individuals through time but simply counted the number of birds at each site.
Now, what if I am not interested in tracking focal species through time but instead want a spreadsheet that provides daily counts for each species?
The Challenge:
The challenge I present in this post requires us to take the above spreadsheets and find a way to obtain daily counts for each species (the spreadsheet on the left already meets this criteria, so only the spreadsheet on the right will need manipulation). In the end, we should have a single spreadsheet that looks like the one below:
My Solution:
The trick here will be to use a pivot table.
1) Highlight the data in the focal species spreadsheet
2) Click Insert -> PivotTable
3) Click OK in the Create PivotTable window
4) From the PivotTable Field List located on the right side of the screen, drag the Date field into the Row Labels box
5) Do the same with the SPECIES field
6) Now drag the SPECIES field into the Values box
You will get:
We can see that we are getting closer to our goal. However, there is too much information in this spreadsheet since we do not want sum of counts in our spreadsheet.
7) Highlight the data, right click, and choose PivotTable Options
8) In the PivotTable Options window, select the Classic PivotTable layout and click OK
Your data should now look like this:
9) With the data still highlighted, right click and unselect Subtotal Date
You should now see:
Almost done! There are still empty cells below the dates that we need to fill in. To do this:
10) Copy and Paste your table in a new worksheet
11) Highlight the cells in the first column (the column that has empty cells)
12) With the cells highlighted, click Find & Select
13) Scroll down to and click Go To Special
14) In the Go To Special window, select Blank and click OK
We will need to add a formula to fill in the cells. To do this:
15) Type the equal sign "=" and click the up arrow
16) Hit CTRL+ENTER to copy the formula into all the cells
Et voilà! From here all that is left to do is to copy these cells into the additional species spreadsheet.
No comments:
Post a Comment