Tree Planting - sort your data base to list planting sites.

Importance to Master Plan

• Your community may set priorities for choosing planting sites based on;
  1. Streets without shade,
  2. Residential areas near commercial or business districts,
  3. People that want a tree. Note: you can send a letter to potential homes with planting sites. State that they have been identified as a planting site and should call if they have questions. After planting, follow-up with property owner by giving the name of tree and care instructions.
• Your master plan may state, "planting sites prioritized by, 1. streets with little shade, 2. streets near business districts, 3. homes with people that want, and will help maintain newly planted trees."
  

Using Excel
The following tutorial teaches you how to utilize the data in your inventory. The fields of data used in the examples below are based on the inventory forms available at the Tree Forms Inventory Template Download. See a sample Tree inventory workbook (xls).

If using the Pendragon Forms template downloaded from these web pages, download this excel worksheet TreeInventoryTemplate (xls) and import your data directly into it. See a Sample Inventory (xls) in this format.

Filtering Data - to find all trees with a common feature. Filter your tree inventory by field to get a selected list. Such as, trees needing consultation, where planting sites are, etc.

1. Click on Data at the top of the page, highlight Filter, then move across to the pop-up window at right and select AutoFilter. This allows you to select any entry in the column to create an exclusive list.
2. Ex. Under the Consult column click on the arrow to expand the Pop-up menu, then choose Y. Only the records that were recorded as Yes for consult will remain.
3. Many utility companies will be happy to look at trees needing consultation that impact 3-phase utility lines. To create a list for the utility company, use the list sorted for "consult" and under the "wires" field, select "3-phase". The resulting list will be of trees needing consultation under 3-phase wires.
4. Go back to Data, Filter, then ShowAll to return all records to view.

Making Pivot Tables - Pivot tables allow you to summarize values within a data field. Pivot tables will be used for finding Species Distribution, Size Class Distribution, Tree Condition, and Percent Stocking.

1. Click on Data at the top of the page, and select PivotTable and PivotChart Report...
2. Under Where is the data that you want to analyze? select Microsoft excel list or database. Under What kind of report do you want to create? select PivotTable. Click Next.
3. In the next step, the range should be the entire table (by default you will see a moving hyphenated line around the table). Click Next.
4. Under "Where do you want to put the Pivot Table report" select New Worksheet, then click Finish.
5. Under PivotTable Field List, select and drag the desired field (Ex. common name) to the Drop Row Fields Here. Go back to the PivotTable Field List, select and drag the same field (Ex. common names) to the Drop Data Items Here. You should now see a list of trees by common name and the quantity of each in the data field.
   Select one of the following fields to establish the corresponding data set:
   
   • common name - Species distribution
   • DBH - Size class distribution
   • condition - Tree condition
   • planting sites - Percent stocking
6. Double click on the cell (3A above) Count of Common name, select options, under show data as, select % of total. Under name, type "Percent of total", then click OK.
7. To change to data order (highest value at top) RIGHT click on attribute in A column (Common_name) and select field settings. Click on advanced, then under Autosort options select Descending. Under Using field select Percent of total. Click on OK until back to the table.
8. Click on the Chart Wizard icon to view the data in a table.

© Copyright, Department of Horticulture, Cornell University.