The Soap Formulators Workbook

A Practical Guide to Using The Soap Formulators Workbook

I have been making soap for roughly fourteen years, starting with handwritten spreadsheets that looked like something from a spreadsheet horror story. Over time, I ended up with a system built around a file I call The Soap Formulators Workbook. It is not fancy software. It is an Excel-based tool with enough rows, columns, and formulas to handle standard cold process calculations without requiring a degree in chemical engineering. Most people who contact me about it are either new formulators who are drowning in raw math or experienced makers trying to standardize their process across multiple recipes. The core of the system is a structured spreadsheet where you input oil weights, select from a pre-populated database of oils with their respective SAP values, and the workbook handles the lye and water calculations. You set your desired superfat level, enter your batch size, and it returns the exact amount of sodium hydroxide and water needed. That sounds simple enough, but the value is in the structure behind it. I built this file after burning through three batches in a row because I was manually calculating SAP values from separate tables and kept making transcription errors. One time I used the SAP value for olive oil when I had actually formulated with a high-linoleic sunflower oil, which has a measurably different saponification requirement. The resulting soap was soft, had a high superfat reality, and failed a hardening test two weeks into curing. That cost me approximately $180 in oils and a week of wasted time. The workbook avoids that specific failure mode by locking the SAP values into a reference table keyed to each oil entry. When you select an oil from the dropdown, the corresponding SAP value auto-fills. You cannot accidentally override it without manually breaking the formula chain, which I consider a feature rather than a bug. The reference table uses values from the Lyle Douglas SAP chart, which is the industry standard for sodium hydroxide calculations at room temperature. If you are working with potassium hydroxide for liquid soap, there is a separate section for that with KO SAP values included.

How It Works in Practice

Let me walk through a typical workflow. I start by deciding on a target batch size. For my main production runs, that is usually 4 kilograms of total oils. I open the workbook, switch to the main calculation tab, and set the batch size cell to 4000 grams. Then I move down the oil list and enter each oil by name and weight percentage or direct gram weight. The workbook automatically calculates the percentage breakdown and validates that the total adds up to one hundred percent. If it does not, a warning cell turns amber. That has saved me from launching batches where the total oil weight was wrong by a few percent because I forgot to adjust a sub-recipe when scaling up. After entering the oils, you select your superfat level. A standard setting for a bar soap intended for general handwashing is between five and seven percent. Anything below four percent tends to produce a harsh bar unless you are using a high amount of coconut oil or castor oil, which generate significant cleansing and bubbles on their own. The workbook applies the superfat to the total lye calculation rather than to individual oils, which is the correct approach for standard cold process work. Some formulators mistakenly apply superfat per oil, which creates an uneven result where certain oils are over-rendered and others are under-rendered. I learned this the hard way on a lavender-honey batch where the honey content oxidized unevenly because the fat redistribution was incorrect. The water amount is handled through the water-to-lye ratio setting. I typically use thirty-three percent of the lye weight, which puts me in the mid-range of the standard 25 to 40 percent window. The lower end produces a thicker, faster-tracing batter that is harder to work with in detailed pouring work. The higher end gives more working time but increases the risk of overheating in dense molds. The workbook calculates the exact water weight and also provides a dissolved lye temperature guideline based on ambient conditions, which I find useful during winter when my workshop runs cooler than the standard twenty degrees Celsius baseline.

Edge Cases and What the Workbook Does Not Handle

I need to be clear about the limitations because several people have sent me frustrated messages after discovering them. The workbook assumes a purely cold process sodium hydroxide system. If you are doing hot process, the lye and water amounts remain the same, but the methodology changes entirely, and the workbook does not account for water evaporation during cooking. You still use the calculated water weight, but you should plan for additional stir time and potential separation when the gel phase hits. There is no automated adjustment for that. Another limitation is fragrance oil load calculation. The workbook includes a field for fragrance percentage relative to total oil weight, but it does not cross-reference that with IFRA maximum usage rates. I have seen formulators load fragrances at eight percent because the spreadsheet accepted the number without question, and the resulting soap accelerated trace within thirty seconds in a warm room. You need to check IFRA data separately. The workbook is a calculation engine, not a safety compliance tool. That distinction matters. A more specific issue I ran into involved using lard as a primary oil. The SAP value for rendered lard varies slightly depending on the source animal diet and rendering temperature, and the workbook uses a single fixed value. In practice, the variation is small enough that it does not materially affect the final bar for most formulations, but if you are sourcing lard from different suppliers seasonally and noticing hardness variations in your cured bars, you may want to do a small test batch and adjust manually. I discovered this after switching to a grass-fed supplier and finding that my standard lye calculation produced bars that were slightly softer than expected after four weeks of cure. A half-percent reduction in my superfat setting corrected it.

There is also no integration for additives like clay, milk powders, or botanical infusions in the lye calculation itself. Those ingredients do not change the saponification requirement, but they do affect water absorption and trace behavior. The workbook has an notes section where I recommend recording the adjusted water amount you used for each batch. Over time, this becomes a useful empirical database for your own formulations.

Getting Started With the File

You can download a copy of The Soap Formulators Workbook from the usual recipe-sharing forums where I post updates. The current version includes an updated SAP reference table with values for seventeen common carrier oils and two specialized butters, a batch history log sheet, and a fragrance load summary tab. I tend to release minor updates when I discover a better source for SAP data or when a user reports a formula error in an older version. The file is Excel-compatible, but it also opens correctly in Google Sheets and LibreOffice Calc, though some users report that the conditional formatting does not render identically in non-Microsoft environments. If you are new to soap formulation, I would suggest running through the sample recipes included in the workbook before building your own from scratch. They cover a standard five-oil blend, a high-olive castile bar, and a coconut-dominant quick-trace recipe. Each sample includes the target properties like hardness, cleansing, and conditioning estimates based on the oil composition profile. These estimates are rough approximations derived from established formulation heuristics, not laboratory test results, but they are useful for comparing relative outcomes between different oil combinations. One thing I will say without hedging: the workbook will not teach you how to soap. It will not tell you when to stop blending, how to judge consistency by feel, or what to do when your batch accelerates unexpectedly. Those skills come from making soap repeatedly and paying attention to what happens in the pot. But the workbook removes the arithmetic barrier so you can focus on the technique instead of recalculating lye weights on a piece of scrap paper every time you scale a recipe. For me, that shift reduced my average formulation-to-pour time from about forty-five minutes down to roughly twelve minutes for a standard batch, not including mixing and pouring. That is a significant difference when you are running multiple recipes in a single session.

When to Look Elsewhere

If you are formulating for commercial sale and need batch documentation that meets regulatory requirements, this workbook is not designed for that purpose. It does not generate compliance sheets, safety data references, or lot numbering systems. Commercial formulators usually transition to dedicated formulation software that includes those features. If you are making hot process soap exclusively, the workbook will give you the correct lye and water numbers, but you will need to manage the cooking phase separately and adjust your water evaporation expectations manually. If you are working with exothermic additives like citrus zest or certain spice blends, the workbook will not warn you about potential trace acceleration or discoloration. That requires your own experience or additional reference material. The Soap Formulators Workbook is a calculation and organization tool. It is useful, it is practical, and it has specific boundaries. Knowing those boundaries upfront will save you more time than any amount of troubleshooting after a batch has already gone wrong.