Pareto chart: paste your defects, get the ranking

Categories and counts in, ranked bars and the cumulative line out — with the vital few named, an "Other" bucket that always sorts last, and an honest warning when the distribution is flat and there is no dominant cause to attack.

How you make a Pareto chart in Excel

Excel 2016 and later has one built in. Select your categories and their counts, then pick Pareto from the statistic-chart icon in Insert → Charts — it sorts the bars for you and draws the cumulative percentage line on a secondary axis without being asked.

What it will not tell you is whether the ranking means anything. On forty defects the leading category and the second can swap places next week on noise alone, and the chart looks equally confident either way. Excel ranks whatever you hand it: no tail grouped into an "other", and no view on whether you are looking at a genuine 80/20 split or a flat spread with a leader by two counts.

Grouping the tail, and when the 80/20 split is not there at all

Nothing you paste leaves this calculation. It is not stored, not logged and not sent anywhere else — the numbers are computed and returned, and that is all. Paste straight out of a spreadsheet — tab-separated columns are understood as they land.

What a Pareto chart is for

Deciding what to work on. Bars ranked from largest to smallest, with a line showing the running cumulative percentage, so the question "which few causes account for most of the problem?" is answered by reading across rather than by arguing in a meeting.

The cumulative line is the point of the chart, not decoration. Without it you have a sorted bar chart, and a sorted bar chart does not tell you where to stop.

80/20 is a rule of thumb, not a law

Vilfredo Pareto observed that most of the land in Italy belonged to a small proportion of the population, and Joseph Juran generalised it into the "vital few and trivial many". It is a frequent pattern. It is not a property of nature, and your data is under no obligation to produce it.

A flat Pareto is a real and useful finding. If it takes nine of your twelve categories to reach eighty per cent, there is no dominant cause to attack, and choosing the tallest bar anyway means picking noise and spending a project on it. The correct response is usually that the categories are wrong — too fine, or split across a distinction that does not matter — or that the problem is genuinely systemic and needs a process change rather than a defect hunt.

The mistake that sends you to fix the wrong thing

Counting frequency when you should be counting cost.

The most frequent defect is very often not the most expensive one. Forty-seven scratches that polish out in seconds are a smaller problem than one contamination event that scraps a batch and triggers a customer notification — but on a Pareto by count, scratches tower over it and contamination is the last bar on the right.

Draw it twice. Once by count, once by cost — scrap value, rework minutes, warranty exposure, whatever the real unit is. When the two charts disagree, the cost one is the one that should set the agenda, and the disagreement itself is worth showing to whoever owns the budget.

Other things worth getting right

Categories must be mutually exclusive. If a part can be counted under two causes, the chart double-counts and the percentages mean nothing.
"Other" always goes last, however large it grows. An "Other" bar in the middle of the ranking is a broken Pareto. If it grows past the top few real categories, the roll-up threshold is hiding the problem rather than tidying it.
State the period. A Pareto is a snapshot with no time axis at all, so it cannot distinguish a category that is steadily worsening from one that had a single bad week. It ranks; it does not trend.

That last limitation is worth taking seriously. Once the Pareto has told you which category to attack, a control chart of that one category over time is what tells you whether it is getting worse, getting better, or has been stable all along — and whether the fix you applied actually did anything.

What the Excel version will not do

A ranking is only as current as the sheet it lives in: The point of a Pareto is to aim this week’s effort. The chart refreshes when somebody updates the workbook behind it, which is generally the morning of the meeting it was made for.
Two Paretos will not line up: Before-and-after is how you find out whether the fix worked, and Excel will not hold the category order steady between the two charts. You end up comparing bars by reading their labels.
A Pareto has no time axis: The tallest bar may be one catastrophic Tuesday or a steady trickle all quarter, and the two need completely different responses. Ranking totals discards the one piece of context that decides what you do next.

Questions people ask about this

How do I make a Pareto chart?

Count each cause over a stated period, rank them largest to smallest, and plot the counts as bars with the running cumulative percentage as a line on a second axis scaled nought to a hundred. Paste your categories above and this does all of it.

What is the 80/20 rule in a Pareto chart?

The observation that a small number of causes often account for most of the problem. It is a frequent pattern, not a law, and your data is under no obligation to produce it. If it takes nine of twelve categories to reach eighty per cent, that flat shape is itself the finding.

Should I count defects or cost?

Both, on two charts. The most frequent defect is very often not the most expensive one — forty scratches that polish out are a smaller problem than one contamination event that scraps a batch. Where the two charts disagree, the cost one should set the agenda, and the disagreement is worth showing to whoever owns the budget.

Where does the "Other" category go?

Last, always, however large it grows. An "Other" bar sitting in the middle of the ranking is a broken Pareto. If it grows past your top real categories, the roll-up threshold is hiding the problem rather than tidying the chart.

Can a Pareto chart show a trend?

No. It has no time axis at all, so it cannot distinguish a category that is steadily worsening from one that had a single bad week. Once it has told you which category to attack, chart that category over time to find out whether it is getting worse and whether your fix did anything.

The vital few change. Your Pareto should notice.

A workspace re-ranks the same categories as this week’s defects are recorded, keeps the order steady so before-and-after actually compare, and puts the counts behind it on a c or u chart — which is what tells you whether a category genuinely got worse or simply had a bad week.

Start now See it on live data

More quality tools tools

Box and whisker plot generator — One column per group, boxes side by side. Quartiles, the 1.5×IQR fences, whiskers that stop at real readings and outliers drawn individually — with the quartile method stated, because that is why your plot and Excel's disagree.
Histogram and normality test — A histogram with the fitted normal curve, both standard bin rules with a reasoned recommendation, and an Anderson-Darling test that refuses to tell you your data is normal — because no test can.

Everything else

Cp / Cpk calculator — Paste measurements — or type a mean and a sigma — with your tolerance, and get Cp, Cpk, Pp, Ppk, the sigma level and the expected parts per million out of spec.
Control limit calculator — Give it subgroups or individual readings and it returns the control limits for the chart, the range or sigma chart beneath it, and every constant it used to get there.
Control chart generator — Paste a column of numbers, or rows of subgroups, and get a real control chart: limits from the data, Nelson rules 1–4 evaluated, out-of-control points marked.
Nelson & Western Electric rules checker — Every run rule evaluated on your data, each violation named in plain English with what it usually indicates — a shift, a trend, tool wear, two machines mixed.
Cpk confidence interval & sample size — A Cpk of 1.33 from 30 pieces has a 95% interval of roughly 0.97 to 1.69. See the uncertainty in your own number, and how many parts would settle it.
Cpk ↔ PPM ↔ sigma level converter — What PPM is a Cpk of 1.33? What Cpk does 3.4 PPM need? Both conventions shown side by side, because the 1.5 sigma shift is why two sources disagree by a factor of ten.
Control chart constants — The whole table, n = 2 to 25, with the formula each constant belongs to. The same values the charts on this site are computed from.
X̄-R chart calculator — Subgroups in, X̄ and R charts out — limits from A₂, D₃ and D₄, run rules evaluated, and the range chart shown first because it decides whether the averages chart can be trusted.
X̄-s chart calculator — For subgroups big enough that the range wastes them. Limits from A₃, B₃ and B₄, sigma recovered with the c₄ correction, and the s chart read first because it decides whether the averages chart can be trusted.
I-MR chart calculator — For processes that give you one number at a time — a batch, an oven, a destructive test. Limits from the mean moving range, run rules evaluated, and an honest note about what an individuals chart cannot see.
Levey-Jennings chart with Westgard rules — Paste your QC results with the assigned mean and SD from the package insert, and get the chart with every sigma band drawn and all six Westgard rules evaluated — each one named, and labelled random or systematic error.
Bland-Altman plot — Paired readings from two methods, plotted as difference against average — with the bias, the limits of agreement, their confidence intervals, and a test for whether the disagreement depends on the magnitude.
Gage R&R calculator (ANOVA) — Paste a crossed study — part, operator, reading — and get the full ANOVA: repeatability and reproducibility separated, the part-by-operator interaction tested rather than assumed away, %GRR, %Tolerance and ndc against AIAG's bands.
p, np, u and c chart calculator — Counted data rather than measured: proportion defective, number defective, defects per unit. Limits that step with the sample size instead of pretending it never changed, and an honest warning when the counts are too low for three-sigma limits to mean anything.
Grubbs outlier test — The G statistic against its critical value, the p-value, and the point implicated — plus a straight answer about what to do next, which is almost never to delete the reading.
Scatter diagram and correlation — Paired readings plotted, with Pearson r, Spearman rho, r², the least-squares line and its p-value — and the two reported together, because when they disagree the disagreement is the finding.
Standard deviation calculator — The usual summary statistics, and the thing a spreadsheet will not tell you: the within-subgroup sigma a control chart uses, beside the overall sigma a textbook means. The gap between them is why Cpk and Ppk disagree.