Today I want to talk about something that really isn’t all that sexy or exciting, but it is super useful—using spreadsheets for your DIYing and formulating! Even if you don’t formulate I’d recommend stepping away from online recipe calculators in favour of spreadsheets. You can use them to calculate, re-calculate, and store all the recipes for everything you make. When I’m formulating I work out most of my formulas in spreadsheets, and use them to track my iterations and adjustments. Spreadsheets are incredibly useful for working in percentages, scaling batches, and comparing formulas, and you definitely don’t need to be an expert to do any of those things. I’d really like to encourage you to start storing the formulas for the things you make in a program like Google Sheets; that will make them easily searchable, and you can quickly review, revisit, and scale formulas as needed. It’s great!
Want to watch this how-to instead of read it?
How to structure a basic formulation spreadsheet
It doesn’t matter if you’re making somebody else’s recipe or formulating something of your own—this is how I like to set up my formulation spreadsheets. Let’s take a look at the formulation spreadsheet for my Sugar Plum Body Butter Bars:
I’ll typically use the first three columns—A, B, and C. Column C will have my ingredients listed. Column B will have the amounts in percentages, using the software’s percentage formatting. I like to set column B up with a SUM function at the bottom of the column—that adds up everything in the column automatically so I can be sure I’ve always got 100% in the formula. In the above example, the value for cell B9 is
Column A will have the amounts in grams, using some simple spreadsheet math to calculate the precise amounts of each ingredient referencing a selected batch size and the percentage of each ingredient. I’ll list the batch size in the header row (100 for 100g, etc.) and then use spreadsheet magic to multiply the value of that cell against the percentage value for each ingredient. If I change the 100 to 200 or 4356, all the corresponding ingredient values will change with no additional work on my part! In the above example, the value for cell A2 would be
=$A$1*B2. The dollar signs in the A1 value lock in that cell call so you can extend that formula down the column and the A1 value won’t change, but the B value will, so the next column down will become
=$A$1*B3, and then
=$A$1*B4 and so on. If you change the value in cell A1, everything will automatically update.
So, if you were going to translate one of my recipes to a spreadsheet to work with it, you’d start by entering the ingredients and corresponding percentages into a new spreadsheet as seen above. You don’t need the gram values—just keep the percentages and the ingredients. If the recipe itself isn’t in percentages (only about the last year of recipes are, and not every formula you find online will be), check out this post to learn how to convert it into percentages.
Percentages (by weight!) are a universal language. Once you start to think and formulate in percentages you will be able to instantly understand so much about formulas—not only yours, but ones you find online, in reference books, and from suppliers and manufacturers. Working in percentages is the first step to understanding how different types of formulas are structured.
For example: say you find a recipe that calls for 4g polawax, 12g shea butter, and 4g argan oil for the oil phase, and you want to know roughly how thick/rich the cream will be. You have no way of knowing with that information. You’d need to know what the batch size was and then calculate percentages out of those amounts to know that.
However, if it was listed as 4% polawax, 12% shea butter, and 4% argan oil, now you know a whole lot more. You know the oil phase is 20%. That’s a decent sized oil phase, and with the bulk of it being shea butter you know that this will be on the thicker, richer side of things—but likely not too thick as we typically have to go upwards of 25% oil phase to get very thick, rich feeling end products. As you work with more and more formulas you’ll start to have a feel for what a 15% oil phase vs. a 20% oil phase vs. a 25% oil phase is like, and that will give you a lot more control over your creations and formulations.
Or, say, you look at a recipe and see it calls for 3 drops of cinnamon bark essential oil. That doesn’t tell you much. But if it calls for 5% you’ll know that’s a lot—too much—and you’ll know you will need to adjust that so the end product isn’t irritating.
Tips for specific types of formulas
If I’m formulating something that is going to contain a “Q.S.” (quantity sufficient) ingredient, I’ll let the spreadsheet do the work for me! Q.S. basically just means “enough of this to make the recipe add up to 100%. In most recipes that contain water, that’ll be your Q.S. ingredient, but it could be any kind of ingredient that can appear in a recipe in high quantities (carrier oils, butters, hydrosols, other solvents, etc.)
For this example we’ll look at my Hydrating Aloe Facial Mist.
The value for cell B2 (the distilled water amount) is
=1-(SUM(B3:B9)). The 1 is 100%, so basically 100% minus everything else in the recipe gives us the amount of water needed to top the recipe up to 100%—quantity sufficient. Easy peasy!
Active Surfactant Matter
Whenever you are formulating with surfactants it’s important to know what the total Active Surfactant Matter (ASM) of your formula is. ASM is basically the concentration of the surfactant—how much of it is active surfactant, and how much of it is something like water. Different surfactants have different ASMs, expressed as a percentage. Liquid surfactants almost always have lower ASM values than solid surfactants. I’ve documented the ASM values of all the surfactants I work with here.
Something like Cocamidopropyl Betaine is 30% active, while Sodium Coco Suphate is 95% active. That means that even if you used 3x the weight of Cocamidopropyl Betaine in a recipe you’d still have less active surfactant matter because Sodium Coco Sulfate is much more concentrated than Cocamidopropyl Betaine.
Different products have different desirable ASM ranges. Something like a gentle face wash is going to have a different ASM than a body wash or solid shampoo bar, so it’s important to know what the ASM of your surfactant formulations are. I use spreadsheets for this, too! For this example we’ll look at the formulation spreadsheet for my Christmas Tree Body Wash.
Our two surfactants (Caprylyl/Capryl Glucoside [C/C Glucoside] and Cocamidopropyl Betaine [that’s the “Amphosol CG”]) are in the second phase with the distilled water. The amounts of each surfactant are in column B. The ASM is calculated over in columns D & E. Column D contains the ASM value for each surfactant as a percentage, and column E calculates our total ASM.
The value of cell E6 is
=D6*B6. The percentage of the surfactant present in the formula multiplied by the ASM of the surfactant gives us the ASM of that surfactant in the overall formula. We do the same thing for E7:
=D7*B7. Then we add those values up to create the total ASM for the formula in cell E8 with
Want to watch this how-to instead of read it?
Generally speaking we use emulsifying waxes at 20–25% (or 1:4–1:3) of our oil phase. I use spreadsheets to calculate this amount based on everything else in my oil phase.
For this example we’ll look at the formulation spreadsheet for my Clementine Cleansing Milk.
For our oil phase we’ll be looking at the second chunk of ingredients. There are two carrier oils (castor oil and safflower oil), one thickener (cetyl alcohol), and our emulsifying wax (Polawax).
Remember, when we’re talking about ratios they aren’t the same as fractions. 1:4 is not the same thing as 1/4. 1:4 means for every four of something, you’ll need one of something else—that’s five parts total. 1/4 means one of a total of four parts. 1:4 is 20% as it is 1 of 5 total parts, while 1/4 is 25% as it is 1 of 4 total parts. Clear as mud? Good!
The amount of Polawax is calculated by the formula in cell B11:
=(Sum(B8:B10))/4. Basically, add up everything else in the oil phase (
(Sum(B8:B10)), and divide it by 4. That creates a 1:4 ratio (a part emulsifier + 4 parts oil phase), or 20%. That is because there are 5 parts total, and 1/5 is 20%.
If we wanted a 1:3 emulsifying wax to oil phase ratio (25% emulsifying wax) our formula would be
=(Sum(B8:B10))/3. That creates four parts, of which the emulsifying wax is one, and the remaining oil phase is the other three.
With this formula if the oil phase changes size, the emulsifier will adjust as required.
You can also see an active surfactant matter calculation at work in this spreadsheet, as well as as Q.S. calculation for the water at the top.
That’s how I use spreadsheets for my formulas, and some tricks to help you with formulating as well. I hope you found that useful! Do you have any spreadsheet tricks to share? Leave a comment!