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:
My basic spreadsheet setup.
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 [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 =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!
Hi Kim,
I’m new here and glad I came across your post. So far I am using a spreadsheet that was online for formulations. But I recently decided to make it a one stop-shop to record my full INCI list as reference, and tab for product ideas… I’d love to a tab for the product ingredient pricing, but I’m not Excel savvy enough to create the formula. Are you willing t share that with me? My sincere thanks in advance for reading this request and considering…
Hi there.
Would you be kind enough to share the excel of the ingredient cost sheet with me? I am battling to make one.
Deepest thanks. 🙂
This is amazing! I’m super new to DIY. How do I know how much percentages should go for each ingredient? (I.e what percentage of sea salt, oil, Shea butter, wax, essential oils, etc.). Is there a reference chart for that? Thank you so much- I’m learning SO much.
Can you please share the excel ingredient cost sheet with me?
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!
Please share your spreadsheet with me, I’m not techy
I don’t offer a spreadsheet for sharing—I don’t even really have one, I build them from scratch to meet my needs every time I need one. This post is all about how I do that 🙂
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!
Oh Marie! thank you so much for this blog post. I watched the video and read the blog in December when you first posted it but my lack of knowledge with spreadsheets was frustrating. I tried made some progress but muddled my way around. I left it alone and then came back and really picked each sentence apart and compared the samples to my own and I had an Ah-Ha! Moment. I got the cost per batch and basic spreadsheet down but until this morning I finally understood the scaling part where you have the percentages and just change the batch size and the grams compute. I FEEL SO SMART AND WELL EQUIPPED! I appreciate your willingness to teach others and the work involved to present it!
I’m so glad you’ve got the hang of it! I love those a-ha moments 😀 Happy spreadsheet’ing and happy making!
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 am pretty excited you are thinking about another book. Would love to see more make up colour blends. Haven’t bought any makeup since getting your book.
I’m so thrilled to hear it! 😀 I’m hoping to start work on another book proposal this summer 😉 (eeee!)
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!
So very helpful.. I love your passion for making (and teaching!) all the wonderful products that you do! I am an Excel guru.. I wish I could send you the one I made just for lip balm! lol All items with INCI names, batch/lot numbers, formulas galore – so I can relate! So happy that I found you! 🙂
OOOh, that beauty of a spreadsheet sounds like a total masterpiece—SWOON! Thanks so much for reading—I’m sure we’ll have some spreadsheet fun together in the future LOL.
I am in the process of creating a spreadsheet now to keep track of ingredients, where I buy and cost. Then I wanted to create a spreadsheet where I can plug in the numbers if i need to change the total amount everything will change if i just change the final amount. But since I am an excel beginner beginner, this spreadsheet helps. I remember saying that I am never going to need excel really when in college or chemistry and now here I am in DIY world. 🙂
Hello Shalay!
I KNOW! I remember learning fractions in school and winging that I’m never going to need to know how to add them up and subtract and voila! Marie’s book comes long and I’m like… grrr. Play around with your excel spreadsheets, it is a remarkable tool that you can track so much with so easily!
I definitely will. I have everything written down and its a pain in the butt. I would like to have a tab with items that I have purchased and need to purchase with the sites that I use and prices of those items laid out so when its time to restock, i can just look at the sheet and calculate how much I am going to spend this way because right now I am going back and forth from multiple note books. Then a tab for formulas to change when i add oils, butters etc. I use this calculator and wholesale supplies and its good now that I figured it out but I rather have it in excel.
I am a new subscriber, but I’m really enjoying everything you’ve done. I have already made some of your recipes and I’m loving them! Thank you. This spreadsheet blog really got me thinking which provoked some questions regarding one of your recipes you used as an example. When referring to surfactants and their ASM values, I have to ask; if you’re looking for a specific percentage of ASM, why use two different ones but instead adjust one to reach the percentage of ASM you are wanting? I know this is irrelevant to spreadsheet instruction but was just curious to know why? Thank you again for all of your shared wealth of knowledge!
We blend surfactants to make milder blends 🙂 You can learn more about that here!
Hi Marie,
You so smart and incredible I watched you for almost 6 months now and I am learning a lot, so, thank you for that.
I know it a bit late but I want to share my version of lotion calc for the Bees here.
To be honest I couldn’t really figure out how total Active Surfactant Matter worked so I ignored it in the sheet.
for any advice feel free to contact me.
Here It is
https://docs.google.com/spreadsheets/d/1BWZ4MenvfPNqCU9sEgE6pJV-NczZMqk9Rfcakq57bdQ/edit?usp=sharing
And again Thank you, My Queen, Bee
Thank you so much for sharing, Bedo! I’m sure many other bees will find this very useful 😀
Two questions! How do we calculate the distilled water when we have spaces in-between “like the empty row that separates the water and oil phase etc” won’t this space affect the formula that’s used to calculate the distilled water ie=1-(sum(b2:b8)) and dose it matter where we put the distilled water like shouldn’t it always be on the B2 coloum? Thank you for being awesome
Hey Julia! Empty cells are empty and do not impact the formula. Try it yourself—add one or take one away and see what happens 🙂
Hi Marie
I am KP NAIK from INDIA i was seen your website so many products Recipes i am very happy with this products videos ,I m very impressed , i thought i can start the business in beauty products pl give me detailed information.
thank you .
Hi! Please read through my FAQ, I have several articles in there pertaining to your question 🙂
I find spreadsheets both sexy & exciting. 😉 Thank you for sharing this. It’s exactly what I’ve been looking for as I start making cosmetics!
Hooray for spreadsheets! Happy making 🙂
I am having such a difficult time trying to configure pricing for my bath salts and I really want to learn. Can someone point me in the right direction, please?!
THANK YOU! I have never worked with spreadsheets before and I knew there was a way to make them adjust my batch sizes easily. My friend and I read through your post and I was able to enter a spreadsheet for one of my shampoo bar formulas. This will be so useful when adjusting lotion recipes, conditioner bars, shampoo bars, etc.
I am so thrilled that you found this helpful! ❤️
I found your youtube channel and now your website. Thanks. May I also request your spreadsheet please.
Thanks
Lydia
cLncaa@msn.com
I used to teach classes in Excel, so after the first couple of hand scribbled pages of formulas that always ended up stained, smeared and wrinkly (quaint but annoying) I started dumping everything into a spreadsheet. One great advantage to this is you can set up a separate worksheet with references to all the active ingredients, and when you share your masterpiece with someone else you can print them a copy of all the wonderful things they’re getting (and dare them to try to find anything like it at CVS). They will then be beholden to you and hooked on your formula that they can’t get anywhere else and like the drug dealer on the corner you will own a piece of their very soul…(I’m being facetious here but I do take my creations seriously). Thank you Marie, your inspiration is a precious gift
This has been a great help – I’m currently trying to get all of my pen and paper formula on to a spreadsheet so thank you!
Realmente una auténtica maravilla, me ha facilitado muchísimo la tarea de crear.
Muchísimas gracias Marie.
Un abrazo fuerte