# Chemical Inventory Spreadsheet: 6 Steps for Lab Teams Source: https://www.verbalexperiment.com/blog/chemical-inventory-spreadsheet Published by Verbex. How these guides are written: https://www.verbalexperiment.com/how-we-write A chemical inventory spreadsheet works best when every physical container gets its own row, with a unique ID, storage location, hazard class, expiry date, lot number, and a link to the supplier's safety data sheet. That's the cleanest way to stop a forgotten bottle from becoming a guessing game. A real lab problem makes the point fast. A reagent bottle turns up at the back of a cold cabinet. It's past its date. Nobody can say who ordered it, whether it was ever opened, or where the SDS lives. A container-level sheet is meant to prevent exactly that kind of drift. The practical sequence is simple, define the required fields and column meanings, build a clean header row, add warnings for expiry and low stock, sort by location and hazard class, attach evidence for SDS, lot, and expiry, then keep version history tied to the lab's note or ELN workflow. For a quick sense of row-based thinking in data work, see [counting rows for data analysis](https://www.boloforms.com/blog/rows-function-in-excel-counting-the-number-of-rows-in-your-data/). ## Table of Contents - [1\. Lab Inventory System](#1-lab-inventory-system) - [What works and what doesn't](#what-works-and-what-doesnt) - [2\. Define the fields before the formulas](#2-define-the-fields-before-the-formulas) - [Keep the naming narrow](#keep-the-naming-narrow) - [3\. Build a clean header row and make it hard to break](#3-build-a-clean-header-row-and-make-it-hard-to-break) - [Make the spreadsheet behave like a form](#make-the-spreadsheet-behave-like-a-form) - [4\. Add warnings for expiry and low stock](#4-add-warnings-for-expiry-and-low-stock) - [Use the warning to trigger action](#use-the-warning-to-trigger-action) - [5\. Sort by location and hazard class, not just alphabetically](#5-sort-by-location-and-hazard-class-not-just-alphabetically) - [Location needs to be specific enough to audit](#location-needs-to-be-specific-enough-to-audit) - [6\. Link SDS, lot, and expiry evidence back to the row](#6-link-sds-lot-and-expiry-evidence-back-to-the-row) - [Use photos for identity, not for guesswork](#use-photos-for-identity-not-for-guesswork) - [7\. Version the sheet and tie it to the lab's note workflow](#7-version-the-sheet-and-tie-it-to-the-labs-note-workflow) - [Chemical Inventory Spreadsheet, Excel Workbook Overview](#chemical-inventory-spreadsheet-excel-workbook-overview) - [Keep the Sheet Current With a Simple Review Routine](#keep-the-sheet-current-with-a-simple-review-routine) ## 1\. Lab Inventory System A spreadsheet only works if it behaves like a controlled record, not a shopping list. That means each row should represent one container, not a vague substance total, because one bottle can be open while another is sealed, one can be expired while the other is not, and one can sit in a different room with a different owner. That distinction matches how real inventory control works in regulated labs, where records have to stay aligned with the actual containers on hand and with evolving inventory rules from the EPA and ECHA. The regulatory picture has also made container-level discipline more important, since the EPA's TSCA Inventory now contains more than 86,000 chemicals and received about 400 Notices of Commencement each year in the cited update period, while the ECHA EC Inventory includes 106,212 unique substances or entries [EPA TSCA Inventory](https://www.epa.gov/tsca-inventory/about-tsca-chemical-substance-inventory) ECHA EC Inventory. [Lab Inventory System (Excel inventory workbooks)](https://www.verbalexperiment.com/lab-documentation-pack?angle=inventory) fits that approach well for small teams because it gives chemical inventory and SDS tracking, sample and freezer box maps, solution preparation, and storage monitoring in one workbook set. The useful part is not that it looks fancy. It is that it gives a lab a place to put the boring but essential columns, chemical name, CAS number, catalog number, container size, quantity, units, owner, storage location, lot or batch number, expiry date, and SDS reference. Those fields help keep the sheet usable when a bottle moves, splits into aliquots, or gets discarded. ### What works and what doesn't A workbook like this works when the team agrees that the sheet is the current record. It does not work when people keep parallel lists in email threads, whiteboard notes, and paper labels. A small lab can often keep control with a simple rule, one container, one row, one current status. > **Practical rule:** if a person can't find a container in under a minute, the sheet's location field is too vague. The product's direct website is [Verbal Experiment](https://www.verbalexperiment.com), and the pack page is the clean starting point for a team that needs a ready-made workbook instead of building one from scratch. For a spreadsheet to stay dependable, the lab still has to define who updates it when a container is received, opened, relocated, or disposed. ## 2\. Define the fields before the formulas A chemical inventory spreadsheet falls apart when people start typing free text into half the cells. That usually happens when the sheet has names but no definitions. The fix is to separate substance-level fields from container-level fields before anyone enters stock. The substance-level side usually holds the chemical identity, CAS number, hazard classification, and SDS link. The container-level side holds lot, expiry, quantity, units, location, and current status. That separation keeps a bottle of the same reagent from being merged with another bottle that is older, partially used, or stored somewhere else. The minimum useful fields are not mysterious. They are the ones that let someone answer four questions fast, what is it, where is it, how much is left, and is it still usable. A sheet that includes manufacturer, catalog number, storage area, owner, and dates for receipt, opening, expiry, or disposal gives the lab an auditable bridge between the physical shelf and the written record. That matters because inventory records in labs are operational compliance data, not just stock counts. ### Keep the naming narrow Use one primary name field and define how it should be written. If the lab allows several names for the same substance without control, the sheet becomes hard to sort and easy to duplicate. Stable identifiers do most of the work. - **Chemical identity:** use the exact label name and keep the CAS number where one exists. - **Container detail:** track lot, expiry, quantity, and location at the container level. - **Document link:** store the SDS reference in the same row, not in a separate binder habit. - **Status control:** use a closed list such as active, consumed, transferred, expired, or disposed. > The hardest part is not adding fields. It is forcing the team to use the same field the same way every time. That discipline also fits how labs are managed. Berkeley requires inventory updates when materials enter or leave a lab and annual certification, while LSU requires incoming chemicals to be barcoded and recorded with fields like chemical name, CAS number, vendor, catalog number, quantity, units, storage location, laboratory, and principal investigator [UC Berkeley chemical inventory](https://ehs.berkeley.edu/safety-subjects/chemical-safety/chemical-inventory). A serious spreadsheet should mirror that logic rather than improvising its own labels. ## 3\. Build a clean header row and make it hard to break The header row is not decoration. It is the contract that keeps the sheet readable six months later. A clean chemical inventory spreadsheet uses fixed column names, one meaning per column, and no merged cells in the data area. That sounds basic, but it is the difference between a usable record and a file that only one person can repair. The main job here is structure. Put the unique container ID first, then chemical name, CAS number, manufacturer, catalog number, concentration if needed, container size, quantity, units, lot, expiry, location, owner, SDS link, and status. If a lab needs more detail, add it deliberately. Do not bury important fields in comments or color codes that disappear when the file is copied. ### Make the spreadsheet behave like a form Validation rules do more for reliability than a dozen extra columns. A units drop-down prevents “mL” and “ml” from becoming separate values. A date rule prevents a typo from making an expiry date look like it is in the distant past or future. A status lookup table keeps the language consistent across all rows. > **Practical rule:** if a field matters for searching, reconciliation, or audit, it should be a real column, not a note in somebody's head. The best spreadsheets also protect formula columns so users do not overwrite them. That matters when low-stock flags, expiry warnings, or location lookups are built into the sheet. The formulas should be visible enough for the manager to review, but locked enough that bench staff cannot break them by accident. For labs that track chemicals and stock in the same workbook, a simple workbook set can work better than a heavier system. The strength of a spreadsheet is speed and familiarity. Its weakness is that it relies on discipline. Without controlled headers, the data becomes difficult to reconcile, especially when multiple people edit it. ## 4\. Add warnings for expiry and low stock A chemical inventory spreadsheet becomes much more useful when it warns people before a problem turns into a cleanup task. Expiry and low stock are the two flags that matter most in day-to-day use. They are also the easiest to automate without turning the sheet into a database. Use a date field for expiry and a quantity field for current stock. Then add a conditional format that changes the row or cell color when the date is near or past expiry, or when quantity falls below a set minimum. That lets a lab manager scan the sheet quickly before ordering or disposal runs. It also helps with the review cycle required by many institutions. OSHA's laboratory Chemical Hygiene Plan guidance calls for a formal review of the plan's effectiveness at least annually, with updates when necessary OSHA laboratory safety factsheet. ### Use the warning to trigger action A flag is only useful if somebody acts on it. Past-date containers should move to a disposal or quarantine status, not stay hidden in the active stock list. Low-stock rows should trigger a purchase or replenishment check, not just another color on the screen. - **Expiry warning:** highlight containers close to or past the date. - **Low-stock warning:** compare current quantity against a defined threshold. - **Review status:** mark whether a row has been checked this cycle. - **Change log:** record who changed the quantity and why. EPCRA adds another reason to track maximum quantity rather than current quantity alone. Reporting is triggered when a hazardous chemical is present at or above the applicable threshold at any time, with 500 pounds or the Threshold Planning Quantity for extremely hazardous substances, and 10,000 pounds for other hazardous chemicals [EPA EPCRA reporting guidance](https://www.epa.gov/epcra/epcra-hazardous-chemical-inventory-reporting-general-reporting-guidance). A good spreadsheet should therefore preserve enough history to show what was present, not just what remained on the day the sheet was opened. That matters even in small labs. A container that was partly used, transferred, or disposed of can still affect safety review, purchasing, and emergency planning if the record is stale. ## 5\. Sort by location and hazard class, not just alphabetically Alphabetical order is comfortable and often wrong. A chemical inventory spreadsheet is more useful when it sorts by room, cabinet, shelf, fridge, freezer, then by hazard class or segregation group. The reason is simple. People search by where a thing should be found, not by what letter it starts with. That structure also matches the reality of chemical storage. ACS guidance says effective management means deciding which chemicals are necessary, understanding incompatibilities, and considering available storage space and location [ACS lab safety guidance](https://www.acs.org/content/dam/pldp/center/lab-safety/publications/safety-in-academic-chemistry-laboratories-faculty.pdf). A 2022 ACS article on storage decisions also ties physical properties, like whether a substance is a solid or liquid, reactivity, volume, and hazard statements, to storage location. The spreadsheet should capture those fields so the sort logic reflects the actual storage decision, not just a catalog list. ### Location needs to be specific enough to audit A row that says “cold storage” is too vague. A row that says “Building B, Room 214, fridge 2, top shelf” is useful. The same goes for hazard class. If the sheet groups oxidizers, flammables, and toxic materials clearly, it becomes much easier to check whether the physical arrangement still matches the written record. > **Practical rule:** the person doing the audit should be able to walk the room with the sheet in hand and spot mismatches without guessing. This is also where identity normalization matters. One product can appear under several names, grades, or concentrations, while one purchased item can contain multiple substances. A single free-text name is too weak to act as the primary key. The safer choice is a container ID linked to a normalized substance record, with the label's exact product name preserved alongside the structured fields. ## 6\. Link SDS, lot, and expiry evidence back to the row A chemical inventory spreadsheet is more defensible when each row points back to evidence. That evidence can be a supplier SDS, a photo of the label, or an imported record from a purchase or receiving process. The goal is not to create a paper archive inside the sheet. The goal is to reduce disputes about what the container was, when it arrived, and whether the label still matched the record. The SDS should live beside the inventory, not in a separate folder nobody checks. ACS's lab safety checklist treats an up-to-date inventory of hazardous materials and readily available SDSs as concrete safety controls [ACS laboratory safety checklist](https://www.acs.org/content/dam/pldp/center/lab-safety/publications/hazard-assessment/table-f-1-traditional-laboratory-safety-checklist.docx). A chemical inventory spreadsheet should therefore include a status field that shows whether the SDS is current and a direct reference to the document itself. ### Use photos for identity, not for guesswork A good label photo can settle disputes about catalog number, lot, concentration, or expiry. It should supplement structured fields, not replace them. If the printed label is unreadable, the row should be marked clearly and the container checked in person. - **Label photo:** confirm what was on the bottle at receiving time. - **SDS link:** connect the row to the authoritative hazard sheet. - **Lot number:** preserve batch traceability for the exact container. - **Expiry field:** capture the date from the container, not memory. A spreadsheet with these links is easier to reconcile during an audit or handover. It also supports a cleaner transition into notebook or ELN records, because the row already contains the evidence needed to explain why a container was used, moved, or retired. ## 7\. Version the sheet and tie it to the lab's note workflow A chemical inventory spreadsheet should not float free of the lab's documentation habit. Every significant change needs a trail. Save dated copies before internal audits or handovers. Keep a change column or version history. Record who altered what and when. That's the only way a sheet stays useful when two people disagree about whether a bottle was moved, consumed, or thrown out. The same logic appears in laboratory inventory guidance that treats records as living controls rather than static lists. Swansea's 2025 guidance requires chemicals to be added only after receipt, removed after use or disposal, linked to their SDSs, and audited at least annually. Central Michigan University's 2025 chemical hygiene plan goes further by calling for a real-time inventory and at least biannual updates for emergency response and regulatory reporting. Those rules point to the same conclusion, a snapshot can satisfy a filing rhythm, but it does not keep pace with bench reality [Swansea chemical storage guidance](https://hwb.swansea.ac.uk/media/HSR-10101-08_Safe-Storage,-Labelling-and-Stock-Management-of-Chemicals_v3.2_05-08-2025.pdf). > A spreadsheet is strongest when it captures the state of the shelf, and the notes capture why the state changed. That is where a lab note workflow matters. A bench scientist should be able to note that a container was opened, partially used, relocated, or disposed of, then update the inventory row after the fact without guessing. The sheet remains the controlled summary. The note or ELN keeps the context behind the change. For teams that want a simple workbook start, the chemical and SDS tracker in the [Lab Documentation Pack](https://www.verbalexperiment.com/lab-documentation-pack?angle=inventory) is the nearest ready-made option, and it also includes storage monitoring sheets and a free blank PDF sample on the page. ## Chemical Inventory Spreadsheet, Excel Workbook Overview | Item | Implementation complexity | Resource requirements | Expected outcomes | Ideal use cases | Key advantages | | --- | --: | --- | --- | --- | --- | | Lab Inventory System (Excel inventory workbooks) | Low, ready-made Excel templates; basic setup and minor customization | Minimal, Excel on PC, basic user skills; one-time US$9 (includes 2 months Verbex free) | Organized chemical/reagent/sample lists, SDS tracking, expiry and low‑stock flags, freezer box maps | Small academic, teaching, or startup labs that need a low‑cost inventory solution | Low cost, offline control, simple to use, first‑party templates (16 templates, 42 files), no subscription | ## Keep the Sheet Current With a Simple Review Routine A chemical inventory spreadsheet only stays useful if someone keeps touching it. The routine does not need to be elaborate. Review expiry and low-stock flags on a fixed schedule. Save a dated copy before any internal audit or handover. Record every change in a change column or version history so the team can see who altered what and when. That small habit does more than occasional cleanups because it keeps the sheet close to the shelf, not just close to the last audit. The review should start with the rows most likely to go stale, opened containers, near-expiry items, and anything stored in shared rooms. Then compare the written location against the physical storage area and reconcile what moved, what was consumed, and what was discarded. That is also the right time to clean up duplicate names, missing lot numbers, and vague locations like “freezer” or “bench cabinet.” If the team only checks the sheet when someone asks for it, the record will lag behind reality. The best version of this workflow is boring. One person owns the file. One naming convention is used across the lab. One status vocabulary is enforced. One review date is on the calendar. That is enough to keep the record auditable without turning it into a full inventory platform. For a ready-made starting point, the inventory workbooks in the complete [Lab Documentation Pack](https://www.verbalexperiment.com/lab-documentation-pack?angle=inventory) include a chemical and SDS tracker plus storage monitoring sheets. It's a paid product at US$9 one-time, the page offers a free blank PDF sample, and each purchase includes 2 months of Verbex free. For a small team that wants a practical workbook rather than a custom build, that's a reasonable next step. * * * Verbex helps scientists capture bench reality while their hands are busy, then turn those notes, images, and timers into a source-backed record for review and export. For a lab that wants better contemporaneous documentation around inventory changes, storage moves, and container-level notes, [Verbal Experiment](https://www.verbalexperiment.com) is worth a look.