How does the tracker decide a CAPA's stage?

From the dates. The stage is the discipline after the latest date filled in: no containment date means D3 Containment, a containment date means D4 Root cause, a root cause date means D5-D6 Corrective action, and so on to D8 Close-out and Closed. Because the stage is calculated, it cannot drift away from the record the way a typed status column does.

How is the due date set?

Opened date plus the close-within days of the CAPA's priority on Setup. With the example policy, a Medium CAPA opened on 2 April has 60 days and is due on 1 June; at a status date of 30 June it shows Overdue unless it has a closed date. Change the windows on Setup to your own procedure or your customer's requirement and every due date follows.

How is CAPA effectiveness measured?

After closure, record whether the effectiveness check passed (Effective), failed (Not effective) or has not been done yet (Pending), and the date it was verified. The rate is effective divided by effective plus not effective for checks verified in the 12 months to the status date; Pending results are left out. With 242 effective and 8 not effective, the rate is 96.8 percent, the same answer as the CAPA Effectiveness Rate calculator.

Why use a typed status date instead of today's date?

So the file is stable. A formula using TODAY changes every time the file opens, which makes a saved review impossible to reproduce and can make a CAPA flip to Overdue between two people looking at the same file. Type the review date on Setup; every status, days-open figure and the 12-month window are judged at that date.

What should I do when a CAPA fails its effectiveness check?

Record Not effective on the original row, with the verification date, and open a new CAPA row for the reopened problem. Keeping the failed result on the original row is what makes the effectiveness rate honest; clearing dates to reopen it would hide the failure.

Does this replace an 8D report?

No. The register holds one line per CAPA with the dates, owner, status and a one-line root cause. Keep the 8D analysis itself (team, problem description, 5 whys or fishbone, verification data) in your 8D report or the A3 template, and use the CAPA number to link them.

Quality Systems and Compliance

CAPA and 8D Tracker Excel Template

Track every corrective and preventive action through the 8D disciplines, see what is overdue and where CAPAs get stuck, and measure whether closed CAPAs actually worked.

Overview

This CAPA and 8D tracker is a working Excel register for quality managers and CAPA owners who need to know, at any review, which corrective actions are open, which are late, where they are stuck and whether the closed ones worked. It ships with twelve months of example CAPAs for a fictional moulder of medical and automotive parts, so the dashboard is populated the moment you open it; replace the example rows with your own and everything recalculates.

Instead of typing a stage or a status, you type the date each 8D discipline was reached: containment, root cause, corrective action implemented, recurrence prevented and closed. The workbook derives the current stage, sets the due date from the closure window of the CAPA's priority on Setup, and judges every CAPA against a status date you type (so the file never changes on its own between reviews). Closed CAPAs carry an effectiveness result; the effective-at-verification rate is effective divided by effective plus not effective over the CAPAs verified in the last twelve months, the same definition the CAPA Effectiveness Rate calculator on this site uses.

In practice, update the status date before the weekly review and read the dashboard from the top: the open and overdue counts, the open CAPAs by 8D stage chart that shows where work piles up, and the opened versus closed chart that shows whether the backlog is growing. Filter by owner for one-to-one reviews. The example data tells a typical story: three CAPAs are overdue, all of them moulding CAPAs stalled after root cause, while on-time closure and effectiveness sit below target.

What this template includes

CAPA Log table: number, opened date, source, type, priority, product, problem statement, owner and the date each discipline was reached (D3 contained to D8 closed)
Stage derived from the dates (D3 Containment to D8 Close-out), so it can never disagree with the record
Due date from your priority's closure window, days open, contained-in-time, on-time closure and a status word (Overdue, Due soon, In progress, Complete) against a typed status date
Effectiveness check recorded as Effective, Not effective or Pending with its verification date
Check column that names problems in words: duplicate CAPA number, priority not on the list, a date before opening or after the status date, closed without actions implemented
Dashboard with source and owner filters and KPI tiles: open, overdue, closed on time and effective at verification against your targets, average days to close, containment in time and the oldest open CAPA
Charts of open CAPAs by 8D stage (in progress, due soon, overdue) and opened, closed and open backlog by month, plus a by-owner table and a Pareto of sources
Method sheet with every formula, and a Verify sheet that re-checks the formulas against an independent reference (Self-check: PASS)

Suggested use case

Use it as the CAPA register for a plant or quality department, to run the weekly CAPA review, and to show an auditor or customer that corrective actions are closed on time and checked for effectiveness.

How to use it

On Setup, type the status date and your on-time and effectiveness targets, then replace the example priorities (with your containment and closure windows), sources and owners.
On CAPA Log, add one row per CAPA with its number, opened date, source, type, priority, product or process, problem statement and owner.
Type the date each discipline is reached, from containment (D3) to closure (D8); the stage, due date and status update themselves.
After closure, record the effectiveness check result and the date it was verified.
Before each review, update the status date on Setup, fix anything the Check column flags, then open the Dashboard and filter by source or owner.

Frequently Asked Questions

How does the tracker decide a CAPA's stage?: From the dates. The stage is the discipline after the latest date filled in: no containment date means D3 Containment, a containment date means D4 Root cause, a root cause date means D5-D6 Corrective action, and so on to D8 Close-out and Closed. Because the stage is calculated, it cannot drift away from the record the way a typed status column does.
How is the due date set?: Opened date plus the close-within days of the CAPA's priority on Setup. With the example policy, a Medium CAPA opened on 2 April has 60 days and is due on 1 June; at a status date of 30 June it shows Overdue unless it has a closed date. Change the windows on Setup to your own procedure or your customer's requirement and every due date follows.
How is CAPA effectiveness measured?: After closure, record whether the effectiveness check passed (Effective), failed (Not effective) or has not been done yet (Pending), and the date it was verified. The rate is effective divided by effective plus not effective for checks verified in the 12 months to the status date; Pending results are left out. With 242 effective and 8 not effective, the rate is 96.8 percent, the same answer as the CAPA Effectiveness Rate calculator.
Why use a typed status date instead of today's date?: So the file is stable. A formula using TODAY changes every time the file opens, which makes a saved review impossible to reproduce and can make a CAPA flip to Overdue between two people looking at the same file. Type the review date on Setup; every status, days-open figure and the 12-month window are judged at that date.
What should I do when a CAPA fails its effectiveness check?: Record Not effective on the original row, with the verification date, and open a new CAPA row for the reopened problem. Keeping the failed result on the original row is what makes the effectiveness rate honest; clearing dates to reopen it would hide the failure.
Does this replace an 8D report?: No. The register holds one line per CAPA with the dates, owner, status and a one-line root cause. Keep the 8D analysis itself (team, problem description, 5 whys or fishbone, verification data) in your 8D report or the A3 template, and use the CAPA number to link them.