Short answer
A perfume formula spreadsheet needs, at minimum, columns for material, dilution and weight, plus calculated columns for pure active (weight × dilution ÷ 100), formula % (weight ÷ total) and relative % (pure active ÷ total pure active), and a check that the percentages sum to 100. Add a cell for a target batch size and a scaled-weight column. Use an Excel Table (or equivalent) so totals include new rows automatically, never paste values over formulas, and keep one version per sheet or per labeled block.
The layout
Put the formula in rows 2 onwards, one material per row, with these columns:
| Column | Heading | Contents |
|---|---|---|
| A | Material | Typed |
| B | Code | Typed |
| C | Dilution % | Typed; 100 for neat |
| D | Weight (g) | Typed |
| E | Pure active (g) | =D2*C2/100 |
| F | Formula % | =D2/$D$31*100 |
| G | Absolute % | =E2/$D$31*100 |
| H | Relative % | =E2/$E$31*100 |
| I | Scaled weight (g) | =D2*$K$1/$D$31 |
Row 31 holds the totals (=SUM(D2:D30), =SUM(E2:E30) and so on), and K1 holds the target batch size. The ranges assume up to 29 materials; adjust to your formula, or better, use a table so you don't have to (see below).
Checks to build in
- Percentages sum to 100:
=ROUND(SUM(F2:F30),2)=100should show TRUE. - No blank dilutions:
=COUNTIFS(A2:A30,"<>",C2:C30,"")should be 0 — it counts rows that have a material but no dilution. - Scaled total matches target:
=ROUND(SUM(I2:I30),2)=K1. - Smallest weight is weighable:
=MIN(I2:I30), compared with your balance's resolution.
Use conditional formatting to turn the check cells red when they fail. A check nobody sees is not a check.
Use a table so ranges grow
The most common spreadsheet failure is a new row added below the range of the SUM, so the total silently ignores it. In Excel, convert the range to a Table (Insert → Table). Formulas then refer to columns by name — for example =[@[Weight (g)]]*[@[Dilution %]]/100 — and totals cover every row in the table automatically. Google Sheets offers a similar table feature; alternatively, use whole-column references with care. Either way, test it: add a row and check the total changes.
Scaling and batch targets
The scaled-weight column turns the formula into a weighing sheet for any batch: put 147.6 in K1 and every row is multiplied by 147.6 ÷ total. For a finished perfume, add two more cells — batch weight and concentration — and calculate the concentrate (=batch*conc/100) to feed K1, and the alcohol (=batch-concentrate). The arithmetic behind these is in converting a perfume formula from percentages to grams and the perfume dilution and calculations guide.
Versions
A spreadsheet has no idea what a version is. Choose one convention and keep to it:
- One sheet per version, named with the code and version ("EX-001 v7"), never edited once a newer one exists; or
- One long table with formula code and version columns on every row (the layout of our perfume formula template), filtered to show one version at a time.
Either way, never edit a version that has been made into a trial. Duplicate it and edit the copy.
Protect the formulas
Lock the calculated columns (sheet protection, allowing edits only to material, code, dilution and weight). That prevents the second most common failure: a pasted value overwriting a formula, after which one row stops updating while looking exactly like the others. Other spreadsheet-specific failures, and how to catch each, are in spreadsheet perfume formulas: 9 failure modes.
Where spreadsheets reach their limits
A well-built spreadsheet is a reasonable formulation tool for a small number of formulas. It becomes hard to manage when:
- materials need to link to a shared material library with prices, stock and IFRA limits, and every formula must update when one changes;
- there are dozens or hundreds of formulas, each with many versions;
- you need to know which formulas use a material;
- several copies of the workbook exist on different devices.
At that point, the question of dedicated software is worth asking — perfume formula software vs spreadsheets compares the two honestly. If you move, a clean spreadsheet with consistent columns is far easier to import.
Frequently asked questions
Excel or Google Sheets?
Either works for the formulas here. Google Sheets makes it easy to have one shared copy; Excel works well offline. The bigger risk is having several copies of either.
Should I calculate in percentages or grams?
Store the amounts in one unit — grams as weighed is the most direct — and calculate the other. Never store both, or they will disagree after an edit.
Written and reviewed by the RUŌOD Lab team. This article is general education about perfume formulation and record-keeping; it is not legal, regulatory or safety advice, and the examples are illustrations, not validated commercial formulas. How we write and check these guides.