Pareto Chart in Excel: Create and Read One Step by Step

Charts & pivot tables · Updated November 20, 2026

To make a reliable Pareto chart in Excel for Microsoft 365 on Windows, summarize your categories and counts, sort the counts from largest to smallest, calculate a running cumulative percentage, then chart the counts as columns and the percentage as a line on a secondary axis. For a quick chart from ungrouped observations, Excel also offers Insert > Insert Statistic Chart > Pareto; the manual method gives you more control over summarized data and labels.

What a Pareto chart shows

A Pareto chart combines two views of the same data. Its bars show categories in descending order of size, while a line shows the cumulative share represented by those categories as you move from left to right. The percentage line should rise from the contribution of the first category toward 100% at the last one.

The chart is often used to prioritize defects, customer complaints, delays, or other causes. For example, a service team might compare complaint types and see whether a few issues account for a large share of all reports. This is related to the Pareto principle, often summarized as an 80/20 rule, but the chart does not guarantee that 80% of outcomes come from 20% of causes. The actual distribution must come from your data.

A Pareto chart describes frequency or another consistently measured contribution. It does not, by itself, prove why a problem occurs, show whether a cause is controllable, or establish that fixing the largest category is the best decision. Use it to identify where to investigate, then consider cost, severity, effort, and context.

Prepare and summarize the data

Start with a clear definition of what one observation represents and a consistent period or scope. If you are analyzing support tickets, decide whether each ticket gets one primary complaint category or whether a ticket can count in several categories. Mixing those approaches can inflate totals or make percentages hard to interpret.

For a manual chart, create a summary table with one row per category and its count or other comparable measure. For example, a small factory records 40 scratched items, 25 late deliveries, 15 incorrect labels, 10 missing parts, and 10 other issues. Put the categories in A2:A6 and the counts in B2:B6, with headers in row 1. Sort the entire table by count, largest to smallest, so category names stay attached to their values.

  • Use mutually exclusive categories when each event should be counted once.
  • Give every observation a defined category, or disclose how unclassified records are handled.
  • Use the same unit across the table. Do not mix complaint counts with dollar values in one Pareto series.
  • Check that the total in the summary matches the number of records in the source data.

If your source contains one row per incident rather than a summary, create a frequency table first. You can use a PivotTable: select the source data, choose Insert > PivotTable, place the category field in Rows, and place that field in Values. Confirm that Values summarizes the field by Count, not Sum. Then sort the resulting counts descending and use that table for the manual chart.

Calculate cumulative counts and percentages

With categories in A2:A6 and their already sorted counts in B2:B6, add Cumulative Count in C1 and Cumulative % in D1. The running total is useful for checking the percentage calculations and for explaining how much the leading categories contribute.

With counts in B2:B6, enter this formula in C2 to calculate the running count through the current row, then fill it down to C6: =SUM($B$2:B2). The dollar signs keep the start of the range fixed as you copy the formula, while the final reference moves down one row at a time.

With counts in B2:B6 and the current cumulative row in C2, enter this formula in D2 and fill it down to D6: =C2/SUM($B$2:$B$6). Format D2:D6 as Percentage using Home > Number > Percent Style. For the example counts, the total is 100; the cumulative counts are 40, 65, 80, 90, and 100, so the cumulative percentages are 40%, 65%, 80%, 90%, and 100%.

Using C2 in the percentage formula makes the logic easy to audit: cumulative count divided by grand total. If you prefer a direct calculation, with the same sorted counts in B2:B6, you can enter =SUM($B$2:B2)/SUM($B$2:$B$6) in D2 and fill down. The result is the same. Do not sort the table after making these calculations unless you sort the entire table together; otherwise, the cumulative line will no longer represent descending contributions.

Build the manual Pareto chart

  1. With category names in A1:A6, counts in B1:B6, and cumulative percentages in D1:D6, select A1:B6. Hold Ctrl on Windows and also select D1:D6 so the chart includes the category labels, count series, and cumulative percentage series. If selecting nonadjacent ranges is awkward, copy the category, count, and percentage columns next to one another in a temporary chart table and select that contiguous table.
  2. Choose Insert > Insert Combo Chart > Create Custom Combo Chart. In the combo chart dialog, set the count series to Clustered Column and the cumulative percentage series to Line. Select Secondary Axis for the cumulative percentage series, then choose OK.
  3. Check that the horizontal axis displays the category names in descending-count order, not the cumulative percentages or row numbers.
  4. Add a descriptive chart title, such as Defects by Type, and label the vertical axes so readers can distinguish count from cumulative percentage. Use Chart Design > Add Chart Element to add chart titles or axis titles if they are not already present.

The secondary axis matters because counts and percentages use different scales. Without it, the percentage line may look almost flat against a count axis, or the bars may be distorted by a percentage scale. Format the secondary vertical axis so its bounds run from 0 to 1, or from 0% to 100%, depending on how Excel displays the scale. Right-click the axis and choose Format Axis to set bounds and number formatting. The final point should reach 100% when the table includes all categories.

Format the chart so it is easy to read

Keep the bars in descending order and use a restrained color for them. A contrasting line and visible markers can help readers follow the cumulative percentage, especially when category names are long. Avoid adding a separate trendline: the cumulative percentage line is the essential second series, not a statistical trendline.

For a chart with many categories, labels can become crowded. Widen the chart, shorten labels in the source table without losing meaning, or group genuinely minor categories into Other. If you use Other, define what it contains and place it last even if its count would otherwise appear higher; otherwise, the descending order will no longer be obvious. Do not combine categories merely to make the chart look cleaner if doing so hides a meaningful cause.

Consider showing both count and percentage information in the table or chart labels. A cumulative line tells the reader the combined share of all bars up to that point; it does not say that the bar at that position has that percentage by itself. In the example, the third point is 80% cumulative, while Incorrect label alone accounts for 15% of the total.

Use Excel’s built-in Pareto chart

For raw observations in a single column, Excel’s built-in chart can be faster. Select the observation data, then choose Insert > Insert Statistic Chart > Pareto. Excel creates a sorted column chart with a cumulative percentage line. The chart is most useful when each cell contains one observed category value and you want Excel to count repeated values.

Built-in chart options and behavior can vary with the selected data layout and Excel release. If your data is already summarized as category names plus counts, inspect the chart carefully: a chart based on raw records and a chart based on a frequency table are not interchangeable in every setup. If Excel treats the columns as bins or produces unexpected category labels, use the manual combo-chart method above. It makes the bar values, order, and cumulative calculation explicit.

To adjust a built-in chart, select it and use the Chart Design and Format tabs. Depending on the chart and version, you may be able to change bin or category settings through Format Axis. If you do not see the Pareto option, check that you are using a desktop version of Excel that includes statistical chart types; Excel for the web may not expose the same chart-creation controls. On Mac, the ribbon layout can differ, but look for Pareto under Insert chart types and use a combo chart for the manual alternative.

Interpret the results and avoid common mistakes

Read the bars from left to right to find the largest contributors, then use the cumulative line to see how quickly the total builds. In the example, the first three categories account for 80 of 100 issues. That is a useful prioritization signal, but not proof that those categories are the only problems worth addressing. A less frequent issue may have a higher safety impact or cost more per occurrence.

  • Unsorted bars: The classic Pareto presentation orders categories from largest to smallest. Sort the full summary table, not only its count column.
  • Incorrect cumulative total: The last cumulative percentage should be 100% when every category is included. If it is not, check the denominator, missing rows, and whether the percentages are stored as numbers.
  • Wrong summary operation: A PivotTable may sum a numeric category code instead of counting incidents. Set the Values field to Count and validate the result against the source record total.
  • Double-counted events: Multiple labels per record can make the total represent category mentions rather than unique events. State which measure the chart uses.
  • Misleading Other category: Grouping can conceal a recurring issue. Review the underlying items and disclose the grouping rule.

For a meaningful comparison over time, keep category definitions and observation periods consistent. A month with twice as many reported cases may simply have twice the volume of activity. If exposure differs, compare rates as well as counts, and label the measure clearly. A Pareto chart of counts answers which categories contribute most to the observed total; it does not automatically adjust for volume, severity, or risk.

Frequently Asked Questions

Can Excel make a Pareto chart automatically?
Yes. In desktop Excel for Microsoft 365, select raw observations and use Insert > Insert Statistic Chart > Pareto. If you need a chart from an existing category-and-count summary, a combo chart with a calculated cumulative percentage is often easier to verify.

Why does my cumulative line not reach 100%?
Check that the denominator includes every count and that the final cumulative count includes the last category. Also make sure the chart includes the final percentage row. If you intentionally omit categories, the line will end below 100% of the full dataset unless you recalculate the denominator for the displayed subset.

Can I make a Pareto chart with percentages instead of counts?
Yes, if every category uses the same denominator and the values are comparable. Sort the category contributions from largest to smallest and calculate their running total. If the percentages are already shares of the same whole, the final cumulative value should be 100%, subject to rounding.

How do I keep the chart updated when counts change?
Update the summary values and confirm the table remains sorted descending. A normal chart reflects changed cell values, but it will not necessarily reorder categories automatically when their ranking changes. For a recurring report, sort the summary again before presenting it, or use a carefully maintained dynamic workflow.

Is the 80/20 rule required for a Pareto chart?
No. It is a popular rule of thumb, not a required result. A valid Pareto chart may show an even spread, a sharp concentration in one category, or any other distribution present in the data. Report the actual cumulative percentages rather than assuming they follow an 80/20 split.

Related

Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.

More in Charts & pivot tables

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.