How to make a Pareto chart in Excel

A Pareto chart is a bar chart of categories sorted from most to least, with a cumulative percentage line running across the top. It exists to answer one question: which few of these categories account for most of the problem?

Excel will draw one in about ten seconds, and there is a second, longer route that you will end up taking anyway the first time you need the chart to do something the built-in one refuses to do.

The built-in Pareto chart

Excel 2016 and later, and Microsoft 365, ship a Pareto chart type. It lives with Histogram and Box & Whisker under the statistic-chart icon in the Insert tab's Charts group. The exact icon position has moved around between versions; the grouping has not.

1. Put your categories in one column and their counts in the next. One row per category, no blanks.
2. Select both columns.
3. Insert → the statistic-chart icon → Pareto.

Excel sorts the bars descending, computes the cumulative percentage, draws the line and adds the right-hand axis. For a chart that is going into a meeting this afternoon and nowhere else, stop here.

What you are giving up:

• It sorts, and it insists on sorting. Any order you imposed on the sheet is discarded. That matters mostly for an "Other" bucket, which the built-in chart will place wherever its count falls rather than last, where it belongs.
• Treat it as a finished picture, not a chart you can build on. The usual moves — adding an 80% reference line as a further series, retitling the secondary axis, plotting a second period alongside for comparison — either are not offered or fight you, because the bars and the cumulative line are not two ordinary series you can address separately. If you need any of them, build it manually as below.
• It is a Histogram variant. Hand it a single column of raw values instead of category/count pairs and it bins them numerically, which is not what you wanted.

Building one manually

Longer, and it gives you an ordinary combo chart that behaves like every other chart in the workbook.

1. Get the categories right. Mutually exclusive, one row each, counted over a stated period. This is the part that determines whether the chart is worth drawing.

2. Sort descending, before anything else — Data → Sort, largest to smallest on the count column. Everything downstream assumes this order.

3. Add a cumulative percentage column. In C2, with counts in B2:B21:

   =SUM($B$2:B2)/SUM($B$2:$B$21)
   

   The absolute start and relative end are the whole trick. Fill down, format as a percentage.

4. Optional: an 80% column — the constant 0.8 filled down the same range, to be plotted as a flat reference line.

5. Select all three (or four) columns → Insert → Combo chart. Counts as a clustered column on the primary axis; cumulative percentage as a line with markers on the secondary axis; the 80% column as a line, secondary axis, no markers.

6. Set the secondary axis to 0 and 1 by hand — see below.

7. Drop the gap width on the column series to something like 20–30% so the bars read as a Pareto and not as a generic bar chart.

The secondary axis is what people get wrong

Left alone, Excel autoscales the secondary axis to the data. On a Pareto that means it will often start somewhere around 40% and finish above 100%, because the cumulative series only ever occupies the top part of the range.

Two things break. The line's apparent steepness becomes an artefact of the scale rather than a property of the data, and two charts drawn a month apart are not comparable, because Excel chose a different scale for each.

Fix the secondary axis minimum at 0 and its maximum at 1 (100%), every time. Then the height of the line means the same thing on every Pareto anyone in the building draws.

Two smaller conventions worth keeping: the primary count axis must also start at zero, or the tallest bar is exaggerated; and the cumulative line starts at the first category's own cumulative percentage — above the first bar — not at zero on the left edge.

Sorting, and where "Other" goes

Descending order is not decoration. The cumulative line is only interpretable if each step is smaller than the one before it, and the "vital few" reading depends entirely on the sort.

The one permitted exception is the residual bucket. "Other" always goes last, however large it is, because it is not a category — it is the absence of one. The manual build lets you do this; the built-in chart does not.

If "Other" is one of the tallest bars, you do not have a Pareto chart yet. You have a categorisation problem, and the fix is to go back to the source records and split it. A Pareto whose largest actionable finding is "miscellaneous" has told you nothing.

Counting the wrong thing

This is the error that costs real money, and it is not a charting error.

Most Pareto charts are drawn by frequency, because frequency is what the defect log gives you for free. Frequency is the right weight only when every occurrence costs about the same, and it usually does not:

By count, scratches are 57% of the problem and the improvement team spends six months on surface handling. By cost, scratches are 6% of the problem and the two rarest defects are 88% of it. Same log, same period, opposite programme.

Choose the weight deliberately — cost, downtime minutes, warranty claims, rework hours, customer complaints — and write on the chart which one it is. A Pareto with an unlabelled y-axis is an invitation to assume it is counts.

80/20 is a rule of thumb

Pareto's original observation was about the distribution of land ownership, and the 80/20 figure is a description that often approximately holds, not a law your defect data is obliged to satisfy. Nothing is wrong if your top two categories come to 62%.

A flat Pareto is a real finding. Six categories at roughly equal height say something specific and useful: there is no dominant cause, and a project aimed at any one of them buys you about a sixth of the problem. That is worth knowing before the project is chartered rather than after.

Two things to do with a flat one:

• Stratify. Split by machine, shift, supplier, cavity, part number. A Pareto that is flat overall is frequently steep inside one stratum, and the stratum is the finding.
• Check the categories are not over-split. Three rows describing the same failure under three names will flatten any Pareto. Merging them is not massaging the data; it is fixing the taxonomy.

If it survives both, accept that the problem is systemic and stop looking for the single big fix.

What a Pareto chart cannot tell you

It has no time axis. It is a snapshot of a period, so it cannot distinguish a category that is steadily getting worse from one that spiked once in week two and has not recurred since. Those are entirely different situations and they produce an identical bar.

That is what a chart over time is for — a c-chart or u-chart on the defect count, depending on whether the opportunity size is constant. Use the pair: the Pareto chooses what to work on, the time-ordered chart tells you whether the work did anything. You can paste a column of counts into the control chart generator to see the second half of that picture, and the guide on common and special cause variation covers how to read it.

Where the spreadsheet stops

A Pareto is a periodic, deliberate piece of analysis, which is the kind of work Excel is genuinely good at. The cost is rebuilding it by hand every month — the manual build has seven steps, and step six is the one that gets skipped when somebody is in a hurry.

The harder limitation arrives after the Pareto, once the chosen category becomes something you need to watch continuously. That argument is made in SPC charts without Excel.