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 `=SUM(B2:B8)`

.

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.

## Why 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

### Quantity Sufficient

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 [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 `=SUM(E6:E7)`

.

### Want to watch this how-to instead of read it?

### Emulsifier Amounts

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!

My initial page in my crafting spreadsheet lists all the ingredients I use and how much each ingredients costs across all the sellers I purchase from. I also note where I purchased that ingredient last time. I then have a page for each product I made and on that page I use the spread sheet for costing the product ingredient by ingredient giving me a total cost per batch as well as a cost per unit.

Kim would you mind sharing your spreadsheet? I would love to see how you set up and also as inspiration to create my own. I have recently started making handmade products and i’m struggling in this department. Thank you!

Awesome! I’ve done this in the past, but have fallen behind with it as cost-per-batch doesn’t matter to me in a world where I typically only make 2–3 of anything 🙂 It is SO rewarding to do that math and realize how insanely inexpensive most making is, though!

OMG your spreadsheets are so similar to mine! I fact that’s how I save my forums so I can adapt the batch size easily and quickly.

Spreadsheet buddies!

This is such a wonderful post!!

Since I am not much of an Excel person, I am using the Soapmaker software for most of my recipes. But you had here some truly awesome tips that I am going to give it a try too!! Thanks!!

I’m so glad you found it helpful! Hopefully you can fall in love with spreadsheets like I have, ha!

After getting help from the Gods at Apple they helped me to do this on my 2008 Numbers spread sheet. The number (40) has to go in the B2 column for some reason, the A didn’t work out and the % in the C column. Then I went to the next cell in B and hit = sign, then clicked B2 (40) * C column with % I want and then click on green check. Moved onto next cell in B and repeated.

Can you please post crayon version of this post. Thanks for the great information. I am a complete Excel nerd and you have now given me another reason to sit and geek out on a new spreadsheet.

Is a crayon version an even simpler version? I’m not sure I have that in me 😛 I’m glad you enjoyed the post!

This is both sexy and exciting!! Thank you so much, life changing stuff, I just created my first formulating spreadsheet, those formula’s are so satisfying! I’m now going to translate all my collected recipes into spreadsheet form – must remember I’m supposed to be making some of these things as Christmas presents!

YAY!!! I am so glad you’re enjoying nerding out with me a bit 😀 This post will help you convert anything that isn’t already in percentages into percentages so you can convert and scale away!

Hi Marie!! You are amazing!! I had a question. How would I put in a soap recipe to include the liquid, lye & fragrance oil. I input everything as you did the 2nd spreadsheet example, nonetheless, I was unable to get the percentage for it. I somewhat cheated bc I had the oil percentage from using soapcal but it doesn’t give liquid percentage.

Hey Dee! This spreadsheet setup isn’t suited for soap calculating as-is; soap calculators also have SAP values for all the fats you’re using, so you would need to be including those values in your spreadsheet as well as the calculations to determine NaOH for the amount of each fat, and then whatever reduction is required for your desired superfat. I still leave the soap calculations to SoapCalc 🙂

This is the most excellent blog post I’ve ever seen, ever. I became a patron because it was so excellent.

I don’t even have excel.

I am so glad you enjoyed it so much! I have to say I didn’t expect this particular post to be so popular but I am thrilled people are finding it so helpful

This is so informative! Thank you!

I’m so glad you found it useful—thank you so much for reading!

This is pretty life changing for a novice diyer! At first I thought, skip by this one. Then I got into it and am thinking this will be sooo handy. So happy to be one of your Patrons. When is the Humblebee and me school on skincare formulations opening? Hehee!

Woohoo! I’m so glad this somewhat dull-sounding post is really resonating with you 😀 Thank you so much for your support and enthusiasm! I have zero intentions of starting a school, but I would like to write another book!

I just ordered all the ingredients for making the Creamy Concealer and Essential Mineral Makeup Powder Base from your wonderful book, I can’t wait to get started. However, I had a question concerning the spreadsheet (this is coming from a very numerically challenged individual) – how do I figure out the number for the ‘Batch Size’ to get the ball rolling for my spreadsheet if I’m starting a new ‘recipe’ or up-sizing an existing one?

That number is simply how much you want to make. “100%” can be anything, so you get to define it. For cosmetics, 5–10g is typically PLENTY. For lotions, 100g is usually a good amount to work with. For body butters I usually chose batch sizes in the 25–50g range. If you want to make it as written, look for the yield of the recipe in grams, if provided. Take a look at some of the labels on some store bought products to get an idea for how much a certain amount is.

Think of it like serving yourself food. It takes a bit of familiarity with the food to know how much is enough—how much butter you’d want vs. how much soup 🙂

(I’ve deleted your other question on this post as it was essentially the same.)

Thank-you for this! I have never set up a spreadsheet before, but this sure would clean up my mess of scribbled notes I have all over the place!

Can you tell me where and what formula you use to get the grams to change with the percentages? (I know it is probably simple, but I am doing something wrong)

Maybe Humblebee should expand into the Math 101 courses!

Val

If you’re setting the spreadsheet up as directed here the grams will change automatically with the percentages. Have you tried watching the video? That shows it in action and might help flush out whatever isn’t quite right 🙂

I’m an excel junkie and have almost too many spreadsheets. . . . BUT. . . I LOVE what you have done here!!!!!!!

THANKS SO MUCH!!!!!!!!!!!!!!!

Too many spreadsheets? There’s no such thing! 😛 Thanks so much for reading and happy spread-sheeting!