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.