In PART 1 we made some simple square blocks and then combined squares into representations of common garden box sizes for square foot gardening.
To download a finished garden planner complete with video tutorial go to this page – Updated 2014
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.
I utilize a modified for me version of Square foot Gardening. This method allows for very high yields in a very small areas. There are very strong advantages to this method and it is a must for people with limited space and time. By my estimations I can supply a family of five with fresh vegetables, preserves and frozen vegetables for nearly an entire year on less than 1000 square feet (excluding grains).
The primary advantages of this method are:
- Easy weeding (vegetables actually shade out weeds)
- No rototiller (once the soil is productive a simple rake/hoe)
- Customizable soil mixtures per bed
- Greater productivity/yield per square foot
- Alot less water (plants shade themselves to keep in moisture)
- Beds warm quicker so you can plant earlier and extend your season
- Minimal thinning (plant fewer seeds)
- Easy pest control and mitigation
- Can quickly and easily change soils/plants
If you are interested in this gardening method, the above linked book provides all the basics, rules and information.
A Great companion book which deals with the vertical aspect of gardening, increasing yields and minimizing space even more.