Pareto analysis in a spreadsheet: find the vital few defects

A Pareto analysis ranks problems from largest to smallest and shows how much of the total the largest few account for. In the defect tally used here, four of nine categories make up 86.0% of 342 defects, and the 80% line falls in the fourth row. Ranked by cost instead, a different category leads, and the four categories that reach 80% of the cost are not the same four. The ranking method is simple. The choice of measure decides which problems get attention first.

This article explains the idea behind the Pareto chart, builds one from a fictional defect tally, compares ranking by count with ranking by cost, and sets out the spreadsheet formulas that keep the ranking correct when the data changes. The tally and unit costs are illustrative. For the short definition, see what is a Pareto chart.

Is the 80/20 split a fixed rule in Pareto analysis?

Vilfredo Pareto, an Italian economist, noted in the late 19th century that a small share of the population held most of the land. Joseph Juran later applied the idea to quality management and popularized the phrase "vital few and trivial many." The 80/20 split is a rule of thumb. A real tally can show 60/40 or 90/10.

The useful question is whether a few categories hold a clearly larger share than their number would suggest. If the bars are nearly equal, a Pareto chart will not show where to start, and the categories may need to be defined more carefully.

How do you build a defect tally for a Pareto analysis?

Log one row per defect with its category, the line it came from and its unit cost. The log in this example holds 342 defects in nine categories. Sorted from largest to smallest by count, with the cumulative share added:

The first three categories reach 79.5%, just short of 80%. The fourth takes the total to 86.0%, so the 80% line falls in row four. The other five categories are the trivial many. Together they make up the remaining 14.0% of defects.

Two rows tie at six defects. A ranking has to put tied rows in some order, and the spreadsheet formulas below handle that without a manual sort.

How does ranking by cost change a Pareto analysis?

Counts treat every defect the same. Cost does not. A cracked housing costs $18.00 to scrap in this example, while a solder bridge costs $0.80 to rework. Ranking the same log by cost gives a different list:

The total cost is $528.00. Cracked housing is 2.6% of the defects but 30.7% of the cost. By cost, the four categories that reach 80% are cracked housing, solder bridge, missing component and cold joint. By count, they are solder bridge, cold joint, missing component and label misprint. Label misprint drops out and cracked housing comes in.

Neither list is wrong. Count points to the process that makes the most defects. Cost points to where the money goes. If both matter, show the two rankings side by side and decide which one the goal calls for.

How do you set up a Pareto analysis in two sheets?

Use two sheets. Log holds one row per defect: category in column A, line in B and unit cost in C. Pareto holds the tally and the sorted view. Category names are typed once in A2:A10. In the tally:

• Count, B2: =COUNTIF(Log!$A$2:$A$500,A2)
• Cost, C2: =SUMIF(Log!$A$2:$A$500,A2,Log!$C$2:$C$500)

Whole-number counts tie often, and LARGE on tied values returns the same value twice. A small tie-break key solves this. In E2, =B2+ROW()/1E6 adds a value below 0.001 for any row under 1,000. It cannot change a real difference, but it gives each row a unique value. Fill it down, and do the same in F2 for cost with =C2+ROW()/1E6.

The sorted view runs from column H. Rank numbers 1 to 9 go in H2:H10, and then:

• Key, I2: =LARGE($E$2:$E$10,H2)
• Category, J2: =INDEX($A$2:$A$10,MATCH(I2,$E$2:$E$10,0))
• Count, K2: =INDEX($B$2:$B$10,MATCH(I2,$E$2:$E$10,0))
• Cumulative count, L2: =SUM($K$2:K2)
• Cumulative %, M2: =L2/SUM($B$2:$B$10)

Fill the row down to row 10. The number of categories needed to reach 80% is =COUNTIF(M2:M10,"<0.8")+1 in M12, which returns 4 for this tally. For cost, use F for the key and C for the cost in the same pattern.

Hand-sorting a copy of the tally goes stale as soon as a log row changes. The formulas re-rank on every change. Check that the tally total equals the number of log rows, so no defect has been left out of every category.

How do you draw Pareto bars in cells when a chart is not an option?

A Pareto chart needs bars sorted in order, and a cumulative line on a secondary axis. Excel 2016 and later include a Pareto chart type, and a combo chart of columns and a line also works in Excel and Google Sheets. When a chart is not an option, REPT draws bars inside the cell. In N2:

=REPT("█",ROUND(K2/5,0))

This draws one block for every five defects. The solder bridge row shows 29 blocks (143 / 5 = 28.6, rounded to 29), and a count of 6 shows one block. The bars are a visual aid, so use a fixed font that draws the block character at the same width in every row.

How do you split each defect category by line or shift?

A category that is large overall can come from one source. Before acting, split each category by line, shift or product. The COUNTIFS formula below counts Line 2 defects for a category named in column A:

=COUNTIFS(Log!$A$2:$A$500,$A2,Log!$B$2:$B$500,"Line 2")

For the three largest categories in this example:

Solder bridges are spread across both lines. Cold joints and missing components are concentrated on Line 2. That does not prove the cause, but it shows where to look first.

What mistakes make a Pareto analysis misleading?

• An "Other" bar near the top. If an unnamed bucket is one of the largest bars, the categories are too coarse to act on. Split it until no unnamed bucket is large, and keep Other last.
• Mixing periods. Counts from one week added to counts from a month give a ranking that depends on the period chosen. Use one period for every row and state it on the sheet.
• Counting symptoms, not causes. A solder bridge is a symptom. The cause may be the stencil, the paste or the reflow profile. A Pareto ranks what was recorded, so if the categories describe symptoms, the chart ranks symptoms. Record the cause in its own column.
• Acting on counts when cost matters. The cracked housing example shows the gap: 2.6% of defects, 30.7% of cost.
• Sorting by hand. A static sort is correct only until the next data change.
• Leaving defects out of the total. The cumulative percentages divide by the tally total. A defect that belongs to no category shifts every percentage.

A Pareto of stop time uses the same method. The unplanned stop minutes in the OEE calculator can be split by reason and ranked this way. The six big losses that the OEE article describes give a starting list of categories, and the short formula is in how to calculate OEE.

The defect Pareto analysis template is the Sheet Reserve layout for this method. For the short definition of the chart, see what is a Pareto chart.