SARE » Products » Crop Rotation on Organic Farms » APPENDIX 6: Linking a Field Map and Spreadsheet in Microsoft Excel

Crop Rotation on Organic Farms

APPENDIX 6: Linking a Field Map and Spreadsheet in Microsoft Excel

SARE Outreach
Charles L. Mohler, Sue Ellen Johnson | 2009 | 154 pages
PDF (2.0 MB)
Order in Print $24.00

or call (301) 779-1007 to order.

Crop Rotation publication cover

by Jody Bolluyt, Peter Lowy, and Charles L. Mohler

Creating management maps of your fields using Excel is easy and allows you to link a spreadsheet similar to tables 5.2 and 5.3 to the maps. Once you have a map and spreadsheet set up in a workbook, entering crop names on the spreadsheet causes them to appear on the map at the same time. The map and spreadsheet can also be set up to place additional information, like target planting dates, on the map. Updating the map as the season progresses is as simple as entering the changes in the spreadsheet. Moreover, for subsequent years, you can duplicate the workbook, rename it with the appropriate year, and enter the crops for that year into the spreadsheet. The map has to be created only once.

A sample map and spreadsheet called Example_field_map_07 can be downloaded from www.neon.cornell.edu/croprotation/index.html. An example of a field map in use on a real farm can be viewed at www.roxburyfarm.com and click on “Roxbury Farm Manuals.” In the section “Mapping of Crop Rotations” click on either sample database to see the table or sample map to see the map. The web site does not support the dynamic linkage between the table and map that you can achieve on your own computer, so click “No” when asked if you want to update linked information.

To Create the DATA Sheet:

  1. Open a new Excel workbook and save it with an appropriate name.
  2. To name the first sheet of the workbook, double click on the Sheet1 tab at the bottom of the page and name it Field_nameDATA (for example, NorthDATA).
  3. At the top of the table enter your column headings. In the example, they are Section, MU, Crops, Date, and Date2.
  4. Enter the data for each management unit, or copy and paste columns from the FieldWork1 spreadsheet if you are following the detailed planning procedure.
  5. Columns containing dates need to be formatted: Select those columns, go to Format menu, choose Cells, click on the Number tab, choose Date, and select the date format you prefer.
  6. If you break up crop fields into sections that are physically separated by driveways, as shown in the example, leaving blank lines between sections on the DATA sheet will make using the workbook easier.

To Create the MAP Sheet:

  1. Click on the Sheet2 tab at the bottom of the page to go to that sheet. Then double click that tab to select it, and enter the name Field_nameMAP (for example, NorthMAP).
  2. Select cell C3 and in the formula bar (a toolbar available under View that appears above the workbook) enter =Field_nameDATA!A2 and click on the checkmark in the toolbar. The value that appears in cell A2 of the DATA sheet should appear. Leaving rows 1 and 2 and columns A and B free gives you room to draw in landmarks later.
  3. Including cell C3, choose as many cells to the right as you have data that you want to include on the map. In the example, we selected Cells C3–G3. Then hit Ctrl-R to fill those cells.
  4. Select enough cells down to cover the number of rows in your DATA sheet, and hit Crtl-D to fill them. Delete any zeros that appear where data are absent on the DATA sheet.
  5. Outline the sections and management units using the Outline box on the formatting toolbar, or choose Cells from the Format menu and select Borders. First outline a section (a group of management units) with a thick line, then select the rows within that outline and underline them with a thin line.
  6. Adjust the column widths (Columns on the Format menu) until all of the information is visible and pleasantly spaced. Widen or narrow the column containing the crop species information to make the management units appear more or less the shape they are in the field. If necessary, adjust the row height (Rows on the Format menu) to help get the shape right. You will want the map to print nicely on one or possibly two pages, and this may also require adjusting column widths and row heights. If necessary, change the font size in some columns to make the information fit. Use Print Preview on the File menu to see how the map will appear on the printed page.
  7. PC users: Go to Tools on the menu bar and select Options. Then click on the  Gridlines  box to make the checkmark disappear. This will eliminate the pale gridlines, so that the page looks more maplike. Mac users: Go to Excel on the menu bar and select Preferences. Verify that View is  highlighted among the options on the left. If not, click View. Then, click on the box next to Gridlines to make the checkmark disappear. Click OK.
  8. Finally, use the Borders and Patterns  tabs within Cells on the Format menu to add landmarks like roadways, hedgerows, etc.

Note that maps of many fields can be coordinated with a single DATA sheet that contains information on all management units on the farm. To make additional maps, click on the Sheet3 tab at the bottom of the page. Then choose Worksheet on the Insert menu repeatedly to create as many additional sheets as you have fields to map. For each field, follow the instructions above for making a map.