Thursday, August 11, 2011

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


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.

* USDA, NRCS. 2011. The PLANTS Database (, 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.

Saturday, August 6, 2011

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


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.