During a busy brew day, it can be a challenge to keep track of what’s going on with current fermentations in the cellar. Sometimes, we don’t notice that they have a slow or stalled fermentation until it’s too late to do something about it. In most cases, the next step is to dump the batch and start again—an unfortunate waste of product, time, and money.
However, being able to keep track of your fermentations and predict issues before they arise can potentially save a batch. Even if you’re already collecting daily gravity and pH readings, it can be challenging to see whether a particular batch is right on target with other batches you’ve brewed or whether it is acting as an outlier.
Here, I’ll outline how you can use Microsoft Excel or a similar program, such as Google Sheets, to create a simple spreadsheet for tracking fermentations. This will let you use your daily gravity and pH readings to visualize your fermentation data and make the proper recipe or ingredient alterations.
How Spreadsheets Can Improve Fermentations
Most brewers aim to have healthy and consistent fermentations. This helps us to not only make better beer and keep customers, but also to predict and plan out packaging and release schedules. By implementing a more robust tracking system for tracking fermentations, you can learn more about your recipes and ingredients and prevent potentially expensive issues and delays.
Yeast health is directly tied to fermentation performance. By tracking batch-to-batch pH and gravity curves, you can clearly see when an under-pitch has occurred or whether a particular generation of re-pitched yeast strain needs to be replaced. In particular, pH curves can be very useful when examining the overall health of your yeast. If your fermentation isn’t showing a clear pH drop in the first 24 hours, you’re likely about to encounter a stalled fermentation. By using Excel spreadsheets to create visuals of past and current fermentations, you can note trends and whether the batch in question is acting normally.
A pH trend that is out of a brand’s spec can also indicate a potential contamination issue. Let’s say the starting pH is much higher than what you were reading in your brewhouse and doesn’t decrease after the first day. That could indicate a chemical contamination, such as with caustic. On the other hand, a lower pH can indicate an infection by lactic acid–producing bacteria. If these bugs take over before the yeast cells start growing, it could result in an unintentionally sour beer. This would be apparent by a much lower pH curve than expected for a particular brand.
Tracking the gravity of a fermentation seems straightforward, but there is much that these data can tell you. Being able to see early trends on a graph can warn you to watch out for stalled fermentation, under-attenuation, or hop creep. Being able to see these signs as early as possible can help you avoid supply-chain issues if, for example, you know that a batch will slowly plod its way down to terminal gravity. By compiling data in a spreadsheet for this purpose, you’ll be able to compare similar batches and thus predict when a batch will close or crash.
This also relates to your beer being within your ABV specifications. By adding a target final gravity to your brand graphs, you can quickly see whether a batch is going to finish low or high, and you can make decisions based on that information.
Finally, one of the important uses of fermentation tracking is to adjust new or existing recipes. If you’re seeing trends in a particular beer where the fermentation drops quickly but isn’t hitting the target final gravity, it may be a good time to make some adjustments in the mash or look at your grain quality. This can be especially useful for new brands and small batches.
By being able to visualize this data, you can make changes to the recipe to make a new beer come out exactly how you envision it.
Why Use Excel?
Even if you aren’t familiar with using spreadsheets for data analysis, Excel makes it easy. Once you make a template of your fermentation tracking spreadsheet, it will be simple to make a copy for each brand you want to analyze. Plus, any questions about Excel or its functions can usually be answered with a quick Google search.
If your computer doesn’t already have Excel or you don’t want to pay for it, Google Sheets is a free option that fulfills the same purpose. This may work even better for your brewery because it offers immediate updates and sharing over multiple devices. Multiple people can have access to the fermentation-tracking sheets so you can work together to make real-time decisions on a batch.
Finally, you can customize Excel to your liking. You can add or change colors, styles, and logos. Some of these things can be used to help with visualizing different aspects of your graphs, but they can also add some flair to your sheets. If you’re looking at these sheets every day, you might as well make them colorful.
Setting Up the Spreadsheet
Begin by creating a new Excel spreadsheet. The first tab will be for the graphs and subsequent tabs will be for the fermentation data of each individual brand.
It’s easiest to start by filling in the data tabs and then creating the graphs once you actually have data for input. You’ll split the tab into two different sections: one side for gravity and one side for pH. On the gravity side (Figure 1a), you will have the batch number on the left and days of fermentation on the top. You can fill in the gravity readings for each day from left to right. Below this will be the average gravity for each day of fermentation for that brand. You can also choose to include a row with your target final gravity for the graph.
On the right side of the sheet, do the same with your pH data for each batch (Figure 1b). Here, instead of having targets, it makes the most sense to just include the average curve for your pH readings. To add more batches above the Average rows, simply insert rows as needed. This also applies to adding days of fermentation by inserting columns to the right of your data. Copy this template over to subsequent tabs for each brand you’re choosing to track. Input your data as far back as you want to go. The more data points you have, the more accurate and useful the average will be.
To the left of your brand tabs, insert a new tab and label it Graphs. This is where all your visual data will be represented for the brands being tracked. Keeping consistent with the data tabs, your graph on the left will be for gravity and the graph on the right will be for pH. Choose to insert a graph and select 2-D line with markers (Figure 2). This way, you can easily see your separate data points. Both graphs will need a title with the brand name, a vertical axis labeled as either Gravity or pH, and a horizontal axis labeled as Days of Fermentation. You can choose to add a legend to the right and data table on the bottom. All these options can be found in Chart Elements under Chart Design.
Next, begin to select your data to add to your graph. You’ll want to include three series: one for your current batch being tracked, one for the average, and one for the target final gravity. In Excel’s Select Data Source box (Figure 3), select your horizontal gravity or pH data for each series in the Y values. The horizonal (category) axis label is your Days of Fermentation, starting at zero.
Once your graph is complete, you can choose to add or change visuals, so that each line is easier to separate (Figure 4). As you add data to your brand tab, it will automatically add to the series line on your graph (Figure 5a). The same procedure can be done for the pH graph, while omitting a target pH line (Figure 5b).
If you aren’t familiar with Excel, it might take a little bit of practice and troubleshooting to create these graphs. However, once you have a template set up, it’s easy to add data and make changes to suit your brands and targets.
In the end, this method is a simple way to organize and track your fermentation data, so that you’re more aware of potential issues or where changes need to be made to your recipe or ingredients. The result: better, more consistent beer, and a more predictable brewing schedule for your business.