GMP Deviation Log & CAPA Register Excel | Risk-Based Classification, Effectiveness Checks & Trending

Every unplanned event in a GMP lab or plant has to be reported, classified, investigated, closed on time and checked for recurrence - and every CAPA it raises has to be shown to work. This Excel deviation log and CAPA register tracks both, linked by deviation ID, with flags for the gaps reviewers look for.

Log each deviation with dates occurred, discovered and reported, area, category, containment, batch impact and disposition. An editable 3 x 3 impact x likelihood risk matrix suggests Minor, Major or Critical; QA sets the final class. The workbook sets a closure target by class (30 / 45 / 60 days by default, all editable), handles approved extensions, checks late reporting, and counts earlier deviations in the same category and area within a 365-day window so recurrences stand out.

The CAPA Register takes CAPAs from any source (deviation, audit, complaint, OOS / OOT, trend review) with due date, extension, completion, effectiveness check (due 90 days after completion by default) and closure. Flags catch CAPA required but not raised, closure with a batch still on hold, overdue items, root cause missing at closure, a class set below the matrix, and CAPAs closed before effectiveness is verified.

Trends shows 12 months by class, category, root cause and area, and the Dashboard gives status tiles and flag counts for your quality review. 15 fictional EXAMPLE deviations and 11 CAPAs trigger every flag so you can see how it works before clearing them.

What's included

• Deviation-CAPA-Log.xlsx (7 tabs)

• Start Here: quick start, colour key, rules, disclaimer

• Dashboard: status tiles, on-time closure, flag counts

• Deviation Log (300 rows): matrix class, closure target, days open, recurrence, linked CAPAs, status and flags

• CAPA Register (300 rows): due dates, extensions, effectiveness check, status and flags

• Trends: 12-month counts by class, category, root cause and area; Risk Matrix: editable 3 x 3

• Lists: areas, categories, root-cause categories, owner roles and settings

• User-Guide.pdf (8 pages): set-up, the deviation and CAPA lifecycle, every calculated column and flag, public references and disclaimer

• Fictional EXAMPLE data: 15 deviations and 11 CAPAs that show every flag

Who it's for

• QA and QC teams who still track deviations in a shared document or a plain list

• Small GMP manufacturers, start-ups, CDMOs and CROs without an eQMS

• Quality managers preparing monthly or quarterly quality reviews and trend reports

• Anyone who needs to show that CAPAs were raised, completed and checked for effectiveness

File formats and compatibility

• Microsoft Excel .xlsx workbook plus a PDF user guide, delivered as one zip

• Built for Microsoft Excel 2019/365 (.xlsx, no macros). Tested in LibreOffice Calc. Google Sheets import is possible but not officially supported.

• No add-ins, internet connection or macros required

• Instant digital download; no physical item is shipped

FAQ

Q: How are deviations classified?

A: Impact (1-3) and likelihood (1-3) pick a cell of the Risk Matrix, which you set from your own SOP. That gives a suggested Minor, Major or Critical class; QA enters the final class, and a class below the matrix is flagged so the justification is documented.

Q: How are CAPAs linked to deviations?

A: Type the deviation ID as the CAPA's source reference. The Deviation Log then counts linked and open CAPAs, and flags a deviation where a CAPA was required but none was raised. One deviation can have any number of CAPAs.

Q: I already own the QC Lab Audit-Prep Pack. Is this different?

A: Yes. The Audit-Prep Pack tracks audit findings and their CAPAs. This workbook covers the deviation process itself: reporting, risk classification, batch impact, closure targets, recurrence and 12-month trending, with a CAPA register for any source.

Q: Is it validated or compliant out of the box?

A: No. It is a template. No spreadsheet is compliant on its own, and this file is not a validated computerised system or a Part 11 / Annex 11 electronic records system. Verify the formulas with your own data and approve it under your own procedures before GMP use.

Q: Is the example data real?

A: No. All EX- deviations, CAPAs, areas and roles are fictional. Clear the white input cells before first use.

Disclaimer: Template only. This workbook is a tracking and calculation aid. It is not regulatory, legal or quality advice, it does not make your processes compliant, and it is not a validated computerised system: validate it for your intended use under your own procedures before relying on it for GMP decisions. Public guidelines (ICH, EU GMP, US 21 CFR, USP and others) are cited for concepts only. Rangefolio is not affiliated with or endorsed by ICH, USP, EDQM, FDA, EMA or PIC/S. All example data is fictional.