Create a Beer Gravity Curve In Excel
Updated: Jun 7, 2022
When I started homebrewing, I casually wrote down various values throughout the brewing process, and I found fermentation data to be one of the most interesting.
I created simple X-Y charts out of my numbers and graphed the gravity values as the beer fermented. What results is a pretty "curve" or line chart that can tell you loads about your beer, the yeast, and your process - and even help you to detect problems.
JUMP TO SECTION:
Check out Keg Punk. Software that helps
you manage taps, track keg inventory, and more.
WHAT ARE GRAVITY CURVES?
Tracking changes in gravity during fermentation is one of the easiest and most insightful ways you can improve your beer from batch to batch. Gravity curves (I also call them fermentation curves) are simply the visual representation of changes in specific gravity readings of beer in the fermenter. Enough with the lingo - it's a chart, plain and simple.
While you can create these gravity curves with paper and pencil, I recommend using the power of computers - and Excel is a great starting tool to do it with. Below is a step-by-step guide to creating gravity curves from your fermentation data in Excel. While Excel has far more processing and formatting capabilities that what's described, this article is just to get you started.
STEP-BY-STEP GUIDE TO CREATE A GRAVITY CURVE
Open Excel and start a blank workbook.
Save the file - I saved it as "GravityCurve".
At the bottom of the page, change the name of the sheet to "Gravity Curve", and add a main title at the top.
Under the main title, add a "Batch Info" section. This should include
Beer Name (The name of the recipe)
Style
Brew Date
Batch Name (I like to name batches as "Beer Name" + Brew Date, for example "Far Out - 12Dec19")
Now add the headings for the table we're making. From left to right, the headings should be:
Sample Date
Day of Fermentation
Fermenter Temp
Sample Temp
Hydrometer Reading
Adjusted Gravity
Lower FG
Upper FG
Highlight the headings. Under Insert, click "Table". In the pop-up, select "My Table Has Headers". Click "OK".
Now let's create a chart. In Insert, click the symbol for "Scatter With Straight Lines and Markers"
Click "Select Data"
Click "Add"
Click in the first field, and then click in the empty space next to "Batch Name". This will name the chart after the batch name.
Click in the Series X-Values field and select the cells under "Day of Fermentation".
Click in the Series Y-Values field and select the cells under "Adjusted Gravity"
When entering data, use a gravity adjustment calculator to get an adjusted gravity reading. Otherwise, your values may be inaccurate.
In order to get the Lower and Upper FG values, you first need the attenuation range of the yeast strain you are using. For this example, we're using Wyeast 1056. From the manufacturer's website, we can easily find the range.
Now calculate the predicted final gravity range:
OG = 1.050
Attenuation range => 73 - 77
Lower Attenuation = .73
Upper Attenuation = .77
Upper FG = [(OG - 1) x (1 - Lower Attenuation)] + 1
Upper FG = [(1.050 - 1) x (1 - .73)] + 1
Upper FG = 1.013
Lower FG = [(OG - 1) x (1 - Upper Attenuation)] + 1
Lower FG = [(1.050 - 1) x (1 - .77)] + 1
Lower FG = 1.011
So for a beer with an OG of 1.050 and a yeast attenuation range of 73-77%, we can predict that the FG of the beer will be between 1.011 - 1.013.
The calculated range is the Lower and Upper FG values. Enter these into the table.
Now we'll add the FG range to the chart.
Click the line in the chart. It should highlight the associated data. Now click "Select Data".
Click "Add".
Click in the first field, and then click the "Lower FG" heading.
Click in the Series X-Values field, and then select the cells under "Day of Fermentation".
Click in the Series Y-Values field, and then select the cells under "Lower FG"
Repeat the process for "Upper FG". This should give you the estimated final range for this batch based off the OG and the yeast attenuation values.
Everything works - so now you can format it! Make it easy to read and understand.
CONCLUSION
By tracking gravity values as the beer ferments, you'll be able to see predict when the fermentation is complete, when to transfer, or detect any issues with the fermentation. While this may be too intense for some brewers, for others that enjoy the science of brewing (like myself), this is a great way to get started analyzing yeast and fermentation patterns of your brews.
Manage your keg inventory and taproom from one place
Keg Punk helps breweries with inventory, taproom management, and analytics. It acts as a central hub for the entire team to seamlessly maintain an accurate inventory count while simultaneously generating unique analytical reports.
