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.

Part 3: Updating the Plant Look-Up Spreadsheet Lists from the USDA Plants Website

UPDATING THE DATABASE LOOK-UP LIST IN THE PLANT SPREADSHEETS

Edit 4/25/2013:  I updated all spreadsheets to be able to convert Scientific Names to Accepted Plant Symbols, which was a requested feature.  In order to do this, I had to eliminate plant synonyms from the data list. But, I added a few other columns such as Family Common Name, Family Symbol, and Order.  The images below look slightly different than those in the updated spreadsheets.  - Colin

If you're using Excel 2003, you should download the ".xls" files.   If you're using Excel 2003, be aware that there is a 65,536 row data limit. If you use the current list without plant synonyms, it's not a problem. However, if you wish to include plant synonyms, you may have to limit your list to a particular state.  To download another state (or to update the LIST worksheet in any of the workbooks), follow these instructions:

1. You'll need to download the list of plants from the USDA PLANTS database:
• Click here to open the link to the plants database with the correct settings selected.
• Click on "Review Selections or Sort Report"
• Click on "Modify Selections"
• Select the state you want plant info for or select other options to display.
• Click on "display results" at bottom of the screen. 
• On the new screen that comes up, click on the word "download" in small print on the upper right-hand portion of the screen, next to "Printer Friendly" 
• A new screen will appear with comma-delimited text. 
• Save the webpage as a text file.  There are many ways to do this, one way is to copy all the text, open notepad, paste the text, and save the file as a ".txt". 
2. Import plant list into Excel 
• Open Excel
• Click on "File" and "Open"
• Be sure the type of files being displayed is "txt" or "all files"
• On the first, "Step 1", screen be sure the circle or box next to "Delimited" is marked. 
• On the second step scree, be sure there's a check mark next to "comma" and the others are unchecked.
• On the final screen, click on "finish".
3. Copy all the plant codes and other data into this spreadsheet's "LIST" tab.
• On the plant list you imported into Excel in the last step, select all cells (or press  "Ctrl + a"), then press the copy button (or "Ctrl + c").  This will highlight and copy the entire worksheet.
• On the "LIST" tab of this workbook, press "Ctrl + a" and then either press the paste button or "Ctrl +v".
• Re-save the "Plant Code Lookup" file with the new state name abbreviation at the end.

You're ready to go!

Note: using the Excel 2003 version, plant families are not downloaded so will not show up on the code lookup page.


Review other sections of this topic:

• Introduction: Replace Plant Symbol Codes with Common Names, Latin (Scientific) Names, or Family Names (with and without Excel macros).
• Part 1:  Copy and paste plant symbol codes into Colin's Plant Code Lookup Excel spreadsheet tool to convert them into scientific/latin names, common names, and families.
• Part 2:  Use Colin's Plant Code Lookup Excel macro to convert plant symbol codes to scientific/latin names, common names, and family names.
• Part 3:  Customizing Colin's Plant Code Lookup Excel spreadsheet with individual states or custom lists.

===================================================================
Also, see my blog and free download to convert AOU bird species codes into common name, scientific name, or family name.  The tool also helps sort bird lists in phylogenetic order.

Part 2: Using the USDA Plant Code Lookup Excel Macro

Edit 4/25/2013:  I updated all spreadsheets to be able to convert Scientific Names to Accepted Plant Symbols, which was a requested feature.  In order to do this, I had to eliminate plant synonyms from the data list. But, I added a few other columns such as Family Common Name, Family Symbol, and Order.  The images below look slightly different than those in the updated spreadsheets.  - Colin

If you downloaded the version of the plant code replacement spreadsheet with macros, you can use shortcut keys to convert between USDA plant codes, common names, latin names, etc.

To see if you have the version with macros, go to the "View" tab of the ribbon and click on "Macros". If "plant_list" macros show up, then you have macros.

The most useful use of this worksheet with macros will be to convert plant symbols in your datasheets with common names, family names, or latin names.  To do this, do the following:

1. Have the macro-enabled "Plant Code Lookup" file open at the same time your excel worksheet with plant data is open. (Be sure to enable macro content if your security settings are medium or high).
2. Highlight the columns or cells with plant symbol codes in it.
3. Press one of the shortcut keys to replace the code symbol with another name, as identified below: 

• Ctrl+Shift+C = replace code with common name 
• Ctrl+Shift+F = replace code with family name 
• Ctrl+Shift+L = replace code with latin name  

Another use of the macros is to replace latin names with common names or replace common names with latin names. There are limitations to the use of these commands as noted below:

• Ctrl+Shift+O = replace latin with common name.  NOTE:  The entire latin name needs to be written exactly the same as in the "LIST" tab, including the variety and "authority" name.  Suggest you customize the "LIST" tab to your needs.
• Ctrl+Shift+A = replace common name with latin.  BEWARE: there are common names cross-reference with multiple latin names.  Suggest you customize the "LIST" tab to your needs.

Use the "Macro Test" tab of this worksheet to test how this function works before you do it on your personal data.  You cannot undo changes made by a macro command!

Another note:  If you have another workbook with macros in it that use the same shortcut keys that are used in this macro workbook, pressing the shortcut keys run the other macro.

In this example the rose color columns (A through D) are originally entered  plant species data.  Greenish columns (F thru J) are data altered with macros and ready for pivot tables or sorting. The macro to automatically fill-in the blank cells in columns F and G is found in the "BirdMacroLibrary.xlsm" spreadsheet.

Move to the Next Section (Part 3):

• Introduction: Replace Plant Symbol Codes with Common Names, Latin (Scientific) Names, or Family Names (with and without Excel macros).
• Part 1:  Copy and paste plant symbol codes into Colin's Plant Code Lookup Excel spreadsheet tool to convert them into scientific/latin names, common names, and families.
• Part 2:  Use Colin's Plant Code Lookup Excel macro to convert plant symbol codes to scientific/latin names, common names, and family names.
• Part 3:  Customizing Colin's Plant Code Lookup Excel spreadsheet with individual states or custom lists.

Part 1: Copy and paste plant symbol codes into Colin's Plant Code Lookup Excel spreadsheet tool to convert them into scientific/latin names, common names, and families.

Edit 4/25/2013:  I updated all spreadsheets to be able to convert Scientific Names to Accepted Plant Symbols, which was a requested feature.  In order to do this, I had to eliminate plant synonyms from the data list. But, I added a few other columns such as Family Common Name, Family Symbol, and Order.  The images below look slightly different than those in the updated spreadsheets.  - Colin

After you download the appropriate plant code converter spreadsheet that you need (described in the Introduction to Plant Code Lookup and Replacement page and depends on if you have Excel 2003 or 2007/2010 and if you wish to have the macro features or not), you'll find the following tabs along the bottom of the worksheet (Fig. 1):

Figure 1. Click on image to enlarge. Some versions of this plant workbook may not have all the tabs shown.

1. Click on the "Symbol Lookup" tab (3rd tab from left).  The page should look like this (Fig. 2):

Figure 2.  Example of pasting plant symbol codes into the yellow cells of the "Symbol Lookup" tab to convert them into scientific names, common names, family names, etc.

2. Enter your 4 letter codes into the yellow cells on the left.

The first six yellow cells have example data entered into them and these can be deleted or replaced.  You can copy/paste long lists from another worksheet or enter them individually.  It doesn't matter if you use Uppercase or Lowercase characters.  Codes must be entered exactly as they are listed in the plants database or you'll get a "N/A" error or you might lookup the wrong plant.

3. Copy and Paste the plants common names, family names, or latin names back to your working document.  If pasting into another Excel worksheet, be sure to use "Paste Values" (shortcut keys "alt," "e," "s," and "v").

Custom Code Lookup/Replacement

If you choose to NOT use USDA plant codes in favor of your own list of custom codes, use the 3rd and 4th tabs.  First, enter your custom symbol codes and names on the "LIST2" tab, then use the "Custom Name Lookup" tab for all future lookups or code replacements.

Notes:

• The spreadsheed only allows for 200 codes to be entered.  More lines can easily be added by copying   and pasting additional rows.
• To edit the "Symbol Lookup" worksheet, you have to unprotect it first.  In Excel 2007/2010, this under on the "Review" menu.
• One useful way to use this tool would be to do your data analysis, summaries, or pivot tables using the codes and for the final table, copy and paste the list of plant codes into the Symbol Lookup worksheet to get the common names
• To paste the scientific names or other data into another spreadsheet, you have to use "paste values":

1. Highlight and copy the information (scientific names, common names, family names, etc) you want. 
2. On your new spreadsheet, select the cell where you want to paste the info 
3. Under the paste menu, select "paste special", and then select "values".
• Or, do the following shortcut keys: 
•  i. hit the "alt" key 
•  ii. hit the "e" key 
•  iii. hit the "s" key 
•  iv. hit the "v" key

• The Excel 2007/2010 versions of this spreadsheet (has a .xlsx or .xlsm extensions) have all plant species in the list from the USDA Plants Database (downloaded from the USDA Plants Database on June 17, 2011)  

Move to the Next Section (Part 2):

• Introduction: Replace Plant Symbol Codes with Common Names, Latin (Scientific) Names, or Family Names (with and without Excel macros).
• Part 1:  Copy and paste plant symbol codes into Colin's Plant Code Lookup Excel spreadsheet tool to convert them into scientific/latin names, common names, and families.
• Part 2:  Use Colin's Plant Code Lookup Excel macro to convert plant symbol codes to scientific/latin names, common names, and family names.
• Part 3:  Customizing Colin's Plant Code Lookup Excel spreadsheet with individual states or custom lists.

Introduction: Replace USDA Plant Symbol Codes with Common Names, Latin (Scientific) Names, or Family Names (with and without Excel macros).

Introduction:

Here's a tool that I created for research and monitoring data that botanists or other outdoor professionals may find useful.  It's often convenient to use codes for recording field data because it takes up less space on your datasheet and less time.  Codes are also faster to enter into the computer when data entry time comes around.  However, if you use MS Excel to enter data with and then wish to produce a table for a report, presentation, or publication, you'll have to convert those codes into common names and/or latin names to make them more readable.

A common code system for recording plant names in the United States is used in the USDA NRCS PLANTS Database.  Plant species "symbols," as they're called, are comprised of the first two letters of the genus, followed by the first two letters of the species, the first single letter of the variety name (if present), and sometimes a tie-breaking number*.  Many vegetation monitoring projects that I've been involved with use these symbols to record species names in the field and in databases.

I developed Excel spreadsheets to convert these plant symbols into common names, Latin (scientific) names, and plant taxonomic family names to help in the reporting and organization of plant data.  You can convert lists of symbols by copying and pasting your data into my downloadable worksheets, or use a macro to convert symbols directly on your own Excel worksheet.

Use the following links to learn how to use my spreadsheet tools, or read the section below to learn about how to download the spreadsheets and which version you want.

Part 1:  Copy and paste plant symbol codes into Colin's Plant Code Lookup Excel spreadsheet tool to convert them into scientific/latin names, common names, and families.
Part 2:  Use Colin's Plant Code Lookup Excel macro to convert plant symbol codes to scientific/latin names, common names, and family names.
Part 3:  Customizing Colin's Plant Code Lookup Excel spreadsheet with individual states or custom lists.


Where and What to Download:

On my downloads page, you'll find four versions of the Plant Code Lookup spreadsheets.  To decide which version you want or need depends on which version of Excel you have (2003 or 2007/2010), and whether you trust downloading Excel spreadsheets with macros or not.  You can read about macros by clicking here, but in short: My macro spreadsheets offer nice features that the non-macro versions don't, but some folks don't trust downloading spreadsheets with macros because they can potentially carry viruses with them.  I can give you my word that the spreadsheets were virus-free at the time that I uploaded them and have taken modest precautions to keep the files safe, but downloading and use of these spreadsheets is otherwise your choice and it's your responsibility to keep your computer safe by maintaining anti-virus software, etc.  You can read my software disclaimer on this website, contact me and ask me to email you the file directly, or download a non-macro version of my spreadsheet.  Here's a description of the four versions (all are free):

• Plant Code Lookup - all plants -with Macros.xlsm - Excel 2007 or 2010. Contains the Macros.
• Plant Code Lookup - all plants - No Macros.xlsx - Excel 2007 or 2010. No Macros.
• Plant Code Lookup - all plants - with Macros.xls - Excel 2003, 2007, or 2010. Contains Macros.
• Plant Code Lookup - all plants - No Macros.xls - Excel 2003, 2007, or 2010. No Macros.

The Excel 2007/2010 worksheets are labeled with "all plants" because they contain all the plant codes in the US that were in the PLANTS database as of August 2011.  The 2003 version doesn't allow that many lines of data, so only one US state's plant list, New Mexico, is included.  All of the spreadsheet include all plants in the USDA PLANTS database, but do not include plant synonyms.  I needed to eliminate synonyms in order to add the ability to convert Scientific names to symbol codes, which was a requested feature.

Click on the button below to access the downloads page for spreadsheet tools, Excel macro libraries, etc. (or get general info about my natural resources downloads here). 

Example of Use:

In this example the rose color columns (A through D) are originally entered  plant species data.  Greenish columns (F thru J) are data altered with macros and ready for pivot tables or sorting.

====> Go to Next Section (click here).

* USDA, NRCS. 2011. The PLANTS Database (http://plants.usda.gov, 11 August 2011). National Plant Data Team, Greensboro, NC 27401-4901 USA.

===================================================================
Also, see my blog and free download to convert AOU bird species codes into common name, scientific name, or family name.  The tool also helps sort bird lists in phylogenetic order.

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.

Opening Excel 2007/2010 in New Windows for Multi-Monitor Support

If you don't use multiple monitors, you might not be annoyed by this change in post-2003 versions of Excel.  However, if you like to open Excel spreadsheets in separate windows, say, for sending them to different monitors, you've probably beat your head against your Biltmore stick wondering why the new versions of Excel don't allow this.  By separate windows, I'm not talking about being able to minimize multiple worksheets and view them with the "View Side by Side" feature.  I'm talking about opening totally different instances of Excel.

~~~~~~~~~~~~~~~~~~~~~~~~

Edit Dec 23, 2014: Recently I found another easy way to open multiple Excel windows in Office version 2010:  Simply shift-click on the Excel icon in the start menu or the task bar and another instance of Excel will open.  You can then navigate to a specific spreadsheet to open. You can't shift click on a spreadsheet file itself, so in some ways I find it easier to use the method below.

~~~~~~~~~~~~~~~~~~~~~~~~

The good news is that there are ways to accomplish this.  By far the easiest method that I've found so far is to add an Excel.exe shortcut to the "Send To" option of the context menu (it's an option that shows when you right-click on a file).  To do this, follow these instructions:

1. Open My Documents or My Computer
2. In the address box, type: %APPDATA%\Microsoft\Windows\SendTo
    Or you can navigate to something similar to:
    C:\Users\Colin\AppData\Roaming\Microsoft\Windows\SendTo
3. Open another instance of My Documents and navigate to your MS Office folder to locate EXCEL.EXE.  

    For me, it was in this folder:  C:\Program Files (x86)\Microsoft Office\Office12

4. Drag "EXCEL.EXE" into the SendTo folder.  This will create a shortcut to Excel in that folder
5. To open an Excel file in a separate window when there is already one open, right-click on the .xlsx or .xls file, hover over "Send To", and then select "Excel".

This unfortunately doesn't work for me if I'm opening a spreadsheet that's attached to an email since the "Send To" option isn't there, but it's really freed up my ability to manage my Excel windows over two monitors.

As a side note, I've also found a very useful free application that creates "Tabs" for quick switching and organizing of mutliple spreadsheets, Word documents, or PowerPoint presentations at the same time.  It's called OfficeTabs, found here (translated to English).

If you like this tip, click on the "+1" to show your support or leave a comment!

Copy & Fill Data Down Columns in Excel (Macro & non-Macro)

This is one of the features I've found useful many times over the years, so is one of the first tips that I'll introduce and will be the first macro that I'll cover on this blog.  To learn more about macros, click here.


THE NEED:


Do you ever have the same data to enter over and over and over and over again?  For instance, for a weather description column for Las Cruces (NM) in June:  Sunny, Sunny, Sunny, Sunny….

Or, how about multiple columns or a whole row of data that needs to be repeated for tens or hundreds of rows?  It's often useful and necessary that each datum (i.e. species and number detected) have all ancillary data associated with it (i.e. habitat type, plot #, date, soil type, etc).  This data format is necessary in order to use Pivot Tables, to export into SAS or other statistical software, etc.

For instance, in the example below each individual datum is a species and number detection and ancillary data are unit, habitat, plot #, and time.  To speed-up data entry, I often only enter data in rows when the value changes.  Here, I entered the unit name and habitat type only once for two avian point counts since they were the same.  I also only entered the plot number and time once for each plot. This isn't just lazy, but helps eliminate spelling errors that can cause data analysis problems later.  Now I want to copy all the unique data values and paste it so that it fills the blanks until the end of the dataset.

This entry explains how to easily copy data and fill blank cells below it in columns. I'll explain a way to do it without and with a macro. 

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.

Having problems using Excel Macros?

If you're having trouble using macros in my macro library or other Excel spreadsheets, check the following about enabling macros and setting your Excel security settings.  Instructions are given for the 2003, 2007, and 2010 versions of Excel.

About Excel Macros

Macros in Excel are "little programs" that do things to data, retrieve data, generate data, or just about anything else in Excel.  You can automate just about anything in Excel using macros and assign that macro a shortcut key, which is useful if you have repetitive actions that you do frequently.  You'll find some useful macros for wildlife, ecology, and other monitoring or research data that I've already created and saved to my "Colin's Macro Library.xls" file.  Most of the macros in the worksheet have shortcut keys and are ready to run once the file is open on your computer, and the macros can be run in any Excel spreadsheet that's open on your computer as long as the macro library is open at the same time.

Below, I'll walk you through how to write your first macro.  The example will allow you to convert acres to hectares with the push of a shortcut key.

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/

Introduction

I'm intending to use this blog to share some tips and tricks I've learned along the way about managing natural resource research and monitoring data. I've slowly picked up some tricks about using Excel, ArcGIS, Google Earth, and other applications that have made my data management and analysis easier and more efficient. It's less about data analysis (consult a statistician for that) than how to efficiently enter, manipulate, and organize data in a useful way.  By no means am I an expert at any of these applications, but I've gained enough knowledge to use them as tools to do what I need them to. That means I get to spend more time in the field and less behind a desk, right? While this is not the typical "glamorous" part of working as an outdoor professional, I hope this information will be useful for those of us field biologists with "average" computer skills who have to occasionally deal with lots of lines of data.  The "tips and tricks" in this blog pertain specifically to wildlife, range, vegetation, botany, forestry, ecology, and other "-ology" data.  You can use the links, search, archive, and "topic" labels to the left to peruse this blog.  I started taking these notes for my own benefit of remembering new things that I learn, however if you find a use for this information, please enjoy and let me know!




Stuff in the works:

• A discussion forum where all of us can share, discuss, and learn new data management tips.
• Automatic converter of 4-letter AOU codes (bird species codes) into common or scientific names. (Done! Click here for bird species code converter and phylogenetic sort order tool.)
• Automatic assigning of bird family names to lists of species. (see link above)
• The same two (+) things for North American plants.
• Automatic re-ordering lists of species by phylogenetic order (useful for producing tables for reports or publication)
• USDA Plant species code converter.
• Nested "IF" statements.
• "Cash register" and inventory system for non-profit organization (NGO) fund raising use (kinda pushing the limits of a natural resource data-oriented blog).
• Other stuff:  If you have a request, let me know and I can try to provide a solution.  No guarantees, though (see disclaimer).