# Defect Pareto Analysis Worksheet > Source: https://netray.co/tools/defect-pareto-analysis-worksheet > Publisher: Netray (netray.co), AI and integration engineering for ERP and manufacturing systems. > Section: Free Tools ## Short answer Log defect categories, counts and unit cost, get the cost impact of each and the totals you need to build a Pareto chart. ## Category quality ## Industry Manufacturing ## Audience - Quality engineer - Production supervisor - Six Sigma Green Belt ## Tool **Kind:** worksheet **Mode:** editable **Currency:** $ **Columns:** #### Defect type **Key:** defect **Type:** text #### Source **Key:** category **Type:** select **Options:** - Machine - Material - Method - Manpower - Measurement - Environment #### Count **Key:** count **Type:** number #### Cost per defect **Key:** unit_cost **Type:** number **Format:** currency #### Total cost **Key:** total_cost **Type:** number **Expr:** count * unit_cost **Format:** currency #### Rework minutes each **Key:** minutes **Type:** number #### Lost hours **Key:** lost_hours **Type:** number **Expr:** count * minutes / 60 **Format:** decimal **Rows:** **Defect:** Burr on bore edge **Category:** Machine **Count:** 118 **Unit cost:** 6.5 **Minutes:** 4 **Defect:** Oversize diameter **Category:** Machine **Count:** 74 **Unit cost:** 38 **Minutes:** 0 **Defect:** Surface scratch **Category:** Method **Count:** 52 **Unit cost:** 12 **Minutes:** 10 **Defect:** Porosity in casting **Category:** Material **Count:** 31 **Unit cost:** 85 **Minutes:** 0 **Defect:** Wrong label **Category:** Manpower **Count:** 22 **Unit cost:** 3 **Minutes:** 5 **Defect:** Gauge variation reject **Category:** Measurement **Count:** 9 **Unit cost:** 20 **Minutes:** 15 **Totals:** #### Total defects **Key:** count **Agg:** sum **Format:** integer #### Total cost of defects **Key:** total_cost **Agg:** sum **Format:** currency #### Total lost hours **Key:** lost_hours **Agg:** sum **Format:** decimal #### Largest single defect cost **Key:** total_cost **Agg:** max **Format:** currency **Summary:** #### Average cost per defect **Key:** avg_cost **Expr:** sum_total_cost / sum_count **Format:** currency #### Largest defect share of total cost **Key:** top_share **Expr:** max_total_cost / sum_total_cost * 100 **Format:** percent **Primary:** true **Notes:** 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. ## Content **Intro:** 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. **Sections:** #### How to use the worksheet **Body:** 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. **Bullets:** - 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 **Body:** 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 **Body:** 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 **Body:** 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. **Cta:** 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. **Faq:** #### How many categories should a Pareto chart have? **Answer:** 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? **Answer:** 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? **Answer:** 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? **Answer:** 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. ## Search terms pareto analysis template excel, defect pareto chart worksheet, pareto analysis quality control, pareto chart template, cost of poor quality pareto, vital few trivial many worksheet, scrap pareto template, 80 20 defect analysis --- Talk to an engineer: https://netray.co/contact?intent=erp_ai | Machine catalog: https://netray.co/agent/catalog.json