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.