Mike's Recipe Spreadsheet
I use this Excel spreadsheet to formulate my recipes. It isn't as full-featured as the commercially available recipe formulation software packages, but it provides all of the basics -- OG, color, and IBU estimation -- to allow you to easily formulate your own recipes.To download the spreadsheet, right-click here, then save the file to your hard drive. The spreadsheet should be usable with Microsoft Excel (97 or later), as well as the freeware OpenOffice.org org office suite.
Using the spreadsheetInformational fields are highlighted in gray; these fields are optional. Input fields which are used in the recipe calculations are highlighted in blue. The estimated OG, IBUs and color calculated by the spreadsheet are highlighted in red (at the bottom of the spreadsheet). I suggest you refrain from changing any of the values or formulas in any of the other cells of the spreadsheet, unless you understand what they are for!
As you enter your recipe information, the estimated OG, IBU and
Color numbers at the bottom of the spreadsheet will be automatically
updated. You can then tweak the ingredient types and amounts, to arrive
at the OG, IBU and Color values that you want. Ranges of OG, FG, ABV,
IBUs and color for each beer style are available at the BJCP web site.
Generally what I do is work out the recipe using the spreadsheet,
then print a hardcopy before I start to brew. I then write my mash
schedule, and any other information I want to keep track of (e.g. yeast
strain used, fermentation temperatures) on the back of the hardcopy.
The hardcopy goes into my brewing notebook.
General Batch Info
Style (optional) -- The style of beer you are brewing.
Code (optional) -- If you
track of your batches with a code written on the bottle caps (like I
do), enter the code here.
Name (optional) -- The name of the beer.
Brew date, Rack date, Bottle/keg
Prime with/amount, Gravity (optional) -- I write these in (on
the hardcopy) as the batch progresses.
Target volume -- The final batch size you are shooting for in the fermenter, including top-up water (if any).
Est Mash Efficiency --
Enter your best guess of your mash efficiency. If you are
consistently undershooting your target OG, decrease the number you
enter here; if you are consistently overshooting your target OG,
increase the number you enter here. Also note that your efficiency may
vary slightly, depending on the OG; I get around 75% for low-gravity
beers, but only 65% for high-gravity beers. If you are brewing from
extract with steeped grains, I suggest using 50% here (it will apply
only to the steeped grains).
Boil volume -- The amount
of wort you intend to boil (before evaporation losses). For
partial-boil extract batches, this will be less than the target volume;
for all-grain, this should be the amount of wort you intend to collect
during sparging (which will generally be larger than the target volume).
Boil time -- Total number of minutes you intend to boil.Target OG, IBU, SRM (optional) -- If you are trying to hit specific OG, IBUs and SRM, you can enter them here for reference purposes.
In this section of the spreadsheet, you enter all of the grains that you will be using in the recipe. The information for each grain is entered on its own row.
Grain Type (optional) -- Description of the type of grain used (e.g. "Pale Ale Malt").
Theoretical Max PPG -- The theoretical maximum yield for this type of grain. (A table of values for common malts is available here.)
Color -- The color (in degrees Lovibond) of the grain. This should be printed on the package the grain came in.
lbs, oz -- The number of pounds and/or ounces of the grain in the recipe.Malt Extract and Other Sugars
In this section of the spreadsheet, list any malt extract, honey, sugar, etc. (other than priming sugar) which will be used in the recipe. The PPG, Color, and lbz/oz columns have exactly the same meaning as for the Mashed/Steeped Grains section of the spreadsheet.
You may need to guess on the color, since Lovibond ratings for malt
extracts and sugars is frequently not specified. If in doubt about the
color, you can just leave it blank (but the color calculations will not
Also note the Boiled column
-- enter an "N" in this column if the extract/sugar will be added at
the end of the boil (or directly to the fermenter); enter a "Y" if it
will be added at the start of the boil. This information is needed
because the gravity of the boil affects hop utilization.
In this section, you enter the hop additions. Enter the information
for each hop addition on a separate row.
Type (optional) -- The type of hops.
AA% -- The alpha acid percentage of the hops. Should be printed on the package.
Age -- If you are using
older hops, you can enter the approximate age of the hops (in years)
here. The spreadsheet will attempt to compensate for the estimated
alpha acid degradation over time, assuming that the hops have been
properly stored (i.e. frozen). If this entry is left blank, the
spreadsheet does not compensate for the age of the hops.
oz -- Number of ounces of hops used.
Boil Time -- Number of
minutes prior to the end of the boil that the hops are added to the
This section lists the estimated OG, IBUs and Color of the beer, as calculated by the spreadsheet. These numbers are updated whenever you change any of the ingredients.Acknowledgements
The hop bitterness calculations are based on Glenn Tinseth's research. The article on which I based the hop bitterness calculations is here. The "bitterness fudge factors" near the top of the spreadsheet correspond to two of the constants in Glenn's equations; if the IBU values calculated by the spreadsheet seem too high or too low, you could tweak these values to make the calculated IBU numbers track your own brewing system better.The color and hop aging calculations are loosely based on information from Ray Daniels' excellent book Designing Great Beers.
Back to Mike's Brew Page
(Posted to Web June 27, 2004)