Quality & InspectionFree worksheetPDF, Excel, Word exportManufacturing

Defect Pareto Analysis Worksheet

A Pareto analysis ranks problems so the team works on the few that cause most of the loss. This worksheet records defect types, counts and cost, and does the cost arithmetic so you can build the chart and defend the priority list.

Download:

Defect type	Source	Count	Cost per defect	Total cost	Rework minutes each	Lost hours
				$767		7.8667
				$2,812		0
				$624		8.6667
				$2,635		0
				$66		1.8333
				$180		2.25

Total defects

306

Total cost of defects

$7,084

Total lost hours

20.6167

Largest single defect cost

$2,812

Average cost per defect

$23

Largest defect share of total cost

39.7%

Sort rows by Total cost, largest first, before drawing the Pareto chart in Excel, then add the cumulative percentage column. Rank by cost as well as by count, because the most frequent defect is rarely the most expensive.

Automatic Pareto from scrap codes

Optional: tell us how scrap and rework reasons are recorded in your ERP and we can outline an automated Pareto report.

Optional. The tool and every export work without this. No spam.

How to use the worksheet

List each defect type found over a defined period, such as a month or a production order batch. Enter the count and the typical cost of one defect, which can be scrap value plus rework labor. Add rework minutes if you want lost hours. The sheet calculates total cost per defect type, overall totals and the largest defect share. Export to Excel, sort descending by cost, add a cumulative percentage column and insert a combined bar and line chart.

Use consistent defect definitions across shifts.
Group rare defects into an Other row at the end.
Rank by cost and by count and compare the two lists.

The method

The Pareto principle, often called the 80/20 rule, observes that a small number of causes generate most of the effect. In quality work this typically means two or three defect types account for most of the cost. Bars show each category in descending order and a line shows the cumulative percentage. Categories reaching about 80 percent cumulative are the vital few. The chart is a prioritisation tool in the Measure and Analyze phases of DMAIC, and you repeat it after improvements to see what moved.

Worked example

In the example data, burrs are the most frequent defect with 118 occurrences but cost 6.50 each, about 767 in total. Porosity occurs only 31 times but at 85 each costs about 2,635, the largest total. Oversize diameter costs about 2,812 in scrap. Ranking by count points the team at deburring, while ranking by cost points at oversize parts and casting porosity. Both views matter, and the cost view usually drives project selection.

Common mistakes

A Pareto of counts alone can send teams after cheap, frequent problems. Using categories that are too broad, such as Other at 40 percent, destroys the value of the chart. Collecting one period only can mislead if a rare event dominated it. After fixing the top item, collect fresh data and redo the analysis, because the ranking will change. Where possible, stratify by machine, shift, supplier or part to find the specific source.

Frequently Asked Questions

How many categories should a Pareto chart have?

Typically 6 to 10 bars plus an Other bar. Too few hides detail and too many makes the chart unreadable. If Other is the largest bar, your categories are probably too coarse and need to be split further.

Should I rank by count or cost?

Do both. Count shows where the process fails most often. Cost, which includes scrap, rework, warranty and delay, shows what hurts the business. Project selection is usually better based on cost, with customer impact as a third lens.

What does the 80/20 rule mean here?

It is a rule of thumb that roughly 80 percent of the effect comes from 20 percent of the causes. The exact split varies. What matters is that the distribution is uneven, which lets you focus effort on the few categories that dominate.

How do I make the chart in Excel?

Export this worksheet, sort by total cost descending, add a column of cumulative percentage, then insert a combo chart with columns for cost and a line on the secondary axis for cumulative percent. Excel also has a built-in Pareto chart type.

If scrap and rework are logged in your ERP, we can help you pull them into a private, always-current Pareto report by cause code, work center and part.

Talk to an Expert Explore More Free Tools

Related Tools

Quality & Inspection

More Quality & Inspection tools

See all

Back to All Tools