How the Excel file is put together

Step 1: Open a new Excel worksheet.

Step 2: If the drawing toolbar is not already turned on you can turn it on by

clicking view>> toolbars >>Drawing (check)

Step 3: Making a grid

Since square foot gardening is based on the square foot, a grid of equal height/widths is more representative for layout purposes. By making a proper square grid where one square in excel directly correlates to one square foot in the garden.
Select rectangle on drawing tools

1. 1. Draw a rectangle somewhere
   2. Right click on the rectangle
   3. Choose format autoshape
   4. Size tab: set height and width to same value (.3″ I’ve used”).
   5. Properties tab: check “Don’t move or size with cells” and press OK.
   6. Move rectangle to top right cell square with the axis (will go all the way into upper left corner).
   7. Click on empty box above 1 and beside A (will select all cells)
   8. Hover mouse over the grid line between A and B on top row,
      when move grid icon shows click and drag the line to square with the rectangle
   9. Repeat for the grid line between 1 and 2

You now have the appearance of a square (1 square = 1 square foot).
You can highlight and delete your drawing rectangle.

Step 4: Choose and/or indicate north for layout (Top of page is easiest).

N
W           E
S

Step 5: Making a box:
Simply select and drag in any cell will make a box of whatever dimensions you want (1 square = 1 square foot).

4×8 box:
Click on a cell, drag to make a 4×8 grid (will highlight)
Right click and choose format cell

Border tab:

1. 1. Click on a nice thick line under style
   2. Click outline (will make thick outer line)
   3. Click on thinner line under style
   4. Then click inside (will make lighter inside lines)
   5. * You can add diagonal lines as well

Step 6: Layout your boxes / Garden

Using your north/south/east/west directions and assuming square boxes, and the above procedure simply make your total garden layout. Leave two squares open between boxes at least and follow standard square foot gardening “rules” where possible.

Check Your pages

At this point in time it is a good idea to see what the page/pages will look like printed out. This is accomplished simply by clicking file >> Page Setup and pressing OK in the upper left menu corner. The page individual pages (as seen by a printer) will show as dotted lines outlining the page. Its important that you spread box areas as well as legends so the do not split across page breaks.

You can change the page size and/or orientation as well. I find horizontal pages better display than vertical.

So now you have some boxes drawn in excel, and know where north is, now what?

Well it is a garden plant so time to plant some seeds, virtually.

What To Plant

First you need to know what you want to plant and generally have an idea what goes where. Tall plants on the north side, bushy plants on outer edges, plants that don’t like each other separated. There are rules some can be ignored/modified but usually its best to stic to the basic rules.

How many plants per square foot is determined by the seed spacing on the seed packet. If the seeds are 3″ inches apart, then in a 12″ by 12″ box you can have 4 rows and 4 columns which euals 16  seeds per square.

Make a legend

Making a legend:  Goes on different page (from page setup)

On the excel worksheet you created you blank boxes in, scroll down to make sure you are below the bottom most box with a few empty rows between.

You will need to make the following columns by merging squares in a horizontal row.

To merge, select 3 or 4 or 5 cells horizontally, then click the merge icon n the toolbar. Repeat for additional columns.

          *Resizing cells (columns) will distort the boxes above.

One you have merged the the cells in a row, by clicking the mouse and dragging down you can “merge” the cells underneath – make enough rows underneath to accomodate all your plants.

I recommend the following headers for your columns:

# Squares;Per square;Plant indicator;Plant name;Total plants

• • # squares:  this is the number of squares allotted to this specific plant overall
  • per square: the number of plants per square as per the rules
  • Plant indicator:  this is just a simple letter to differentiate different varieties of plants (i.e. for roma tomatoes, c for cherry tomatoes, etc….). Plants will be also differentiated by cell colour. Use an arbitrary number to indicate blank squares
    (will become useful soon)
  • Plant name: self explanatory
  • Total plants: will multiply # squares by per square for a total plant count (need to know how many seeds to buy/plant

You can now add your plant names, fill out the per square number (based on square foot gardening spacings). To choose a colour code, simply highlight the cell you want to change colour , pick a colour on the fill button and press OK.

Make sure your plant indicator has a text indicator as well. Personally I use the colour for the types of plant, then the text value to indicate the different varieties. I.e. tomatoes are yellow, however ct = cherry tomatoes, r=roma tomatoes….
A blank legend item with say a “1″ will indicate a blank square.

This is the same method used to fill in corresponding squares in the plant boxes.

Make it pretty

With some basic cell formatting it will lake the legend and even the boxes a little more bearable.

Highlight the entire legend (or garden box area or everything)
> Right click >> Format Cells

Under the Alignment tab you can set the horizontal and vertical alignment to center which will center any text in the cell in the middle. You can also choose to wrap text, shrink to fit… etc.
The Border tab will allow you to add gridlines and borders of varying widths – play with it and see what looks pretty to you.

You should have a legend which looks similar to this:

With only the Per Square, Plant indicator and Plant name columns filled in.

Now some math.

The # squares legend item can either be filled in by hand OR you can let excel do all the work.

If your plant boxes have the text indicator in the boxes (use “1″ for blank as well). you can utilize the excel COUNTIF() function (not as hard as it sounds).

The COUNTIF(range, criteria) is a built in excel function that will count all cells within the range of cells which fit the required criteria.

The range is simply the entire area of your garden boxes (can be across multiple sheets/large areas or individual boxes).

Write down the topmost left corner cell ID (ie A1) and the bottom most right corner (ie X40) of a box that covers your entire garden box layout.

The criteria is your text indicator. Write down the cell ID of your first plant indicator.

OK, now highlight the first cell under # squares, in the function bar (just below the menus)

type:

=countif(cell id of top left corner, cell id of bottom right corner, cell ID of plant indicator)

ex: =COUNTIF($C$4:$X$12,M28) where $C$4 = top left corner, $X$12= bottom right corner and M28= plant indicator in legend.

The $ signs before the letters and numbers (cell ID’s) will tell excel to make thos values constant (your box area will not change).

After you press enter and dont get an error, you should be presented with a number. Check your boxes and see if it has counted your boxes properly. (You absolutely MUST have the text indicator in the plant box for it to show up).

If all works click on th cell, click/drag the bottom right corner of cell to cover the rest of the legend items. Now you should have an accurate count of squares used for each plant.

The remaining column total plants is simply the above column ( # Squares multiplied by the per square column).

In the first cell below the total plant type:

=cell ID of the corresponding # squares * cell ID corresponding per Square

ex: =C28*H28

This should multiply those two numbers together and tell you how many plants you will need. After you confirm the math, click and drag as you did with the COUNTIF function to fill in all the total plants.

Now you can change things around in the garden box areas and the the legend information should automatically update.

Frost Dates

To determine your local frost date you can simply Google “frost dates“

Links to frost dates:
Vesseys Seeds: http://www.veseys.com/ca/en/learn/reference/frost/canada
Canada Farmers Almanac: http://www.almanac.com/content/frost-chart-canada
USA Farmers Almanac: http://www.almanac.com/content/frost-chart-united-states

Adding your nearest home city to frost dates google search may narrow down it for you.

For my fost dates I use a last frost date of June 1 and a first frost of September 7 for a whopping 98 day growing season. This short growing season reduces what I can plant and increases the risks (has been known to snow in early July but i haven’t seen it).

Adding frost dates to your garden plan

Once you have these dates find a spot below your legend for your garden you did in the previous step. Merge some cells if you need to, but make Last Frost Date, First Frost Date and Growing Season headers.

The frost dates need to be in a date format. Select the two cells which will hold the dates, right click >> Format cells. Under Number tab, select date and then a date format which appeals to you. You can aldo change alignments and such while you are here. Click OK.
Now enter the dates into the boxes.

Excel can use dates in simple math functions, so to determine your growing season simply highlight the growing season cell, in the Function bar type:

=CELL ID of last frost date – CELL ID of first frost date ex. =F28-F27

These dates will be reffered to by other excel functions so don’t move them once they are done and correct.

Now we need to make a planting/growing schedule.

Make a growing schedule

For the planting schedule we need the following headers:

Plant Name:   Same plants as listed in your legend (can be cut/pasted for simplicity.

Transplant:    Yes or not – just indicates if this will be a transplant

Planting (weeks):  This is the planting weeks before/after the first frost date. Usually found on the seed packets or a few google searches should tell you. This is usually in weeks so I have formatted the math for working with weeks. Negative values indicate weeks before first frost which usually means transplants. Fill out this column for all desired plants.

Plant By: This is a calculated field  wich will simply add the planting weeks to the first frost dates. This column needs the cell properties changed to date.

Use the following formula:

= CELL ID of last frost date + CELL ID planting (weeks) ex: =$F$27+P42*7

The multiplication *7 at the end is to convert weeks to days, if you used just days in planting weeks – you don’t need the *7. Excel date math only works in days.

the $ signs in the first frost date CELL ID will make it a constant.

Now simply drag the complete cell down to fill in the entire column – should have a nice column showing when you should plant.

Germination (Days): This is also located on seed packets or found with a little difficulty online. Not overly necessary however I use it to determine an outside range for crop finish dates. This is filled out by hand.

Days after frost plant:  Some plants require a “buffer” after the frost date for planting, usually related to soil temperature, sunlight, etc.. Negative numbers indicate planting before the last frost date, usually transplants however some seeds (Peas) can be direct planted before the last frost date. This is filled out by hand.

Transplant on/by:  This should only be filled out for transplant plants (copy/pasted into appropriate rows).

For transplant only plants utilize the following formula:

=CELL ID last frost date + CELL ID days after frost plant ex. =$F$27+Y42

Again, the $ for the last frost date  to make it constant.

Once you confirm the math works, copy/paste into additional transplant rows

Maturity (Days):  This is the days to maturity given on seed packets and needs to be filled out by hand. Days to maturity is only a guide as growing conditions determine when something is finished and ready to be harvested.

Done by: Or Harvest date, is calculated by adding the plant by, germination (days) and Maturity (days).  I dont really need the germination time added in however i like a buffer so I include it. These cells need the cell properties set to Date format.

Use the following formula:

=CELL ID Plant by + CELL ID Germination (days) + Maturity (days)
ex. =S42+V42+AE42

Once the math works, simply copy/paste or drag to fill in the rest of the column. This will give you an estimate on harvest times as the Maturity time can range depending on growing conditions (hence the germination buffer).

Crop 2?: Ive included this as some growing seasons are long enough to allow for a couple of crops of specific plants (peas, beans, radish, lettuce, etc.)

This is simply calculated by adding germination (days) and maturity (days) to the dont by date. This cell requires a date format as well. You could add a third/fourth crop as well in tropical areas.

= CELL ID done by + CELL ID Germination (days) + Maturity (days)
ex. =AH42+AE42+Y42

<

Thats all for this series. Will be adding articles for trellising, extending growing seasons, plant varieties and such as I have time.