Pareto analysis of defects (80/20 chart)

Ranks defect categories by count and by cost, flags the vital few and tracks them by line and week.

Downloads are included in the $19.85 yearly membership. Sign in

XLSXCSVODSXMLNumbers

	A	B	C	D	E	F
1	Pareto analysis of defects
2	Example data — replace the blue input cells with your own.
3
4	DEFECTS LOGGED	TOTAL COST		TOP CATEGORY		VITAL-FEW CATEGORIES
5	1,817	$983		Short shot		4
6	160 log rows	Quantity times cost per part		34% of defects		Hold the first 80% of defects
7
8	Settings
9	Vital-few threshold	80%	Categories up to and including the one that crosses this share are vital few.
10	First day of the 13-week window	Jul 6, 2026	Use a Monday. Week 1 starts on this date; each week is seven days.
11
12	Top 6 categories by count
13	Category	Count	Count bar	Cumulative %	Cumulative progress	Vital few?
14	Short shot	618	████████████████████	34%	███████░░░░░░░░░░░░░	Vital few
15	Flash	414	█████████████	57%	███████████░░░░░░░░░	Vital few
16	Sink marks	317	██████████	74%	███████████████░░░░░	Vital few

Showing the first 16 of 39 rows and 7 of 7 columns. Cells with formulas show the formula on hover.

	A	B	C	D	E	F	G	H	I	J
1	Defect log
2	One row per defect record. Quantity is the number of defective parts; cost per part is the scrap and rework cost of one part.
3	Enter one row per defect record in the blue cells. Week, cost and the row check are formulas. Capacity: 500 rows.
4
5	Date	Week	Line	Shift	Defect category	Quantity (parts)	Cost per part (USD)	Cost (USD)	Row check	Quantity bar (largest record = 16 blocks)
6	Jul 6, 2026	1	Line 3	Night	Short shot	16	$0.41	$6.56	OK	██████████████
7	Jul 7, 2026	1	Line 2	Night	Short shot	6	$0.43	$2.58	OK	█████
8	Jul 7, 2026	1	Line 3	Night	Flash	15	$0.19	$2.85	OK	█████████████
9	Jul 7, 2026	1	Line 3	Day	Short shot	11	$0.40	$4.40	OK	██████████
10	Jul 8, 2026	1	Line 1	Night	Splay	7	$0.23	$1.61	OK	██████
11	Jul 8, 2026	1	Line 2	Day	Short shot	16	$0.39	$6.24	OK	██████████████
12	Jul 8, 2026	1	Line 3	Day	Flash	16	$0.19	$3.04	OK	██████████████
13	Jul 9, 2026	1	Line 2	Evening	Short shot	10	$0.42	$4.20	OK	█████████
14	Jul 9, 2026	1	Line 3	Day	Short shot	9	$0.41	$3.69	OK	████████
15	Jul 10, 2026	1	Line 2	Night	Flash	17	$0.17	$2.89	OK	███████████████
16	Jul 11, 2026	1	Line 1	Day	Flash	6	$0.18	$1.08	OK	█████

Showing the first 16 of 506 rows and 10 of 10 columns. Cells with formulas show the formula on hover.

	A	B	C	D	E	F
1	Pareto ranking of defect categories
2	Counts and costs by category, ranked from largest to smallest, with cumulative shares and the vital few.
3
4	1. Category list (input, 12 slots) with counts and costs from the log
5	Slot	Category	Count	Cost (USD)	Count sort key	Cost sort key
6	1	Short shot	618	$260	618.00	259.54
7	2	Flash	414	$74	414.00	74.26
8	3	Sink marks	317	$87	317.00	86.52
9	4	Warp	130	$120	130.00	119.81
10	5	Burn marks	79	$44	79.00	43.82
11	6	Splay	54	$12	54.00	11.81
12	7	Weld lines	63	$70	63.00	69.80
13	8	Contamination	68	$162	68.00	162.05
14	9	Dimensional	41	$134	41.00	134.01
15	10	Color variation	33	$21	33.00	21.10
16	11

Showing the first 16 of 52 rows and 11 of 11 columns. Cells with formulas show the formula on hover.

	A	B	C	D	E	F	G	H
1	Defects by line and category
2	Counts by category and line, heat shading against each category's largest line, and the top category on each line.
3
4	1. Counts by category and line
5	Defect category	Line 1	Line 2	Line 3	Total	Share	Heat (lines 1 to 3)	Top line
6	Short shot	158	260	200	618	34.0%	▓█▓	Line 2
7	Flash	174	128	112	414	22.8%	█▓▓	Line 1
8	Sink marks	138	117	62	317	17.4%	██▒	Line 1
9	Warp	56	39	35	130	7.2%	█▓▓	Line 1
10	Burn marks	19	23	37	79	4.3%	▓▓█	Line 3
11	Splay	20	15	19	54	3.0%	█▓█	Line 1
12	Weld lines	11	23	29	63	3.5%	▒▓█	Line 3
13	Contamination	15	42	11	68	3.7%	▒█▒	Line 2
14	Dimensional	34	0	7	41	2.3%	█░▒	Line 1
15	Color variation	0	19	14	33	1.8%	░█▓	Line 2
16

Showing the first 16 of 28 rows and 8 of 8 columns. Cells with formulas show the formula on hover.

	A	B	C	D	E	F	G	H	I	J	K	L
1	Weekly defect trend
2	Defects by category and week across the 13-week window, with sparklines and weekly totals.
3
4	1. Defects by category and week (sparklines use the Min and Max columns)
5	Defect category	1	2	3	4	5	6	7	8	9	10	11
6	Week starts (Monday)	Jul 6, 2026	Jul 13, 2026	Jul 20, 2026	Jul 27, 2026	Aug 3, 2026	Aug 10, 2026	Aug 17, 2026	Aug 24, 2026	Aug 31, 2026	Sep 7, 2026	Sep 14, 2026
7	Short shot	68	47	0	38	51	51	0	24	64	92	72
8	Flash	54	61	43	33	28	47	31	10	30	39	8
9	Sink marks	20	27	8	25	11	13	30	32	8	32	46
10	Warp	0	12	9	11	0	23	6	0	7	19	5
11	Burn marks	0	0	0	8	10	7	11	4	11	0	0
12	Splay	7	15	0	9	0	7	0	0	10	0	6
13	Weld lines	0	0	0	0	18	10	24	11	0	0	0
14	Contamination	0	0	11	0	15	0	0	15	0	0	12
15	Dimensional	0	22	0	7	12	0	0	0	0	0	0
16	Color variation	0	0	0	0	0	0	0	0	0	7	0

Showing the first 16 of 40 rows and 12 of 18 columns. Cells with formulas show the formula on hover.

	A
1	Pareto analysis of defects: notes
2	Notes: what this workbook does, how to use it and the method behind it.
3
4	What it does
5	Ranks defect categories by count and by cost. This is the Pareto (80/20) view of a defect log.
6	Holds up to 500 defect records over 13 weeks. The example data is 160 records from a fictional injection molding plant with three lines.
7	Marks the vital few: the categories that together make up the first 80% of defects, or any threshold set on the Dashboard.
8	Splits the same records by line, by category and by week, with bars, shading, sparklines and reconciliation checks.
9
10	How to use it
11	1. On the Dashboard, set the vital-few threshold and the first day of the 13-week window. Use a Monday for the first day.
12	2. On the Defect log, enter one row per defect record: date, line, shift, defect category, quantity of defective parts and cost per part. Leave unused rows empty.
13	3. On the Pareto tab, keep the category names in the 12 slots in step with the log. The Row check column on the Defect log flags any category that is not in the list.
14	4. Read the count ranking and the cost ranking on the Pareto tab. The threshold line marker shows the row where the cumulative share reaches the threshold.
15	5. Use the By line and Trend tabs to see where and when the top categories occur. The checks on the Dashboard should all read OK.
16

Showing the first 16 of 40 rows and 1 of 1 columns. Cells with formulas show the formula on hover.

What does this template do?

A Pareto chart ranks defect categories from most to least frequent, so a quality team can see which few causes produce most of the defects. This workbook is for process, quality and production engineers who log defects by line and shift and want the ranking to update as the log grows.

The Defect log holds up to 500 records. Each row has a date, a line, a shift, a defect category, a quantity of defective parts and a cost per part. The Pareto tab sums counts and costs by category, sorts them with LARGE and INDEX/MATCH, and computes the cumulative share. A category counts as vital few when the cumulative share before it is below the threshold on the Dashboard, 80% by default. The By line and Trend tabs split the same records by line and by week.

The example data is fictional: 160 defect records from an injection molding plant with three lines over 13 weeks. Short shot, flash and sink marks make up most of the counts, while contamination and dimensional defects cost more per part.

What’s inside

Counts and costs by category, ranked with LARGE and INDEX/MATCH
Vital-few flag and a threshold marker on the cumulative share, 80% by default
Count ranking and cost ranking side by side, so a rare but costly category stands out
Line by category grid with heat shading and the top category on each line
Weekly trend with sparklines, week-over-week change and reconciliation checks

Which tabs does the workbook have?

Tab	What it holds
Dashboard	KPI tiles, the threshold and window settings, top categories, line and weekly views, and checks.
Defect log	Room for 500 defect records, with a week number, cost per record and a row check.
Pareto	Category list, count and cost sums, count and cost rankings with cumulative shares, bars and vital-few flags.
By line	Counts by category and line, heat shading, the top category per line and line totals with bars.
Trend	Weekly counts by category with sparklines, weekly totals with bars and week-over-week change.
Notes	What the workbook does, how to use it, the formulas, assumptions and limits.

What formulas does this template use?

This template holds 2,825 formulas in 4,031 cells across 6 tabs, so 70% of its cells calculate. They use 18 distinct functions; the longest formula is 1,939 characters and 1,385 of them read from another tab.

Function	Uses	What it does
`IF`	4,522	one result when a test is true, another when false
`MAX`	2,531	largest value
`OR`	2,052	true when any test is true
`ROUND`	831	rounds to a number of digits
`MIN`	625	smallest value
`REPT`	612	repeats text
`COUNTIF`	504	counts cells meeting one condition
`INT`	500	rounds down to a whole number
`SUMIFS`	221	adds values meeting several conditions
`CHOOSE`	205	picks the nth value from a list
`ISNUMBER`	169	true for a number
`SUM`	84	adds numbers

The 12 most used of 18 functions. Counted from the workbook itself. Only functions that Excel, LibreOffice Calc, Google Sheets and Apple Numbers evaluate the same way are used, so the formulas survive every download format.

How do you use it?

Set the vital-few threshold and the first day of the 13-week window on the Dashboard.
Enter defect records on the Defect log: date, line, shift, category, quantity of parts and cost per part.
Keep the category names on the Pareto tab in step with the log; the Row check column flags any category that is not in the list.
Read the vital few on the Dashboard or the Pareto tab, then compare the count ranking with the cost ranking.
Use By line and Trend to see where and when the top categories occur.

What is it good for?

Ranking scrap causes for a weekly quality review
Choosing which defect to attack first in an improvement event
Comparing defect counts and cost by production line
Checking whether the top categories fall after a process change

Questions about this sheet

What does the vital-few flag mean?

A category is flagged vital few when the cumulative share of the categories ranked above it is below the threshold. With the default 80%, the flagged categories together make up most of the defects.

Why can the cost ranking differ from the count ranking?

Count ranks defective parts. Cost multiplies each record's quantity by its cost per part, so a category with few parts but a high cost per part can rank first by cost and lower by count.

Can I add more defect categories?

Yes. The category list on the Pareto tab has 12 slots, and the log checks every category against it. Type a new name in an empty slot and the rankings, lines and trend pick it up.

Does the workbook replace root-cause analysis?

No. It ranks what the log records. Confirm causes on the shop floor before deciding on a fix.