Replacing AOU Bird Species Codes with Common Names, Latin Names, and Family Names in Excel. Plus, Sorting Birds by Phylogenetic Order.

I produced this tool to convert AOU Bird Species Alpha Codes (standard 4-letter codes established by the American Orithological Union) into common names or scientific names in Excel spreadsheets.  It has other functionality as well, such as helping to sort bird species in taxanomic order and by family.

Download the free Excel spreadsheet "BirdMacroLibrary.xlsm" (formerly "Colin's Macro Library.xls") by clicking the button below (or get general info about my natural resources downloads here). 

The spreadsheet uses Excel macros.  To learn more about using macros in my wildlife and botany spreadsheets, click here.  Spreadsheet works with Excel versions 2003, 2007, and 2010. (Also see my pages about Excel formula usage and a software disclaimer).

The first 4 sets of instructions below use macros, the 5th item uses formulas, and the 6th describes how to copy & paste your codes into a table to have them automatically converted.  If you're not comfortable using macros, go to part 6.

1. Replace AOU Codes with Common Names in Excel

The following instructions describe how to use Colin's bird macro library for Excel spreadsheets that have AOU codes in either columns or rows.  This macro will replace the AOU codes with the bird common name without disturbing data in adjacent columns (such as number detected, plot number, etc).  If it comes across an unknown code, it'll leave the code unchanged.  Note:  You can't undo this action.

1. Download "BirdMacroLibrary.xlsm", open it and enable macros if necessary (link above describes how).
2. Open your spreadsheet with bird data if it's not already open (both the macro library and your data spreadsheet need to be open on your computer).
3. In your spreadsheet, select all the 4-letter species codes you wish to convert.
4. Press the shortcut keys "Ctrl + Shift + R"  to replace all codes with the common name.  (i.e., press the "ctrl" key down and while still holding it down, press the "Shift" and "r" keys).

You can practice this on the "Bird Macro Practice" tab of the macro library spreadsheet.

Macro shortcut keys can be changed under menu item "View | Macros | View Macros" (Excel 2007/2010).  On the list of macros that show up, click on the name of the macro and then click on "Options" to change the shortcut.  Note that if you change the names of tabs or the filename of the macro library, some macros will not work.

2. Create New Column or Row and with Common Names in Excel

This is a bit of a safer operation since macros cannot be undone.  These macros will create a new column (or row) and place the common name that corresponds to the species code in it, rather than replacing the original code.  The directions are the same as above, except:

• Place your cursor on the first AOU species code in the row or column.
• If data is in columns, press "Ctrl + m".  If data are in a row, press "Ctrl + Shift + M".

You can practice this on the "Bird Macro Practice" tab of the macro library spreadsheet.

3. Replace AOU Species Code with ANY NAME in the AOU List

1. Again, have "BirdMacroLibrary.xlsm" open at the same time as your data spreadsheet.
2. It's recommended to make a copy of the column or row that has the species codes in it.
• Example: For data in columns, click on the column header with codes in it to select the whole column.
• Press "ctrl + c" to copy the selected column.
• Right-click on the column header and select "Insert".
• Place your cursor at the top of the new column, select the cell, and press "ctrl + v" to paste the data.
3. Highlight the species codes that you wish to convert.
4. Press "Ctrl + r". 
5. An input menu will pop-up that looks like the image below.  Enter a selection and press OK.

Pop-up input box to replace AOU species code with common name, scientific name, family, etc.

  You can practice this on the "Bird Macro Practice" tab of the macro library spreadsheet.

4. Sort Bird Lists by Phylogenetic Order

This macro will insert a column to the left of your AOU codes and place the phylogenetic sorting order of that species in the new column.  You then manually sort all the data based on the sort order.  Data must be in AOU species codes format and must be in columns.  (See next section below about how to use the VLOOKUP function to sort birds by phylogenetic order if the data are in common name or latin name formats, or edit the macro to suit your needs).

1. Have both the bird macro library and your data spreadsheet open at the same time.
2. Click on the first (top) AOU code in the column.
3. Press "ctrl + p".  A new column will be created and the sort orders will be placed in the cells.
4. Give the new column a name, such as "Phylogenetic Order."
5. Highlight all the data, in the table to ensure associated data, such as the record date or number detected, gets sorted with the species names.
6. Use the Excel sort tool to sort by the numbers in the "Phylogenetic Order" column, from low to high.
7. You may then delete the sort order column.

5. How to Sort Birds by Family Name (and cross-reference birds with other AOU info)

This section describes how to use a "VLOOKUP" function to assign ornithological Family names to lists of birds in Excel.  This is not a macro, but can easily be made into a macro function like those above.  But learning how to use this function will aid you in matching a list of birds in any format (AOU code, common name, or latin name) with any information cross-referenced in the AOU Alpha Code list (such as Order, Family, Sub-Family, Scientific Name, etc).

BirdMacroLibrary.xlsm has to be open in order to use this function as described here.

For this example refer to the image below:

For the example of matching AOU Alpha codes with family name, look at the text in GREEN in the example (sorry to those who are red/green colorblind).  This is the same data on the "code lookup example" tab of "BirdMacroLibrary.xlsm".

The VLOOKUP function has four parameters that need to be set:

1. Lookup_value is the cell that holds the bird name (it can be an AOU Code, common name, or scientific name).  In this example, the lookup value is "A3" and refers to "NOMO," or "Northern Mockingbird."
2. Table_Array tells Excel where to find the value that you want to look up and match.  '[BirdMacroLibrary.xlsm]LIST10'!A$2:H$2169 says to look in the macro library, and specifically look at the tab "LIST10" and at cells in the row and column range A3 to H2169. (The apparent double quotes in Colin''s is actually two single quotes, which is how the code says an apostrophe is used in the name).
3. Col_Index_Num tells which column holds the information that you want to match to the value that you just looked up.  In this case, cell A3 (NOMO) was the value that was looked-up and we want to match it to Family name, which is the 10th column on the tab 'LIST10'.
4. The last parameter (range_lookup) should be "FALSE".  This means only an exact match for the value in A2 will be used.  This is important because there are bird names (especially scientific and common names) that are fairly similar and a close match may wind up matching the wrong information.

Here are the steps to use the VLOOKUP function with "BirdMacroLibrary.xlsm" to match bird names with family names and then sort and group based on family:

• In a blank cell in an adjacent column to the bird name or code, type "=VLOOKUP(..." and enter all the parameter values as described above.  Also, title this column "Family Name" or similar.
• Copy the formula in the cell down the column to all rows of data.  Since we're using relative references, the lookup value will update itself for each row or bird record.
• Family names should appear in the column with the VLOOKUP formula in it.
• Highlight the column with family names in it and do a "Copy and paste values":
• After the column with the formulas are highlighted press the following shortcut keys (one at a time, not all at the same time): "alt", "e", "s", "v".
• Finally, follow the instructions in section 3 of this guide to sort the data by phylogenetic order.  Or, use the text in RED in the example above or in the spreadsheet to use a VLOOKUP statement to assign sort order values to your data.

6. Using the Bird Code Lookup sheet to cross-reference your list of bird Alpha Codes with all available information, including separate genus and species columns.

Again, this is not a macro, but is a spreadsheet tool in "BirdMacroLibrary.xlsm".

• Open my macro library and go to the "Bird Code Lookup" tab.
• Paste your species codes in the yellow column to have all other info show up.
• Copy & paste the info you need to your own spreadsheet. Remember to paste as "values" (press the following keys in order, not at the same time: "alt," "e," "s," and "v.")  You can follow this by pasting the text formats (alt, e, s, and t) if you wish.

Page is "protected."  You can only enter info into the yellow column.  To unprotect, go to "Review" and "Unprotect Sheet" in Excel 2007/2010.

AOU Bird species code converter spreadsheet tool (not a macro).

7. Updating "BirdMacroLibrary.xlsm"
The list used in my macro library to convert AOU species codes is from the most recent update provided on the Institute for Bird Populations website (http://www.birdpop.org/AlphaCodes.htm), which is Sept 19, 2012.

The taxonomic/phylogenetic order info came from the AOU website: http://checklist.aou.org/taxa.

If you need to update the spreadsheet, go to the IBP website and download the zipped DBF file "English and Scientific Alpha Codes for North American Birds"  (a note at the top of the page tells when the most recent update was). Copy and paste the data into the "LIST10" tab on Colin's macro library.  You'll need to add phylogenetic sort code column in colum "H" for the macros to work properly.

======================================================================

That's all there is to using "Colin's macro library.xls" macros and spreadsheet tools.  A 3-part instructional blog for converting plant species codes into common names, scientific names, etc. is here.  Please leave me a comment if you have questions or feedback.

How to Create a Bird Checklist: Part 1 - Assigning Seasons based on Survey Dates (Using Embedded IF Statements)

THE NEED:

I once had 1,800 lines of bird survey data that I wanted to run a report on for a poster I was to present at the New Mexico Ornithological Society annual meeting.  I wanted to show a sort of checklist of birds by season, with data from certain months assigned to each season as follows:

• December though February would be Winter,
• March through May would be Spring,
• June through August would be Summer, and
• September through November would be Fall.

I certainly wasn't going to go through all 1,800 lines of data and enter which date belonged to which season, so I wrote a formula that I could copy and paste into all the rows of data that identified the season for me.  The "checklist" wound up looking like this:

Read on to see how to use embded "IF" statements in Excel to accomplish this.

Excel IF Statements (Shannon-Wiener Diversity Index Calculation Example)

'IF' statements are one of the most useful formula components to learn after you've learned some basic Excel formula writing.  A common, basic use of an IF statement is to say: "If the value of a cell is this, then do this action, if not then do something else." Or:

=IF(Condition You Want to Test, "Value" if True, "Value" if False)

In fact, those "Values" can be other conditional IF statements (making it a nested IF statement, which I'll cover later), or additional formulas.

To show how useful this function can be, we'll use the example of calculating Shannon-Wiener Diversity Indices.  The Shannon-Wiener Diversity Index, often symbolized by H' (or, H-prime), is a measure of species diversity that takes into consideration not only the number of species present, but includes their relative abundance in the population.  The example below shows 5 species of plants found along a line intercept range transect (this was a pre-monitoring survey before a prescribed fire).  The number associated with the species (in column 'C') shows the centimeters of the line covered by the plant.  You could also use number of individuals, biomass, or other measure of abundance.

With 5 species of plants, the species RICHNESS for this transect is 5.  To calculate Diversity, the formula is:
H' = - SUM (Pi * LN[Pi]), where Pi is the proportion that each species makes up of the total abundance found.  LN is the natural log of that same number.

The top portion of the image (yellow section) shows how one might calculate H' by calculating Pi in one column and then Pi*ln[Pi] in a second column, and then adding those values up (and taking the negative of that number).  However there are errors:

Click on picture to make it larger. Columns G through K show the formulas in A to E.

In the top portion, notice two things:

1. The $ (dollar sign) in column D (and J) makes an absolute reference to row 12 which shows total coverage (aka abundance).  I just point this out as an example.
2. Also notice the "#NUM!" error from E9 through E14.  That's because the natural log can't be calculated for zero.  And that's where the IF statement comes in.

The lower portion (green) introduces an IF statement in column E (and K) to eliminate the erroneous calculation.  For instance the value in D19 (as shown by the formula in K19) was calculated by stating:
"If the proportion (D19) is greater than zero, then calculate Pi*LN[Pi], if not then the value is nothing."
=IF(D19>0,D19*LN(D19),"")
And no errors.

To complete the diversity index cacluation, take the negative of the sum of all the values in column E, which comes to 0.945901.  This is a fairly low value, which shows that there's a low species richness and low "evenness" in representation among each species.

So, there you've seen the value of the IF statement in context of calculating the Shannon Index of Diversity.

As another side note, notice I used the "COUNTA" function rather than the "COUNT" function to calculate species richness because COUNTA counts non-empty cells, while "COUNT" counts cells with numbers in it and column B contains text, not numbers.

SUBTOTAL() Function to Summarize Data

I learned about this formula fairly recently as a better alternative to using the SUM() function when summarizing multiple totals in a single data column.

THE NEED:
Adding Subtotals or Totals to a table when there are multiple items.  This function is particularly useful because it can create a column grand total that ignores other subtotals.  I recently used this to calculate (1) the area of dense vegetation I was planning to plant, (2) the area of scattered (buffer) vegetation that was in my project site, and (3) total area being restored.  I have also used it to create grant budget tables.

Combine two columns of data into one

I'm going to start off this blog with a fairly obscure function. Not obscure because the formula is rare, but because I don't imagine that many people would be needing to do this. However, since I've come across the need to do this on more than one occasion, there may be other "-ologists" who wish to learn.

The Need:

I first used this formula when I had a large bird point count dataset with many plots in different management units (or habitats).  I wanted to combine the Unit Names with the Plot Numbers, which were in two separate columns, into a single column.

About Formulas

Many of the Excel tips used in this blog are formulas that calculate or do other functions to data in rows and columns that you specify.  There are many resources on the web where you can learn how formulas work, so this is a very basic primer:

1) Formulas begin with a "+" or "=" followed by some sort of action or calculation that you want to do to data in adjacent column(s) or row(s) that you specify.

Simple calculation formulas use "+", "-", "*", or "/" to add, subtract, multiply, or divide numbers by other numbers.

2) The "range," or cells of data that you want to perform calculations or other activities on are referred to by a letter (column that data is in) and number (row that data is in).

For instance, in the image below of an Excel worksheet, to calculate the sum of "Number" for the first two lines of data, a simple calculation would be "=G2+G3".  This would be entered in any blank cell, for instance in cell H2 or H3.

More complex formulas use functions to do actions on data that you specify in parenthesis.  For example the SUM() function adds all the numbers in the cells that you specify in the parenthesis (i.e., the "range").  For instance "=SUM(G2:G14)" in the example above would return a value of 17.  A logical location to put this formula would be in cell G15 below all the data.  Other simple functions are AVG(), used to average numbers, and COUNT() used to count non-blank cells in a range.  More complex functions use IF statements, VLOOKUP statements, and many, many others.

In this blog, I will often show how a formula is written in the column to the right of (or row below) where a formula exists in a worksheet.  For the example above, Column C is showing the results of a formula which combines text from column A and B.  The text in column D (labeled "Formula") are the exact text that is typed in Column C to produce that result.

More information can be found at these useful sites and others:
  http://office.microsoft.com/en-us/excel-help/examples-of-commonly-used-formulas-HP005200127.aspx
  http://spreadsheets.about.com/od/excelformulas/ss/formula_begin.htm
  http://www.ehow.com/how_2248061_use-formulas-excel.html
  http://excelformulas.info/

Some of my favorite Excel help forums are:
  http://www.mrexcel.com/
  http://www.excelforum.com/
  http://excelhelp.org/