To make a sample Pareto chart in Excel, first sort category counts from largest to smallest, calculate each category’s cumulative percentage, then plot the counts as columns and cumulative percentage as a line on a secondary axis. For the example below, five defect types total 100 cases, so the cumulative line rises from 32% to 100%.
What a Pareto chart shows
A Pareto chart combines descending columns with a cumulative-percentage line. It helps you see which categories account for the largest share of a total, such as customer complaints, product defects, late deliveries, or support requests. The first bar is the largest category, the next bar is the second largest, and so on. The line shows how quickly the categories add up to the whole.
The chart is often used with the Pareto principle, sometimes summarized as the 80/20 rule: a relatively small number of causes may account for a large share of an outcome. That is a useful prompt for investigation, not a guarantee that the data will follow an 80/20 split. Your chart may show 70%, 85%, or another share across the first few categories. It identifies concentration in the data; it does not prove why the pattern occurred.
Sample defect data and the result
Imagine a quality team counted 100 defects and grouped them into five categories. The counts are already ordered from highest to lowest. The cumulative count is the running total, and cumulative percentage is that running total divided by all 100 defects.
| Defect type | Count | Cumulative count | Cumulative percentage |
|---|---|---|---|
| Scratch | 32 | 32 | 32% |
| Missing part | 24 | 56 | 56% |
| Wrong label | 18 | 74 | 74% |
| Loose screw | 14 | 88 | 88% |
| Other | 12 | 100 | 100% |
In this example, scratches and missing parts together represent 56% of the recorded defects. The first four categories represent 88%. That makes the chart useful for prioritizing a closer look, while leaving room to check costs, severity, and whether the counts are representative.
Prepare the worksheet
In Excel for Microsoft 365 on Windows, enter the category names in column A, the counts in column B, cumulative counts in column C, and cumulative percentages in column D. Put headers in row 1 and the five categories in rows 2 through 6. Sort the records by count, largest to smallest, before creating the chart; the chart order matters because the line is a running total.
With names in A2:A6, counts in B2:B6, and the cumulative-count column beginning in C2, enter =SUM($B$2:B2) in C2 and fill it down through C6. The values should be 32, 56, 74, 88, and 100. The dollar signs keep the start of the sum fixed as the formula is copied.
With counts in B2:B6, cumulative counts in C2:C6, and the cumulative-percentage column beginning in D2, enter =C2/SUM($B$2:$B$6) in D2 and fill it down through D6. Format D2:D6 as Percentage. The results should be 32%, 56%, 74%, 88%, and 100%. These formulas assume the five sample rows are the complete data set; expand the ranges if you add categories.
Build the chart from summarized counts
For this table, which already contains one row per category and a count, make a combo chart. Select the category names in A1:A6, then hold Ctrl on Windows and select the counts in B1:B6 and percentages in D1:D6. On the ribbon, choose Insert > Insert Combo Chart > Clustered Column – Line on Secondary Axis. In the chart setup, make sure Count is the column series and Cumulative percentage is the line series, and that the percentage series uses the secondary axis. Depending on the Excel build, the combo chart command may appear in the Charts group or under Recommended Charts.
If selecting separate ranges is inconvenient, arrange the chart data in adjacent columns—for example, category, count, and cumulative percentage—and select that block. Then choose the combo chart and confirm the series assignments in the dialog. Excel may initially treat the percentage series as columns or put it on the primary axis, so check the settings before accepting.
- Use a clear title, such as Defects by Type and Cumulative Share.
- Label the left vertical axis Count and the right vertical axis Cumulative percentage.
- Format the right axis as a percentage, with bounds from 0% to 100% if Excel has not set them appropriately.
- Keep category labels readable; rotate them or widen the chart if long names overlap.
On Mac, the chart concepts and series settings are the same, but ribbon placement and dialog labels can differ slightly by Excel version. Excel for the web also provides chart editing, although some detailed formatting controls may differ from the Windows desktop application. If the combo-chart option is not visible, create a column chart first, then use the chart’s series settings to change the cumulative-percentage series to a line and assign it to the secondary axis.
Use Excel’s built-in Pareto chart for raw observations
Excel also has a dedicated Pareto chart type, which is convenient when the source is a column of individual observations rather than a summarized category-and-count table. For example, if each row in a column records one defect type from one inspected item, select that column, including its heading, then choose Insert > Insert Statistic Chart > Pareto. Excel counts the occurrences, orders the categories, and adds the cumulative-percentage line.
This is an important distinction: do not select the two-column summary table of labels and counts as though it were a list of individual observations. The dedicated chart expects the raw values to tally. If your data is already summarized, the combo-chart method above gives you direct control over the count and cumulative-percentage series. Built-in statistical chart availability and controls can vary by Excel edition; if Pareto is not offered in your Insert menu, the manual combo chart works with ordinary chart features.
How to read and use the chart
Read the bars from left to right to compare category counts, then follow the line to see the running share. On the sample chart, the second point is 56%, meaning the two largest categories account for 56 of 100 defects. The line does not show a separate percentage for each bar; each point includes that category and every category before it.
Use the pattern to decide what to investigate, not to make an automatic decision. A high count can justify checking a process, but a less frequent defect may have greater safety, financial, or customer impact. Consider adding cost or severity analysis, checking the time period represented, and validating how categories were assigned. A Pareto chart is most useful when the categories are consistent and the underlying counts are trustworthy.
Common problems and fixes
- The columns are not descending. Sort the source table by count, largest to smallest, and make sure each category stays paired with its count. Sorting only one column scrambles the data.
- The cumulative line is flat or incorrect. Confirm that the formula uses a running sum and that the denominator includes every category. Check that percentage cells are numeric values, not text.
- The line appears near the bottom. Put the percentage series on the secondary axis. Counts and percentages use different scales, so one shared axis makes the line hard to read.
- The right axis shows decimals. Format that axis as Percentage. A stored value of 0.56 is correctly displayed as 56% when formatted as a percentage.
- The final point is not 100%. Check for omitted rows, filters, blank counts, or a denominator range that does not cover all categories. If categories are intentionally excluded, explain that scope in the chart title or note.
When a Pareto chart is not the best choice
A Pareto chart is designed for categorical counts or totals that can be meaningfully ranked. It is less suitable for a time series, where a line chart better shows change over time, or for data where category order has a meaningful sequence that should not be rearranged. It can also become difficult to read when there are many categories with long names. In that case, combine genuinely minor categories into an Other group only when doing so does not hide an important issue, or show the largest categories separately and document what was grouped.
For comparing category shares across multiple periods, consider separate small charts or a carefully designed 100% stacked chart. A single Pareto chart usually answers one question for one defined data set. Mixing different periods or populations into one ranked total can obscure changes and lead to misleading conclusions.
Frequently Asked Questions
Can Excel make a Pareto chart automatically?
Yes. In supported desktop editions, select a column of raw categorical observations and choose Insert > Insert Statistic Chart > Pareto. For a table that already summarizes categories and counts, create a combo chart with counts as columns and cumulative percentage as a line.
Does a Pareto chart always follow the 80/20 rule?
No. The 80/20 phrase is a rule of thumb, not a property Excel enforces. The cumulative line shows the actual distribution in your selected data, which may or may not reach 80% near the first fifth of categories.
Do I need to sort the data before making the chart?
For a manually built Pareto chart from summarized data, yes: sort the categories by count from largest to smallest before calculating the running percentage. Excel’s dedicated Pareto chart sorts the raw-data categories as part of its charting behavior.
Why does my cumulative percentage exceed 100%?
Usually the running total or denominator range is wrong, or some categories were counted more than once. In the sample setup, the last cumulative count should equal the sum of B2:B6, and the last cumulative percentage should be exactly 100% apart from display rounding.
Can I include negative values?
A standard Pareto chart is intended for nonnegative counts or totals. Negative values do not represent category frequencies in the usual sense and can make cumulative percentages confusing. Investigate negative adjustments separately or define a measure that is meaningful for ranking.
Related
- Subscript in Excel: Shortcut and Formatting Steps
- Quick Forecast in Excel: Steps, Limits, and Practical Tips
- Excel Redo Shortcut: The Quick Key and When It Works
Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.
More in Charts & pivot tables
- How to Remove a Trendline from an Excel ChartTo remove a trendline from an Excel chart in Microsoft 365 for Windows, click the trendline to select…
- Excel Chart Legend Icons: How to Make Symbols BiggerYes—Excel can make chart legend icons look bigger, but it does not provide a separate control for setting…
- Pivot Chart Grand Total: What Excel Shows and How to Add ItA PivotChart does not plot a PivotTable’s grand total as a chart category or data point. Excel keeps…
- Excel Line Chart: Change Color Partway Through a LineTo change a line chart’s color partway through in Excel for Microsoft 365 on Windows, split the plotted…
- Excel Sparklines from PivotTables: How to Add ThemYes. In Excel for Microsoft 365 on Windows, you can create sparklines from numeric cells in a PivotTable:…
- Half-Circle Charts in Excel: Create a Semi-DoughnutExcel does not have a dedicated half-circle chart button, but you can make one with a doughnut chart:…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.