Manufacturing
Paper lab notebooks and spreadsheets: digitizing formulation history
By SourceX Editorial · Updated
Short answer
To digitize lab notebooks and formulation spreadsheets, start with formulas tied to products you sell today and the test results that justified them, then work back through their parent experiments, including failures. A consistent field template, with formula ID, version, raw materials, process, tests and outcome, matters more than scanning every page.
Key takeaways
- Digitize commercial formulas and their qualifying test results first, then parent and sibling experiments, then everything else.
- Failed experiments belong near the top of the list, because they explain a formula's limits and are lost first when chemists leave.
- Scan every page as an image for the record, but structure only the pages you will use.
- Handwriting recognition is weakest on chemical names, numbers and units, so plan for chemist review of structured values.
- Digitizing makes formulas easier to copy, so access controls and confidentiality terms must be in place before anything is searchable.
Why formulation history is hard to use today#
Formulation history is hard to use because it is split across media and people. Older work sits in paper lab notebooks, newer work in each chemist's own spreadsheets, test results in a LIMS or in instrument printouts, and production batch records in the ERP. Linking a commercial product back to the experiments behind it often depends on someone who remembers.
That dependence shows up at bad moments: when a senior chemist retires, when a raw material is discontinued and a reformulation is urgent, when a customer asks why a product changed, or when a buyer in a sale asks for the formulation trail.
Electronic lab notebooks and formulation management systems solve the problem going forward. The open question is what to do about the years of work recorded before them.
The prioritization rule: commercial formulas and test outcomes first#
The prioritization rule is to digitize formulas tied to products you sell today, and the test results that justified them, before anything else, then work outward through their parent and sibling experiments. Those records carry the most value per page and are the ones you will need for reformulation, complaints and diligence.
Failures belong in tier 2 for a reason. Experiments that did not work explain the boundaries of a formula, and they are the history most often lost when chemists leave.
| Tier | What to digitize | Why |
|---|---|---|
| Tier 1 | Current commercial formulas, their qualifying test results and current specifications | Needed for production, customer questions and any sale |
| Tier 2 | Parent and sibling experiments of commercial formulas, including failures | Shows why choices were made and what did not work |
| Tier 3 | Active development projects | Avoids repeating work already done |
| Tier 4 | Abandoned product lines with complete test results | Useful for future projects and reformulation |
| Tier 5 | Incomplete or untraceable notebook pages | Scan and index only; structure on demand |
A field template for every formulation record#
A field template keeps records from different decades and chemists comparable. Agree on it before anyone transcribes a page, and make each field required unless the source truly lacks it.
| Field | Example content | Note |
|---|---|---|
| Formula ID and version | Unique ID plus version letter | Never reuse IDs |
| Parent formula | ID of the formula this one modified | Builds the family tree |
| Product or project link | Commercial product code or project name | Ties tier 1 records to sales |
| Raw materials | Internal raw material codes with supplier grade | Normalize trade names to codes |
| Amounts | Weight-based proportions per ingredient | Record units exactly as the source used them |
| Process | Order of addition, mixing and dispersion conditions | Often missing from spreadsheets |
| Batch scale | Lab, pilot or production | Results can differ by scale |
| Test methods | Internal or standard method references | Name the method version |
| Results | Values with units and test conditions | Keep raw values, not just pass or fail |
| Outcome | Pass or fail against target, plus the chemist's conclusion | Capture failures explicitly |
| Source reference | Notebook number and page, or file path | Link to the scanned original |
| People and dates | Chemist, reviewer, date of work | Supports invention records |
Scanning versus structuring#
Scanning and structuring are different jobs, and most projects need both. Scan every notebook page as an image so the original survives and can be cited, with signatures and witness dates intact. Structure only the pages that matter, starting with tier 1.
Handwriting recognition on lab notebooks is unreliable for chemical names, numbers and units, so plan for human transcription with chemist review on structured records. Spreadsheets need a different cleanup: normalizing raw material names, separating formula versions that were overwritten in place, and recording which results came from which batch.
Steps to run a digitization project#
A digitization project runs best as a fixed sequence with a named owner for each step. The R&D lead owns the template and tiers, while IT or quality owns the repository and access controls.
- Inventory every notebook, spreadsheet folder and LIMS export, with date ranges and authors.
- Agree on the field template and the raw material code map.
- Tier the records, starting from the current product list.
- Scan all notebooks to a controlled repository with page-level indexing.
- Transcribe tier 1 records, then have the original chemist or a peer review them.
- Load structured records into the ELN, LIMS or a controlled database, linked to the scans.
- Link formulas to QC batch results and customer complaints by product code.
- Set access controls before the data becomes searchable.
Protecting formulas while you digitize#
Digitizing formulas makes them easier to copy, so protection has to come first. Formulas are usually trade secrets, and under the Defend Trade Secrets Act information qualifies only if the owner takes reasonable measures to keep it secret. DOJ guidance describes measures such as need-to-know access, confidentiality agreements, telling employees what is secret and keeping documents secured.
In practice that means role-based access in the ELN or repository, logging who exports what, confidentiality agreements with any outside scanning or transcription vendor, and clear labeling. Two other categories need flags: raw material information suppliers shared under NDA, and formulas developed under customer-funded projects, whose agreements may give the customer rights.
Illustrative example: a coatings maker digitizes its architectural line#
Illustrative: a fictional coatings maker sells architectural and light industrial paints. Its formulation history spans paper notebooks from earlier chemists, a shared drive of spreadsheets and a LIMS that holds QC results but not development tests.
The VP of R&D starts with the current product list. Each commercial formula is traced to its notebook pages, transcribed into the template, reviewed by a senior chemist and linked to its qualifying tests. Parent experiments follow, including several abandoned approaches to lower-odor binders. Older notebooks with no product link are scanned and indexed only.
When a raw material supplier later discontinues a coalescent, the team finds every affected formula and the earlier experiments that tested alternatives from one search instead of a notebook hunt.
Formulation history as a licensable record#
Formulation history linked to test outcomes, including failures, is the kind of record AI developers working on materials and formulation models look for, and digitizing it is also the first step toward any license. Licensing does not have to expose commercial formulas: they can be excluded, or abstracted to ingredient classes, during preparation.
SourceX runs that work through the SourceX five-step transaction, from a metadata-only fit check through Rights, Preparation, Approval and Delivery, with the company approving each step. The SourceX Evidence Packet records provenance and permitted use for anything released.
Frequently asked questions
Should we buy an ELN before digitizing old notebooks?
Choosing the destination first helps, because the field template should match how the ELN or LIMS stores formulas and results. But you can start scanning and indexing immediately, since that work is useful whatever system you pick and it protects the originals from loss.
Can handwriting recognition read our notebooks?
Partly. Modern tools can read clear handwriting, but chemical names, subscripts, units and numbers are where errors cluster, and a wrong number in a formula is worse than a blank. Use recognition to draft, then have chemists check every structured value against the scan.
Should we keep the paper originals?
Usually yes. Notebooks may support invention dates, regulatory questions and disputes, and signed and witnessed originals can carry weight that a scan alone may not. Store them securely after scanning, and ask counsel before destroying any notebooks.
How do we handle formulas recorded in different units?
Record values exactly as the source shows them, with units, and add a normalized field calculated from them. Never overwrite the original. Reviewers can then check conversions, and anyone using the data later can trust that an odd value came from the notebook rather than the transcription.
Who should review transcribed formulation records?
Ideally the chemist who did the work, or a peer who knows the product line when the original author has left. The reviewer checks every amount, unit and test result against the scanned page and signs off in the record, so later users know the structured values were verified by someone qualified.
Sources
- Under 18 U.S.C. 1839(3), information qualifies as a trade secret only if the owner has taken reasonable measures to keep it secret and it derives independent economic value from not being generally known. Source
- DOJ guidance states protective measures must be reasonable under the circumstances, citing need-to-know access, confidentiality agreements, advising employees and keeping documents locked. Source
Related resources
See if your company qualifies
A short company assessment. No data uploads are needed.