How is the Pareto chart built without macros?

Each category's total is a SUMIFS over the tally rows inside the filters. Categories are ranked with COUNTIF so ties keep the Setup list order, then sorted with INDEX and MATCH, and each row shows its share and the cumulative share. The chart draws the values as bars and the cumulative percentage as a line on a secondary axis. It works in Excel 2016 and later, Microsoft 365, LibreOffice and Google Sheets.

Why show a Pareto by cost as well as by count?

Because the most frequent defect is often cheap. In the example, flash leads with 567 occurrences at 0.35 each (about 198 in cost), while 64 missing inserts at 7.80 each cost about 499. Ranked by count, missing inserts are seventh; ranked by cost, they are first. Fixing the cost leader usually pays back faster.

No, it is a rule of thumb. Real tallies are often flatter: in the example it takes 5 of 10 categories to reach 80 percent of the defects. The cut-off is a Setup value, so you can use 70 or 90 percent, and the vital-few tile shows how many categories it takes in your data.

Where do the cost per occurrence figures come from?

From you. Use the scrap value of the part, the rework labour, or the relabelling cost for each category; the example figures are illustrations, not industry data. Because they are standard estimates per category, the cost Pareto ranks categories well but is not an accounting total.

How is defects per unit calculated?

Defects logged in the period divided by units inspected in the same period from the Daily Output table. With 18 defects in 400 units, DPU is 0.045, or 4.5 defects per hundred units, the same as the Defects per Unit calculator. With an area filter, DPU becomes that area's defects per unit made, because units are plant totals.

Quality and Inspection

Pareto Analysis Excel Template

Tally defects or problems by category and see two Pareto charts, by count and by estimated cost, the vital few and defects per unit update as you type, with no macros.

Overview

This Pareto analysis template is a working Excel workbook for quality engineers, supervisors and improvement teams who need to know which problem to fix first. It ships with four weeks of example tallies for a fictional moulding and assembly plant, so both Pareto charts, the tiles and the trend are populated when you open it; replace the example rows with your own and everything recalculates.

Each tally row records a category, the area where it was found and how many were found. A cost per occurrence on Setup turns counts into an estimated cost, and the Dashboard ranks the categories twice, by count and by cost, with each category's share and the cumulative share. The vital-few tile counts how many categories it takes to reach your cut-off, and a Daily Output table of units inspected gives defects per unit, the same formula as the Defects per Unit calculator.

In the example, flash is the most frequent defect, but missing inserts, seventh by count, are the most expensive because each one scraps a finished assembly. That is the reason to draw both charts: the count Pareto shows where the noise is, the cost Pareto shows where the money is. The Method sheet explains the ranking, the cut-off and what a Pareto cannot tell you.

What this template includes

Defect Log table: date, area, category and count, with the cost per occurrence looked up from Setup and the cost calculated per row
Daily Output table of units inspected, so the Dashboard shows defects per unit (DPU) next to the Pareto
Two Pareto blocks and charts from the same tally, by count and by estimated cost, sorted with formulas that work in Excel 2016 and later, LibreOffice and Google Sheets
Vital-few tile: how many categories reach your cut-off (80% by default) of the defects
Dashboard with area and date filters, tiles for defects, cost, vital few and the count and cost leaders, plus DPU by week and share by area charts
Check columns that name problems in words: category not on the list, duplicate row, no matching Daily Output row
Method sheet with every formula, and a Verify sheet that re-checks the formulas against an independent reference (Self-check: PASS)
CSV with live formulas for the defect log; the dashboard, charts, dropdowns and self-check are in the .xlsx

Suggested use case

Use it to decide which defect or problem to attack first, to show a team where the losses really are before a kaizen event, or to track whether the top category shrinks after a fix.

How to use it

On Setup, enter your plant name, currency, Pareto cut-off and DPU target, then replace the example areas and categories, with a cost per occurrence for each category.
On Defect Log, add one row per category per area per day (or shift) with the number found.
On Daily Output, add the units inspected each day.
Fix anything the Check columns flag, then open the Dashboard and compare the Pareto by count with the Pareto by cost.
Use the area and date filters to see one area or the weeks before and after a fix.

Frequently Asked Questions

How is the Pareto chart built without macros?: Each category's total is a SUMIFS over the tally rows inside the filters. Categories are ranked with COUNTIF so ties keep the Setup list order, then sorted with INDEX and MATCH, and each row shows its share and the cumulative share. The chart draws the values as bars and the cumulative percentage as a line on a secondary axis. It works in Excel 2016 and later, Microsoft 365, LibreOffice and Google Sheets.
Why show a Pareto by cost as well as by count?: Because the most frequent defect is often cheap. In the example, flash leads with 567 occurrences at 0.35 each (about 198 in cost), while 64 missing inserts at 7.80 each cost about 499. Ranked by count, missing inserts are seventh; ranked by cost, they are first. Fixing the cost leader usually pays back faster.
Is 80/20 a rule?: No, it is a rule of thumb. Real tallies are often flatter: in the example it takes 5 of 10 categories to reach 80 percent of the defects. The cut-off is a Setup value, so you can use 70 or 90 percent, and the vital-few tile shows how many categories it takes in your data.
Where do the cost per occurrence figures come from?: From you. Use the scrap value of the part, the rework labour, or the relabelling cost for each category; the example figures are illustrations, not industry data. Because they are standard estimates per category, the cost Pareto ranks categories well but is not an accounting total.
How is defects per unit calculated?: Defects logged in the period divided by units inspected in the same period from the Daily Output table. With 18 defects in 400 units, DPU is 0.045, or 4.5 defects per hundred units, the same as the Defects per Unit calculator. With an area filter, DPU becomes that area's defects per unit made, because units are plant totals.