Kit And Extract Beer Spreadsheet

[ianh]

hi Ian,

I just used your spreadsheet for the 1st time, and it was bloody fantastic, thankyou indeed. Not only did it help building a recipe, but also with a great simple overview of the process itself.

There were 2 things which as a beginner I would have really like to have access to, so for the sake of continuous improvement I thought I'd mention them. I steeped some caramalt first, and used this as the base for the boil volume, but it had an unknown SG, and was warm. Trial and error got me close, ish, but a calculator that would let us enter the current SG reading, temp, and volume, and then show the amount of DME (g) that should be added to bring it to the target 1.040 would have been great.

is such a thing easily worked out?
I am pretty sure i've seen the calculations somewhere?

regardless, thanks indeed, I now have 20 litres of some heavenly smelling juice quietly doing its stuff downstairs in the garage.

Hi Nodrog

Thanks for the comments.

The spreadsheet takes into account speciality grains when calculating the additional DME or LME that needs to be added to achieve 1.040. I just use the volume of my kettle (1.7 litres), 2 kettles of water to steep the grain and 4 kettles to sparge 6X 1.7 = 10.2 10 in round numbers. The other way is to add your required volume of water to your pot then use a stick or ruler to measure the depth. Then steep and sparge your grain pour into the pot and top up with water to the mark.

cheers

Ian

 

[tavas]

Hi Nodrog

Thanks for the comments.

The spreadsheet takes into account speciality grains when calculating the additional DME or LME that needs to be added to achieve 1.040. I just use the volume of my kettle (1.7 litres), 2 kettles of water to steep the grain and 4 kettles to sparge 6X 1.7 = 10.2 10 in round numbers. The other way is to add your required volume of water to your pot then use a stick or ruler to measure the depth. Then steep and sparge your grain pour into the pot and top up with water to the mark.

cheers

Ian

Hi Ian

Thanks for the spreadsheet. I am having 1 error when using the "Inventory" button. I keep getting an error in the Inventory subroutine; xlSortOnValues. Not sure if I need to activate/deactivate something.

Cheers

 

[ianh]

Hi Ian

Thanks for the spreadsheet. I am having 1 error when using the "Inventory" button. I keep getting an error in the Inventory subroutine; xlSortOnValues. Not sure if I need to activate/deactivate something.

Cheers

Hi tavas

Thanks for the comment. Sounds as though something is wrong on the Inventory worksheet. You've not moved or deleted anything? Should have the heading Inventory on the top row and Grains Hops etc on the second row then the inventory of stuff is row 3 through 42 Columns A to S.

Suggest download another copy of spreadsheet and try Inventory first up. If still getting error pm me.

cheers

Ian

 

[brente1982]

Wow that spreadsheet is sensational, really helpful for an beginner like myself. Thanks heaps.

Might i suggest, for someone like myself who doesnt always understand the procedures, like how to steep properly, or get the water to the required temperature when boiling hops and stuff like that, just a written instruction on how to do them all. Would be much help and appreciated


But overall, so far i think its fantastic

Cheers

Brent

 

[chemacky]

Has anyone tried using the spreadsheet with openoffice.org? After sorting out the first few errors by enabling macros, and visual basic... I'm getting a 'BASIC error' when trying to save a recipe.

Any ideas?


edit: awesome spreadsheet btw!!!

 

[Shed101]

Might i suggest, for someone like myself who doesnt always understand the procedures, like how to steep properly, or get the water to the required temperature when boiling hops and stuff like that, just a written instruction on how to do them all. Would be much help and appreciated

Brent - in case you haven't found it yet, get yourself along to How to brew you'll find answers to some of those questions plus a bazillion other useful things there

 

[michael_aussie]

I have just stumbled onto these SS.
Can I suggest this thread should be stickied?? (air-locked).
From a noob's point of view, the SS looks like a fantastic consolidation of lots of important information.

 

[shonks69]

I have updated the Kit & Extract Beer Designer spreadsheet to version 2.3. I have produced two versions 2.3a which has the AABC styles and 2.3b which has the BJCP styles as in the previous versions.

The update to the new version is mainly to fix problems which existed in version 2.2 plus I have just started using a Refractometer to calculate my sg's and have included some columns on the Brews worksheet for that.

Problems fixed include Inventory weights for Dextrose and Maltodextrin plus the Inventory yeast total. Fixed the problem with Inventory weights due to rounding errors, you thought you had 1.0 kg but spreadsheet had say 0.99999995 and then said you had insufficient when you did a recipe requiring 1.0kg.

There was a problem with the Hop drop-down list not covering all the hops listed.

Fixed a problem when you saved a recipe that already existed it was saved in the wrong place and consequently recipes appeared dotted round the Recipes worksheet.

View attachment 38824

You will need to copy your existing Brews and Recipes to the new spreadsheet.

I left a couple of brews as examples on the Brews worksheet. If you are not going to use a Refractometer for sg's you can delete columns T to Y. Delete the 2 existing brews and then copy the rows from your old Brews worksheet to the new one. There are formulas and formatting in the Rows down to brew 100, if you have more brews than this you will need to copy the rows down.

If you leave columns T to Y on the Brews worksheet,then you will need copy your Brews across in two lots. First copy the Rows but only columns A to S and paste into the new Brews worksheet. Then copy the rows and columns T to Y from the old worksheet and past in Columns Z to AE on the new worksheet.

The recipes can be copied across to the new spreadsheet Recipes worksheet, do not transfer any that that appear to be located in odd spots, they should all be in sequence in Columns E to AO.

One done save your spreadsheet with a new name.

cheers

Ian

Edit:

If you are using a version with the refractometer columns on the Brews worksheet, you need to change a line in the makerecipe macro to get the dextrose for bulk priming into the correct column. Near the end of the makerecipe macro is the line

ActiveCell.Offset(0, 19).Value = Worksheets("MAIN").Range("m19").Value

this needs to be changed to

ActiveCell.Offset(0, 25).Value = Worksheets("MAIN").Range("m19").Value

If you have problems doing this, you can just delete the dex bulk prime value in column V and put it in column AB each time you run the make recipe macro.

Hi Ian
Great work mate
How can I add extra types of grain/hopps to the spread sheet?

Cheers
Shonks :icon_cheers:

 

[ianh]

Hi Ian
Great work mate
How can I add extra types of grain/hopps to the spread sheet?

Cheers
Shonks :icon_cheers:

Hi Shonks

Thanks. You can add other ingredients.

For grains you need the ebc value. On the MAIN worksheet go down to cell O101 and you find the list of grains, just add you grain and its ebc value to the bottom of the list. Grain in O column ebc in P column.

Similar for hops you need the %AA value. On the MAIN worksheet go down to cell S101 and you find the list of hops, just add you hop and its %AA value to the bottom of the list. Hop in S column %AA in T column. If you hops have a different %AA value from the one in the spreadsheet, find the hop and change it's %AA value in the adjacent column.

You can add ingredients providing you don't reach the cells with the black border.

cheers

Ian

 

[milestron]

Hi there
First out this spreadsheet is a work of art - trully it has been one of the best tools for me to learn 'how everything fits together'.

My next brewing milestone I think will be trying a partial mash, I've been reading up a few articles on it. Will the spreadsheet work the same for partials ie I just put the grain amounts in same as specialty grains?

Cheers

 

[peterl1981]

ok i have just downloaded the KIT & EXTRACT BEER DESIGNER, bloody great, i can see where i stuffed up my last brew..

so going buy this designer for the recipe below

i would add about DME to the boil untill i reach 1.040 then get up to a rolling boil then add yeast then 10 minutes more hops

then add the remaining of the 923g of DME or 1223 of LME

is the DME or LME extra or is it just from the can of 1.5Kg Coopers Light Malt Extract in recipe

1.7Kg Thomas Coopers Sparkling Ale
1.5Kg Coopers Light Malt Extract
15g amarillo @ 15mins
15g amarillo @ 5 mins
15g amarillo dry hopped in secondary
Yeast - US-05

hope you can help as my last brew was very weak and dont wantr to stuff it up again

cheers
lynchman

 

[Lodan]

Make sure you don't add your yeast to the boil, that will kill the little guys. Wait until you've done everything and cooled the wort down. The DME/LME will just be some of the malt that you planned on using in the recipe, no additional amount needed.

Looks like a tasty mod recipe of the DS JSGA

 

[peterl1981]

yeah its a james squire golden ale hope it works out

 

[shonks69]

Hi Shonks

Thanks. You can add other ingredients.

For grains you need the ebc value. On the MAIN worksheet go down to cell O101 and you find the list of grains, just add you grain and its ebc value to the bottom of the list. Grain in O column ebc in P column.

Similar for hops you need the %AA value. On the MAIN worksheet go down to cell S101 and you find the list of hops, just add you hop and its %AA value to the bottom of the list. Hop in S column %AA in T column. If you hops have a different %AA value from the one in the spreadsheet, find the hop and change it's %AA value in the adjacent column.

You can add ingredients providing you don't reach the cells with the black border.

cheers

Ian

Thanks Ian
You must of invested a great deal of hours designing this spreadsheet.

Cheers mate :icon_cheers:

 

[ianh]

Thanks Ian
You must of invested a great deal of hours designing this spreadsheet.

Cheers mate :icon_cheers:

Investing a few more hours, working on version 3, one of the advantages of being retired you can work on what you want. Brewday today.

cheers

Ian

 

[Dazza_devil]

G'day Ian,
Would it be possible to include some other ingredients or adjuncts in your spreadsheet Ian? I'm just punching in a Belgian Dubbel, playing around with different possibilities and wouldn't mind being able to put some Dark Belgian Candy Syrup into the equation. There is a lot of detail on it's makeup on the Craftbrewer website but I'm not sure if it's possible to figure it all in to your spreadsheet.

 

[ianh]

G'day Ian,
Would it be possible to include some other ingredients or adjuncts in your spreadsheet Ian? I'm just punching in a Belgian Dubbel, playing around with different possibilities and wouldn't mind being able to put some Dark Belgian Candy Syrup into the equation. There is a lot of detail on it's makeup on the Craftbrewer website but I'm not sure if it's possible to figure it all in to your spreadsheet.

Hi Boagsy

Looking at changing the Dextrin and Maltodextrin slots to Adjuncts, so there will be 4 speciality grains and two adjuncts. Then extending the number of hop additions to 8. Requires a major rewrite for the SGs plus rewrites for the recipes etc,

When are we going to catch up?

cheers

Ian

 

[shonks69]

Investing a few more hours, working on version 3, one of the advantages of being retired you can work on what you want. Brewday today.

cheers

Ian

Good on ya mate :icon_cheers:

 

[chemacky]

Amazing spreadsheet! I use it all the time. Can I suggest a way to 'export recipe to text' for ease of posting onto places like here? I tried the old copy and paste from the "recipes" tab, but it doesn't come out quite as desired...

If there is a way to do this already, please forgive me, but also tell me how as I'm clearly incapable of finding it myself!

 

[kelbygreen]

yeah more hop additions would be good been doing a few IPA and have just started to record my brews on paper as not enough hop places. Also hops like citra and a few others are not in the hop selection so gets confusing lol. But its still good to work it out on style as the hop additions i cant fit are mostly dry hop or last min so no ibu change well minimal anyway