This is a step by step guide as to how I originally created the excel worksheet which acts as my garden planner.
It requires some experience with excel and basic functions in excel.
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
- Draw a rectangle somewhere
- Right click on the rectangle
- Choose format autoshape
- Size tab: set height and width to same value (.3″ I’ve used”).
- Properties tab: check “Don’t move or size with cells” and press OK.
- Move rectangle to top right cell square with the axis (will go all the way into upper left corner).
- Click on empty box above 1 and beside A (will select all cells)
- 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
- 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).
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).
Click on a cell, drag to make a 4×8 grid (will highlight)
Right click and choose format cell
- Click on a nice thick line under style
- Click outline (will make thick outer line)
- Click on thinner line under style
- Then click inside (will make lighter inside lines)
- * 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.
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)
=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
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.
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)
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)
Thats all for this series. Will be adding articles for trellising, extending growing seasons, plant varieties and such as I have time.
EXCEL GARDEN PLANNER has been updated a bit and uploaded.
The .zip file contains the newer .xlsx (excel 2003+) as well as the .xls (excel 97-2003)
Features of the excel Garden Planner
Garden Plan overview:
Layout of garden (SFG methodology)
- Automatically calculates square footage used, available
- Automatically calculates square footage allotted per plant species
- Automatically tells you how many plants you will need
- Automatically calculates plant date, transplant date, approximate maturity date
This area is your sandbox – where you can draw your garden.
Each square represents one square foot.
The excel counters and automated features will only count what’s within the confines of these borders.
It is 100 foot wide x 40 foot high (My current area) resizing the box can be done, but may break some functions of you do not adjust the calculations.
Planting guide section
Here is where the grunt work is done – from calculating box space (available and used) to determining how many seeds/plants you need and even works out a planting schedule for you.
At the top of the planting guide are the dates.
The Last Frost and First Frost dates must be added here. Use the current year.
It will calculate your grow days and days remaining (days until season is over)
These dates are used for most of the calculations in the planting guide.
The Planting guide columns:
# squares : Calculated when you add a box and assign it a 1 (for empty) or plant code.
Per square: These are the standard or modified SFG plants per square foot – easily found by Googling the plant name square foot spacing. I Use trellis’s and due to my northern climate some of my numbers are not standard.
Code: Simple 2-3 letter code for each plant – this is what will “count” for all the automated features. I also colour code my boxes for quick reference – simply highligh the box and change the background colour.
Plant Name: Well this is where you put the plant name
Total plants: This is calculated from the count of the squares per plant and the Per square allotment.
Transplant: For my own personal use as I just want to know what I am transplanting
Planting (weeks to frost): Some seeds need to be planted indoors or can be planted outdoors before the last frost date. This is where you enter the weeks before last frost you can plant (transplant or direct in garden). Use negatives to denote
Plant by: This calculated field simply subtracts the planting weeks from the last frost date. This is the estimated planting date.
Germination days: Some seeds have this, some don’t.
Days after frost plant: Some plants need to be transplanted after the risk of last frost has passed or germination requires a warmer temperature.
Transplant on/by: Calculated value, I Only use this for transplants – but could be for any seed planted after the last frost.
Maturity: Plant maturity – usually on seed packets
Done by: Calculated estimate of the done growing date (estimated harvest).
Last Plant Date: This is the estimated date by which you could conceivably still plant (Based on Maturity and First frost date). Useful if you have something that died off after planting or have picked already.
Square Foot Gardening
Information and resources for square foot gardening here in NW Ontario.
Excel Garden Planner
An excel worksheet I developed to help plan and track my square foot gardening.
North Hardy Plants
Not all plants will grow here, but a lot will.
Square Foot Gardening
Square Foot Gardening
Square foot gardening in raised beds allows for a relatively maintenance free garden. It also provides that extra growing time so essential here in NW Ontario. Square foot gardening is demonstrated throughout the gardening section as it is the way I grow my gardens.
Advantages to Square Foot Gardening