Corrective Action Plan Template Excel: 18 Columns, Formulas and Example
9 min read ยท 2026-11-18
A corrective action plan template excel workbook needs one row for each finding and 18 columns. The columns track the finding from the day it is raised to the effectiveness check, the closing date and the approver. Most free templates stop at "action, owner, due date". This guide covers the rest: the columns an auditor will look for, dropdown lists that keep the data clean, formulas that flag late or badly closed rows, and a summary tab that answers "what is open, what is late, and who owns it" in one screen.
The steps of the corrective action process itself, such as containment, root cause and verification, are covered in other guides. This one is about the spreadsheet.
The roadmap at a glance
Goal: A working corrective action workbook that the whole team uses and an auditor can read. Duration: 1 week
Set up the register (Day 1)
Build the main sheet before anyone enters data.
- Create the 18 column headers in the order below.
- Turn the range into an Excel table named CAP.
- Freeze the header row and the CAP ID column.
Milestone: The empty register is saved in a shared folder.
Add dropdowns and checks (Day 2)
Make wrong entries hard to type.
- Build a Lists tab with the values for Source, Severity, Status and Result.
- Apply data validation to those four columns.
- Format the date columns as dates.
Milestone: Every coded column accepts only values from the list.
Add formulas and flags (Day 3)
Let the sheet show problems on its own.
- Add the Days open helper column.
- Add the overdue rule and the "closed without proof" rule.
- Test both rules with a dummy row.
Milestone: A late row turns red and a badly closed row turns orange.
Build the summary tab (Days 4 to 5)
Give managers one screen instead of 60 rows.
- Add counts of open, overdue and major findings.
- Add a list of owners with their open and late actions.
- Add the average days to close.
Milestone: The summary updates by itself when the register changes.
Load real findings and run the first review (Days 6 to 7)
Move live findings into the new sheet.
- Enter every open finding, one row per action.
- Assign one owner per row.
- Hold a 15-minute review using the summary tab.
Milestone: The team has run its first weekly review from the workbook.
The 18 columns of a corrective action plan spreadsheet
Set the columns up in this order, from A to R, so each row reads like the story of one problem. The letters matter because the formulas below use them.
Keep Closed date and Approver in separate columns. If a name sits in the same cell as the date, Excel reads the cell as text and every formula that counts days breaks.
Two optional columns help in manufacturing: part number or batch, and quantity affected. Put them after column E so the core letters do not move. If you add them, shift every letter in the formulas below by two.
- A, CAP ID: a unique number such as CAP-2026-014. If one finding needs several actions, use 014a and 014b.
- B, Date raised: the day the problem was found.
- C, Source: dropdown with internal audit, external audit, customer complaint, incident, inspection and supplier.
- D, Requirement: the clause, specification or procedure that was not met.
- E, Nonconformity: what was found, where and how much. Facts, not opinions.
- F, Severity: dropdown with major, minor and observation.
- G, Correction: what was done right away to contain the problem.
- H, Root cause method: 5 Whys, fishbone, Pareto or other.
- I, Root cause: one clear sentence.
- J, Corrective action: what will change, written as a task with a verb.
- K, Owner: one person, never a team.
- L, Due date.
- M, Status: dropdown with open, in progress, done, verified and closed.
- N, Evidence: a file name or document reference.
- O, Effectiveness check: how and when you will check that the fix worked.
- P, Effectiveness result: dropdown with effective, not effective and pending.
- Q, Closed date: a date only, so the formulas can use it.
- R, Approver: the name of the person who signed off the closure.
Dropdown lists that keep the data clean
Filters and counts only work if everyone types the same words. "Closed", "closed " and "Done/closed" are three different values to Excel.
When you need a new source or status later, add it to the Lists tab. Every dropdown picks it up.
- Create a tab called Lists. Put each list in its own column: Source, Severity, Status, Result.
- Select column C in the register, open Data, then Data Validation, choose List and point the source to the Source column of the Lists tab. Repeat for F, M and P.
- In the Error Alert tab of the same window, keep the style on Stop, so a typed value outside the list is refused.
- Format columns B, L and Q as dates. A date typed as text will not count as late.
Formulas that flag late and badly closed rows
Add a helper column S called Days open. In S2:
=IF(B2="","",IF(Q2="",TODAY()-B2,Q2-B2))
An open row counts days up to today. A closed row freezes at the number of days it took to close. The first test keeps empty rows blank.
Then add two conditional formatting rules on the range A2:S500, with Use a formula to determine which cells to format. The dollar sign before each column letter keeps the rule looking at the right column on every row. Without it, Excel shifts the reference cell by cell and the colors land in the wrong place.
The closed without proof rule is the one most templates leave out, and it is the one that saves you in an audit. A row closed on the day the action was done, before anyone checked that it worked, shows up at once.
- Overdue, in red: =AND($L2<>"",$L2<TODAY(),OR($M2="Open",$M2="In progress")). A row turns red when its due date has passed and the action is still not done. Rows marked done, verified or closed stay white.
- Closed without proof, in orange: =AND($M2="Closed",OR($N2="",$P2<>"Effective",$Q2="",$R2="")). A row turns orange when someone marks it closed but the evidence, the effectiveness result, the closing date or the approver is missing.
A summary tab: open, late and by owner
A register of 60 rows is unreadable in a management review. Add a tab called Summary that reads from the table named CAP, with four counts at the top.
In the weekly review, open this tab first. Lead with three numbers: findings open, actions overdue and major findings open. Then go through the owners with late actions, one line each.
- Open findings: =COUNTIFS(CAP[Status],"<>Closed",CAP[CAP ID],"<>")
- Overdue actions: =COUNTIFS(CAP[Due date],"<"&TODAY(),CAP[Status],"Open")+COUNTIFS(CAP[Due date],"<"&TODAY(),CAP[Status],"In progress")
- Major findings open: =COUNTIFS(CAP[Severity],"Major",CAP[Status],"<>Closed")
- Average days to close: =AVERAGEIFS(CAP[Days open],CAP[Status],"Closed")
The owner block below the counts
Copy B10 and C10 down as far as the owner list goes. A pivot table on Owner and Status gives the same view without formulas, but you have to refresh it by hand.
- In A10, list each owner once. In Excel 365, =UNIQUE(FILTER(CAP[Owner],CAP[Owner]<>"")) fills the list by itself. In older versions, type the names.
- In B10, open actions for that owner: =COUNTIFS(CAP[Owner],A10,CAP[Status],"<>Closed")
- In C10, late actions for that owner: =COUNTIFS(CAP[Owner],A10,CAP[Due date],"<"&TODAY(),CAP[Status],"Open")+COUNTIFS(CAP[Owner],A10,CAP[Due date],"<"&TODAY(),CAP[Status],"In progress")
A filled row: an ISO 9001 audit finding
Here is one finished row, column by column, from a typical internal audit finding on an assembly line.
- A, CAP ID: CAP-2026-014
- B, Date raised: the audit date
- C, Source: Internal audit
- D, Requirement: ISO 9001 clause 7.1.5, monitoring and measuring resources
- E, Nonconformity: two torque wrenches in use on assembly line 2 were past their calibration due date.
- F, Severity: Major
- G, Correction: both wrenches were taken out of use and sent for calibration, and parts assembled with them since their last valid calibration were rechecked.
- H, Root cause method: 5 Whys
- I, Root cause: purchasing had no step to add newly bought tools to the calibration register.
- J, Corrective action: add a "register in calibration list" step to the purchasing procedure, and check the register monthly against the tools on the line.
- K, Owner: quality manager
- L, Due date: the date agreed for the procedure change
- M, Status: In progress
- N, Evidence: purchasing procedure revision number, first monthly check record
- O, Effectiveness check: three monthly register checks with no overdue tool found on the line
- P, Effectiveness result: Pending
- Q, Closed date: empty until the third check passes
- R, Approver: empty until the quality lead signs off
How the filled row behaves in the sheet
While Q is empty, column S keeps counting, and the row turns red the day after its due date if the status is still In progress. If someone sets the status to Closed while P still says Pending, the row turns orange.
Keep the full 5 Whys in a second tab or an attached file and put only the conclusion in column I. The reader sees the cause at a glance, and an auditor can still open the detail.
What an auditor checks in the sheet
ISO 9001:2015 clause 10.2.2 asks you to keep documented information as evidence of the nature of each nonconformity, the actions taken and the results of any corrective action. In this workbook, that means columns E, G, J, N and P can never be empty on a closed row. The orange rule above checks N and P for you. Add E, G and J to it if your auditor is strict.
Auditors also look for two things a spreadsheet hides easily:
- Correction and corrective action mixed in one cell. Columns G and J keep them apart, as clause 10.2 does.
- "Done" in the evidence column. Column N should name a real document: a procedure revision, a training record or a check sheet.
Excel, Word or PDF for a corrective action plan
The formats do different jobs. Excel or Google Sheets is the working register for many findings, with filters, flags and the summary tab. Word suits a formal reply to a single finding, such as a supplier corrective action request, with room for a narrative and signatures. PDF freezes a signed version to send to a customer or a certification body. Use the same CAP ID in every file so the trail stays clear.
Keeping one version of the workbook
Excel sends no reminders, and copies drift apart once people email the file. Keep one master file in a shared folder. Give status and closing rights to one person. Lock the Lists tab and the formula column S with Review, then Protect Sheet, so nobody overwrites a formula by mistake. Review the summary tab on a fixed day each week.
When the plan has to be presented to a customer or a board, the row view is not enough. Show the phases from containment to verification with their dates and milestones, so the reader sees when each finding will be closed and how you will prove it.
Common mistakes to avoid
- Putting the closing date and the approver in one cell turns the date into text, so keep them in separate columns or every day count breaks.
- Letting people type statuses by hand creates values Excel cannot count together, so use dropdown lists fed by a Lists tab.
- Closing a row on the day the action is done hides fixes that never worked, so keep it open until the effectiveness result says effective.
- Naming a team as the owner means nobody chases the action, so put one person in the Owner column.
- Writing the correction and the corrective action in the same cell blurs what was contained and what was changed, so keep columns G and J apart.
- Emailing copies of the workbook leaves several versions in circulation, so keep one master file in a shared folder and protect the formula column.
Frequently asked questions
What columns should a corrective action plan template in Excel have?
At minimum: ID, date raised, source, requirement, nonconformity, severity, correction, root cause, corrective action, owner, due date, status, evidence, effectiveness check, effectiveness result, closed date and approver. Keep the closed date and the approver in separate columns so date formulas work.
How do I highlight overdue corrective actions in Excel?
Use conditional formatting with a formula such as =AND($L2<>"",$L2<TODAY(),OR($M2="Open",$M2="In progress")), where L is the due date and M the status. Apply it to the whole data range and pick a red fill.
Can I use the same template in Google Sheets?
Yes. Data validation, conditional formatting with custom formulas and COUNTIFS all work in Google Sheets. Structured references such as CAP[Status] do not, so replace them with column ranges like M:M.
How do I track corrective actions by owner?
Add a summary tab with each owner's name and two COUNTIFS formulas: one for open actions and one for actions past their due date. A pivot table on Owner and Status gives the same view if you prefer not to write formulas.