Required section · Section 2 of 6
The mental model: raw stays raw, formulas earn trust before they are locked
The normal, safe pattern for a laboratory spreadsheet analysis is not one sheet, it is a small set of sheets with one job each. A read-only source copy holds the export from the laboratory information system (LIS) exactly as received, so a laboratory can always retrieve an accurate copy of what was originally reported, which is what CLIA retention requirements at 42 CFR 493.1105 actually demand. A raw-data sheet holds a cleaned working copy: consistent data types, explicit units, missing values marked rather than left blank, duplicates resolved, and specimen identifiers intact.
An analysis sheet references the raw-data sheet by structured name, never by copying values, so the calculation chain always points back to one source of truth. An output sheet holds only what is meant to be read: tables and charts built for a huddle, a report, or an audit.
Continual-improvement guidance from CLSI QMS06 (4th edition, 2025) frames this kind of workbook build the same way it frames any process improvement: identify the opportunity, select an approach, implement it, evaluate the effect, and sustain the gain. Applied to a spreadsheet, sustaining the gain means the workbook still works the same way six months later, which is why locking and version control matter as much as getting the first formula right.
A calculation that feeds a patient result is not exempt from validation because it lives in a spreadsheet instead of an analyzer. CAP's accreditation guidance treats a laboratory-developed calculation as a test-system component: it needs objective validation evidence, a written procedure, and laboratory-director approval before clinical use. Describe the requirement to a colleague as validating a calculation before clinical use, not by citing a specific checklist item number you have not personally verified.
Before trusting any formula in a laboratory spreadsheet, know which sheet it lives on, what it is allowed to touch, and who signed off on it.
Illustrative drawing — this picture was drawn rather than captured.
Six-stage calculation audit, from import and preservation through sustained use
Import and preserve
Load the raw export into a read-only source copy and a separate working raw-data sheet; the source copy is never edited.
Write the specification
Draft each calculation against a written specification, using structural references rather than copied values.
Test known, boundary, and error cases
Run known-answer, missing-value, and out-of-range inputs through the calculation. A failed test returns the work to correction and retesting before review.
Peer review
A qualified peer reviews the specification, formula, and successful test evidence under the current procedure.
Director or authorized approval
Obtain the required director or authorized approval before clinical use.
Sustain and monitor
Monitor the approved workbook and repeat the applicable audit after a change.
Knowledge checks
Reading and checks are open. Sign in only to save.
Knowledge check 1
Section status
Finish this section
Reading and checks are open. Sign in only to save.
The module finishes after every required section is marked done and every check in those sections is correct.