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.