FMEA Template in Excel: Score Failure Modes, Calculate RPN and Track Actions
A practical process FMEA template in Excel for small manufacturers: columns, severity, occurrence and detection scales, risk priority number, prioritization rules, action tracking and a worked candle-making example.
An FMEA (failure mode and effects analysis) lists, for each process step or component, the ways it can fail, the effects and causes of each failure, and scores severity, occurrence and detection so you can tackle the biggest risks first. An FMEA template in Excel calculates the risk priority number (RPN), flags critical rows and tracks actions until a re-score shows the risk has dropped. This guide focuses on the process FMEA, the most useful kind for small workshops and makers.
Template columns
| Column | Question |
|---|---|
| Process step | Which operation are we analyzing? |
| Failure mode | How can this step go wrong? |
| Effect | What does the customer or the next step notice? |
| Severity (S) | How serious is the effect? (1–10) |
| Cause | Why does the failure happen? |
| Occurrence (O) | How often does the cause occur? (1–10) |
| Current controls | What prevents or detects it today? |
| Detection (D) | How likely is it to reach the customer undetected? (1 = certainly caught, 10 = not caught) |
| RPN | S × O × D |
| Action, owner, due date | What will we do, who and when? |
| Revised S, O, D and RPN | How much did the risk drop? |
Write the scales into the workbook
An FMEA is only comparable if everyone scores the same way. Add a Scales tab describing each value for your business. A rough guide:
| Score | Severity | Occurrence | Detection |
|---|---|---|---|
| 9–10 | Safety hazard or legal non-compliance | Almost every batch | No effective check |
| 7–8 | Product unusable, certain complaint | Frequent | Occasional visual check |
| 4–6 | Noticeable defect or rework | Occasional | Sample inspection |
| 2–3 | Minor, barely noticeable | Rare | Systematic check at the workstation |
| 1 | No noticeable effect | Practically never | Automatic 100% check |
Restrict S, O and D to whole numbers from 1 to 10 with data validation.
Formulas and priorities
- RPN:
=[@S]*[@O]*[@D](1 to 1,000) - Priority:
=IF([@S]>=9,"High (severity)",IF([@RPN]>=HighThreshold,"High",IF([@RPN]>=MediumThreshold,"Medium","Low"))) - Risk reduction:
=IF([@RevisedRPN]="","",1-[@RevisedRPN]/[@RPN])
The severity rule matters: a failure with severity 9 or 10 needs action even if its RPN is low. The joint AIAG and VDA FMEA handbook, widely used in automotive supply, replaces the RPN with an action priority table based on the combination of S, O and D; if you supply that industry, follow its method.
Example: a small candle-making workshop
| Step | Failure mode | Effect | S | O | D | RPN |
|---|---|---|---|---|---|---|
| Melting | Wax overheated | Fire risk, discolored wax | 10 | 2 | 4 | 80 |
| Wicking | Wick off-center | Tunneling, uneven burn | 6 | 5 | 5 | 150 |
| Fragrance | Fragrance load too high | Sweating, weak burn | 5 | 4 | 6 | 120 |
| Labeling | Safety label missing | Non-compliant product | 8 | 3 | 3 | 72 |
Two rows get priority. Wicking has the highest RPN: a centering tool and a visual check of every tray bring occurrence to 2 and detection to 3, so the revised RPN is 6 × 2 × 3 = 36, a 76% reduction. Melting has a lower RPN but severity 10, so it is treated as high priority too: a thermometer with an alarm and a rule never to leave the melter unattended go into the procedure, written up with the SOP template.
Running the FMEA as a team
- Map the process step by step before filling in the table.
- Bring in the people who do the work; the best causes come from them.
- Score together, with the scales visible.
- Give every action an owner and a date.
- Re-score only once the action is in place and checked.
Review the FMEA when you change a supplier, a machine or a recipe, and whenever a complaint arrives that the analysis did not anticipate.
How it fits with other tools
Maintenance actions that come out of the FMEA belong in the preventive maintenance schedule template. For project risks rather than process risks, use the risk register. Who is trained on each check is shown in the skills matrix. More guides are in Small-Scale Manufacturing.
Limits
An FMEA is an analysis tool. It does not replace safety regulations, product labeling requirements or customer and certification requirements; it supports them.
Frequently asked questions
What is the difference between a design and a process FMEA?
A design FMEA looks at how a product can fail because of how it is designed; a process FMEA looks at how production can create defects. Small makers using proven designs usually start with the process FMEA.
What RPN is considered high?
There is no universal threshold. Each business sets its own, such as 100 or 120, and always acts on severity 9 and 10.
How often should the FMEA be updated?
When the process, product, equipment or supplier changes, and at least once a year.
Does it work in Google Sheets?
Yes. Formulas, data validation and conditional formatting work the same way, and the team can edit one shared file.