Soapmaking Spreadsheet Recipe Tool

[Grantxw8]

@AliOop beat me to the answer while I was looking for the longer explanation in a thread I saw a few nights ago, after which I ceased to stress so much over the longevity number. @Grantxw8, check out this link. I bookmarked it this time so I don’t lose it.

Thanks for going to the trouble of looking it up. It was a good read. Having read it I’m not stressing about it either. I had thought it was some magical way of predicting shelf life outside of the normal variables.

I’m learning again how much there is to know compared to what I do know. It’s fun.

Thank you for not being offended! I also try not to take offense and come from the perspective that most people do mean well.

In response to your questions:

1. longevity = palmitic + stearic.

2. Regarding the difference in oil amounts, did you include the 4% superfat in the oil total on your spreadsheet? I’m no mathematician, but that seems to be about the difference that I’m seeing between your oil total and the SMF oil total.

Hi AliOop,

Yes, I wondered that too but I double-checked it and I only included it once (I thought I may have discounted it twice somehow - thus explaining my lower values).

FWIW, I seem to have tracked down the culprit. I’d been playing with the different settings in the online calculator and had selected the button to allow batch resizing when “%” inputs are used. It seems that even when "%” is deselected and “g” is chosen instead the setting is still used. Probably not ideal as the setting can’t be seen or changed unless “%” is chosen again. Anyway, toggling that button seemed to correct the figures. Phew!

That’s the good news. The not so good news is that I incorrectly thought I’d solved the circular reference issue. Sadly I was mistaken and it persists. I have another idea for solving it but it would make using macros necessary. I’m hoping to avoid that as I understand why people might be cautious about installing anything with macros. They can be dangerous to your computer health if they’re from a malicious source.

I’ll look for another way.

 

[Fireside]

it would make using macros necessary. I’m hoping to avoid that as I understand why people might be cautious about installing anything with macros. They can be dangerous to your computer health if they’re from a malicious source.

Take this in the humorous and I'm-way-too-paranoid spirit in which it is intended:

When I downloaded your spreadsheet and tried to use the macros, I got a message saying they were disabled and warning me that introducing a spreadsheet with macros from an unknown source could harm my computer if there is malicious intent. It then when on to tell me how to enable the macros if I felt compelled to ignore their warning.

"Should I do it?" I asked myself. "Afterall, Grantxw8 is an unknown source to me. What if this is an elaborate scheme to infect people's computers and take control of them, under the guise of sharing a soapmaking spreadsheet?" Then I came to my senses and realized that you are obviously an extremely smart person and if you had malicious intent you would find a much simpler way to be malicious, and you would probably target a more lucrative audience than soapmakers. So, I enabled the macros and, voila!, nothing bad happened. You are just as you present yourself to be: a nice, generous, intelligent, spreadsheet-loving soapmaker. Thanks again for sharing and thanks in advance for, hopefully, laughing at this.

 

[ScentimentallyYours]

Soap calculator

Thanks for going to the trouble of looking it up. It was a good read. Having read it I’m not stressing about it either. I had thought it was some magical way of predicting shelf life outside of the normal variables.

I’m learning again how much there is to know compared to what I do know. It’s fun.

I had been puzzled by the hardness number and expected it to be the same as longevity. After all, doesn’t a hard bar last longer? Based on the number of questions that come up about longevity, it must be a common issue. In the soap calculator the hardness number is determined by the total % of all hard oils with lauric, myristic, palmitic and stearic fatty acids included. As @AliOop explained, the longevity number is the sum of only palmitic and stearic acids.

hardness% - cleansing/bubbly% = longevity

I think that although lauric and myristic acids contribute to a harder bar of soap, they also dissolve more easily to create bubbles and cleanse, meaning they don’t contribute to slowing down the solubility of the bar. If the other soap properties look good, we can add a small amount of soy wax or another form of stearic acid to boost hardness and longevity.

 

[AliOop]

@ScentimentallyYours You are so right that hardness and longevity are two different things when it comes to soap qualities. Coconut oil at 100% makes a physically hard bar, but bc it is so soluble, it doesn’t last long.

I would clarify that it is not the “hard” or “soft”’oils per se, but combinations of fatty acids that determine the soap qualities. Coconut oil, for instance, is a hard oil but has very different qualities than lard, palm, or any of the butters.

 

[ScentimentallyYours]

@ScentimentallyYours You are so right that hardness and longevity are two different things when it comes to soap qualities. Coconut oil at 100% makes a physically hard bar, but bc it is so soluble, it doesn’t last long.

I would clarify that it is not the “hard” or “soft”’oils per se, but combinations of fatty acids that determine the soap qualities. Coconut oil, for instance, is a hard oil but has very different qualities than lard, palm, or any of the butters.

Yes! The lauric and myristic acids in CO are “hard” oils, but their properties in the soap during use are very different from palmitic and stearic acids in Lard or cocoa butter when we use the soap. Right?

 

[Grantxw8]

Take this in the humorous and I'm-way-too-paranoid spirit in which it is intended:

When I downloaded your spreadsheet and tried to use the macros, I got a message saying they were disabled and warning me that introducing a spreadsheet with macros from an unknown source could harm my computer if there is malicious intent. It then when on to tell me how to enable the macros if I felt compelled to ignore their warning.

"Should I do it?" I asked myself. "Afterall, Grantxw8 is an unknown source to me. What if this is an elaborate scheme to infect people's computers and take control of them, under the guise of sharing a soapmaking spreadsheet?" Then I came to my senses and realized that you are obviously an extremely smart person and if you had malicious intent you would find a much simpler way to be malicious, and you would probably target a more lucrative audience than soapmakers. So, I enabled the macros and, voila!, nothing bad happened. You are just as you present yourself to be: a nice, generous, intelligent, spreadsheet-loving soapmaker. Thanks again for sharing and thanks in advance for, hopefully, laughing at this.

Hey Fireside,

You make me smile. I fully agree with your caution over macros. In fact, I don't enable macros on MS Office documents from sources I don't know and trust. That's one of the reasons I didn't password-protect the tool, so anyone with knowledge of VBA could examine it. That and I built it for my own use and convenience. The macros aren't, currently essential for the functioning of the tool. They really just do some data entry, copy/paste stuff protest/unprotect the current sheet and show/hide sheets. All of that can be accomplished in other ways. I'm just lazy and find the macros faster. I could just offer 2 versions - one with macros and one without if someone requested it. Anyway, thanks for being so trusting.

My wife and I are a little under the weather (head colds) at the moment and we have a big weekend with the market for the refugee group tomorrow. So, this will be on the back burner for the moment. The tool as it currently stands will work well for its intended audience - newish soapers - who would/should be making smaller batches. As I understand it, the difference between lye-concentration and water-as-a-%-of-oils calculation becomes more important when scaling batches up.

 

[Grantxw8]

Greetings from Brisbane,

I walked away from the spreadsheet and had a good night's sleep. I awoke today with a thought. I had an inkling of how to solve my circular references issue without making macros essential! Even better, it worked.

I'll upload Simple Soap Designer 2 in 2 versions. One that I use with macros (because I'm lazy and like to do things once before making the computer do it in the future) and another without macros for the (cautious) masochists amongst you. The version without macros is fully functional but requires a bit more Excel knowledge. For instance to hide the extra tabs (fragrance, colourants etc.) you have to right-click on the tabs and select 'Hide'. To bring them back to add more fragrances etc. click on any tab and select 'Unhide' and choose the sheet you want to see.

As always, I'd be interested (read delighted) to receive feedback. Thanks to everyone who has already given me input and guidance on this little project. You guys rock.

I'd advise anyone who downloaded a previous version to update to the latest one as there are some behind-the-scenes improvements that make things more accurate. As previously stated, this is provided free without any sort of warranty. Please double-check your recipes with another lye calculator such as SoapMakingFriend or SoapCalc. I certainly do.

Version 3 is now available HERE.

Have a great weekend.

 

[Grantxw8]

Hi @Grantxw8 - Your spreadsheet looks amazing, but I didn’t give it a test run because I’m another soap maker in the 40% lye concentration camp.

Hi MobJack Bay,

FYI, I spent a day or so playing with Excel and the tool now has the option to use lye concentration as a variable. I’ve limited it to 5 different concentrations that were suggested to me in some articles I was sent.

If people prefer a greater range of concentrations I could easily add more but chose these 5 for clarity.

If you think you might find the tool useful but need more options please let me know.

It is also now available in 2 versions, with and without macros.

Have a great weekend.

 

[AliOop]

Yes! The lauric and myristic acids in CO are “hard” oils, but their properties in the soap during use are very different from palmitic and stearic acids in Lard or cocoa butter when we use the soap. Right?

Kind of… I do understand what you mean, but I wouldn’t mix the terms “fatty acids” and “oils” bc those are two different things.

CO is a hard oil; its lauric and myristic fatty acids promote bubbles and provide cleansing.

Lard and cocoa butter are also hard oils; they are higher in stearic and palmitic fatty acids that create both hardness and longevity.

Make sense? I hate to be picky about words, but we want to be sure not to confuse others who may read the thread later.

 

[ScentimentallyYours]

Kind of… I do understand what you mean, but I wouldn’t mix the terms “fatty acids” and “oils” bc those are two different things.

CO is a hard oil; its lauric and myristic fatty acids promote bubbles and provide cleansing.

Lard and cocoa butter are also hard oils; they are higher in stearic and palmitic fatty acids that create both hardness and longevity.

Make sense? I hate to be picky about words, but we want to be sure not to confuse others who may read the thread later.

I don’t disagree about confusion over the terms oils and fatty acids. I didn’t intend to confuse the two, so clarification helps. When we plug the oils into the soap calculator, however, the breakdown in fatty acids is listed alongside the soap properties graph. Knowing the types of FA and percentages in specific oils helps us understand how to tweak our recipes to get the soap properties we’re after.

 

[Grantxw8]

Hi Folks,
I have added some very rudimentary batch recording to the tool and made some small behind the scenes improvements (speed, accuracy etc.). I've also added an "Instructions" tab showing the basics to get started.

I've decided to stop producing the version without macros as it severely limits what I can do with the tool (and I make it primarily for myself). Making another version is too much work TBH. I've also had to remove the option to add oil amounts manually (rather than as a percentage) as it was messing up the calculations. I may add this back later but I'm out of time for the near future. Our refugee family arrive tomorrow! Very exciting.

I hope you enjoy this tool and find it useful. The only other enhancement I plan to make beyond this is to add some "Longevity" information in the design tab. That's for another day though.

I've removed the file from this post and refer you to the latest version (v4.0) below.

 

[Grantxw8]

I have a little time to work on this again in the next week or so. I'm hoping that the next version I upload will be the final version. If anyone has used this and found it useful I'd be keen to hear if there are any enhancements you'd like. I'm anticipating that people might want the navigation by way of the macro buttons to be clearer? I can't promise anything but, if I get replies in a timely manner and they're 'do-able', I'll give it a go.

Since the last version, I've added back the use of manual amounts for oils and added longevity as a characteristic in the Design sheet. However, I'll not publish that version until I've made any changes that are requested. Looking forward to hearing from you.

 

[Mobjack Bay]

Grant - thank you for sharing your amazing spreadsheet and especially for adding the lye concentration-related options. I like that I can work in the design sheet and then move the recipe to the calculation sheet with a simple click. Plus, everything else I need, such as the fat profiles and a place to add my mold dimensions and volumes, is built in. I keep an eye on fatty acid profiles in addition to soap qualities when formulating and appreciate how easy it is to check the numbers in the design sheet (and without scrolling!). I can’t think of anything else to suggest at this time.

 

[Grantxw8]

Grant - thank you for sharing your amazing spreadsheet and especially for adding the lye concentration-related options. I like that I can work in the design sheet and then move the recipe to the calculation sheet with a simple click. Plus, everything else I need, such as the fat profiles and a place to add my mold dimensions and volumes, is built in. I keep an eye on fatty acid profiles in addition to soap qualities when formulating and appreciate how easy it is to check the numbers in the design sheet (and without scrolling!). I can’t think of anything else to suggest at this time.

Thanks for the positive feedback. It really helps to hear that others are using the tool and liking it. There’s no way for me to know whether it’s being downloaded even, so knowing that it is hitting the mark for someone else is really encouraging.

 

[Grantxw8]

Hi Folks,

Time is a little pressing these days so I've taken what feedback I have received and used it to finalise the Simple Soap Designer Tool. I'll upload what I hope will be the final version today. It already does everything I (a non-commercial, hobbyist soap maker) need it to do. It is presented to you without warranty of any sort and with a recommendation to always double-check your lye calculations elsewhere like I do. SoapCalc and SoapMakingFriend are good places and are free currently.

The Macros are now required to make everything function properly. However, I have not password protected anything so anyone can look at the VBA code and check that I'm not doing anything nefarious.

I spent a ridiculous amount of time changing the layout and making it a little more compact and prettier (eye of the beholder and all that).

Having said all that, if I receive feedback that there is a problem with the tool, I will find the time to fix it. If there is any absolutely cracking idea that I find compelling I'll find time to implement that too. For now, though, it is complete.

I hope you have fun with it.

Latest version available HERE.

 

[Whillow]

Hi Folks,

Time is a little pressing these days so I've taken what feedback I have received and used it to finalise the Simple Soap Designer Tool. I'll upload what I hope will be the final version today. It already does everything I (a non-commercial, hobbyist soap maker) need it to do. It is presented to you without warranty of any sort and with a recommendation to always double-check your lye calculations elsewhere like I do. SoapCalc and SoapMakingFriend are good places and are free currently.

The Macros are now required to make everything function properly. However, I have not password protected anything so anyone can look at the VBA code and check that I'm not doing anything nefarious.

I spent a ridiculous amount of time changing the layout and making it a little more compact and prettier (eye of the beholder and all that).

Having said all that, if I receive feedback that there is a problem with the tool, I will find the time to fix it. If there is any absolutely cracking idea that I find compelling I'll find time to implement that too. For now, though, it is complete.

I hope you have fun with it.

Wow, I am not sure how I missed this whole thread. That is a lot of work you put into this spread sheet and is very impressive. Thank you very much for sharing.

Hope you are fully enjoying retirement. Seems everyone I know that has retired are more busy than they were before (that will probably be my problem when I eventually retire).

I will post my results once I sit down to figure it all out.

 

[Grantxw8]

Wow, I am not sure how I missed this whole thread. That is a lot of work you put into this spread sheet and is very impressive. Thank you very much for sharing.

Hope you are fully enjoying retirement. Seems everyone I know that has retired are more busy than they were before (that will probably be my problem when I eventually retire).

I will post my results once I sit down to figure it all out.

Hi Whillow,

Retirement is indeed busier than I expected. However, it is mostly busy with things I enjoy which is great. I loved my job (prosthetist) but I'm not missing it because I've found other worthwhile things to occupy myself. I hope you find the same when the time comes.

I'd be very keen to hear your thoughts on the tool when you've had an opportunity to work with it. I added a sheet with some instructions which made sense to me. I trust they are reasonably clear. At least clear enough to give people a place to start. I've left some pricing data in the tool so that putting it through its paces wouldn't require a lot of effort. However, to make the calculations for batch sizes and pricing accurate for your circumstances, you'd need to clear my data and enter your own. This applies to Fragrances, Oils, Lye, Colourants etc., as well as putting in your mould dimensions to calculate batch sizes.

It started as a small project to help me figure out how to use oils in soaps. It then became another type of beast altogether allowing me to track where I was spending money on my hobby. I'd put a bit of time into it and wondered if it would be useful to others so thought I'd share it. Hopefully it will help those hobbyists who can't justify the expenditure on commercial programmes (which are awesome but... ). It might also be helpful in helping people learn a little about the contribution different oils (and therefore fatty-acids) make to the characteristics of a soap.

Of course there is so much more to soaping than this but it's a good place to start. If you have any issues with the tool, please feel free to let me know and I'm always open to suggestions. I think I've reached the end of how I would develop this for my own use but I like playing with Excel (it's like Sudoku to me) so any further development would require suggestions from others.

Happy soaping. Cheers, Grant

 

[Grantxw8]

I've uploaded v4.1. There is no functional difference to v4.0. I just added some red buttons to the set-up sheets that will clear my preloaded data (without the danger of deleting the calculations) so you can start using it to work for your own situation.

 

[Whillow]

I've uploaded v4.1. There is no functional difference to v4.0. I just added some red buttons to the set-up sheets that will clear my preloaded data (without the danger of deleting the calculations) so you can start using it to work for your own situation.

Thanks again. I will be working on this Sunday afternoon. If I have any questions, I will post here if you don't mind.

 

[Grantxw8]

Thanks again. I will be working on this Sunday afternoon. If I have any questions, I will post here if you don't mind.

Thanks Whillow, I’ll look forward to hearing your thoughts when you have some time. No pressure though.