To make a Pareto chart in Microsoft Excel for Microsoft 365 on Windows, select a category-and-count table, then choose Insert > Insert Statistic Chart > Pareto. The chart orders categories from largest to smallest and adds a cumulative-percentage line; the 80/20 rule is a useful pattern to investigate, not a result Excel guarantees.
What a Pareto chart shows
A Pareto chart combines two views of the same data. Its columns show the size of each category, arranged in descending order, while a line shows the cumulative share of the total as you move from left to right. The first column is the largest contributor; the line reaches 100% at the final category.
The idea is associated with the Pareto principle, often summarized as the 80/20 rule: a relatively small share of causes may account for a large share of effects. In quality improvement, for example, a few defect types might make up most recorded defects. In customer support, a few issue types might account for much of the workload. The exact proportions vary. A chart showing 70% of problems from the top few categories can still be useful, and a chart that happens to show 80% does not prove a universal law.
The main purpose is prioritization. Instead of treating every category as equally important, a Pareto chart helps identify which contributors are worth investigating first. It shows frequency or another measured quantity, not the underlying reason that category is high.
Create the built-in Pareto chart
Prepare two columns: one for category names and one for positive numeric values such as counts, costs, or hours. Include a header in each column, and leave out any grand-total row. For example, a table might list defect types in A2:A6 and their counts in B2:B6.
- Select the headers and data, such as A1:B6.
- On the ribbon, choose Insert > Insert Statistic Chart > Pareto.
- Check that the columns are ordered from highest to lowest and that the line rises toward 100%.
- Add a descriptive chart title, such as Defects by Type, and label the measure if the chart may be read without its source table.
Excel calculates the cumulative percentages for the built-in chart, so a separate percentage column is not required. The chart is most informative when each category has a clear meaning and the values use the same unit. Do not combine counts for some categories with dollar amounts or hours for others.
If Pareto is not offered in the Insert menu, use the manual method below or open the workbook in the desktop version of Excel. Excel for the web and Mac can present different chart menus or capabilities depending on the version and update channel, so do not assume every interface has the same command. The manual table and combo-chart approach is a practical fallback.
Prepare and check the data
A Pareto chart needs comparable category totals. If the source is a list of individual events, first summarize it into one value per category. A PivotTable is one option: select the source data, choose Insert > PivotTable, place the category field in Rows, and place a field that is present on every record in Values, summarized by Count. Review the resulting counts before charting, especially if blanks or inconsistent category labels could split what should be one category into several.
Before inserting the chart, check these points:
- Category labels are distinct and understandable. Correct inconsistent spellings such as Typo and typo if they represent the same category.
- Values are numeric and nonnegative. A Pareto ranking of negative amounts does not have the usual cumulative-share interpretation.
- Categories cover the intended population and time period. If the date range is partial, say so in the title or a note.
- There is no total row among the category values. Including a total as another column distorts the ranking and cumulative share.
- Repeated records are counted according to a consistent rule. Decide whether duplicates are genuine events or data-entry errors before summarizing.
For raw records, distinguish frequency from impact. Counting every complaint answers which types occur most often; summing the cost or time associated with them answers which types have the greatest measured impact. Those rankings can differ, so choose the metric that matches the decision.
Build a Pareto chart manually with formulas
A manual chart gives more control over its calculation and appearance, and it works when the built-in chart type is unavailable. Start with category names in A2:A6 and their values in B2:B6, sorted from largest to smallest. Use headers in row 1. For a small dataset, sort the table by the value column in descending order using Data > Sort. Keep each label with its value while sorting.
With the sorted values in B2:B6, enter this in C2 to calculate the running total through the current row, then fill it down through C6: =SUM($B$2:B2). The result in C2 is the first category’s value; the result in C6 is the total of all five values. The dollar signs keep the start of the range fixed as the formula is filled down.
With the cumulative totals in C2:C6 and the full set of values in B2:B6, enter this in D2 and fill down through D6: =C2/SUM($B$2:$B$6). Format D2:D6 as Percentage. The final percentage should be 100% (or very close if displayed rounding hides decimal places). If it is not, check that the ranges include every category exactly once.
Select the category labels and the value and percentage series, then insert a combo chart. In the Windows desktop dialog, use Insert > Recommended Charts > All Charts > Combo. Set the count or amount series to Clustered Column and the cumulative percentage series to Line, with the percentage series on the secondary axis. Format that axis as a percentage with a scale from 0% to 100%. The left axis then represents the original values, while the right axis represents cumulative share.
The exact axis formatting controls can vary by Excel version. If Excel scales the percentage line like the raw counts, make sure the percentage series is plotted on the secondary axis. A line plotted against the count axis can appear nearly flat and mislead readers.
Worked example: finding the largest contributors
Suppose a team records 100 service incidents in five categories. The counts, already sorted in descending order, are Login 42, Billing 25, Shipping 15, Account access 10, and Other 8. The total is 42 + 25 + 15 + 10 + 8 = 100 incidents. The cumulative shares are 42%, 67%, 82%, 92%, and 100%.
The line crosses 80% after the third category: Login, Billing, and Shipping account for 82 of the 100 incidents. This suggests those three categories are reasonable starting points for investigation, but it does not say why those incidents occurred or whether each is equally easy to prevent. The category labels also matter: Other at 8% may conceal several distinct problems. If that remainder is important to the decision, break it down and chart the underlying types rather than treating it as a single cause.
If the team instead charts total handling hours, the order might change. A rare issue that takes a long time to resolve can matter more than a frequent issue that is fixed quickly. A useful chart therefore states its unit and period, for example, Service incidents by category, January–March, rather than relying on a vague title such as Pareto.
How to interpret the 80/20 point
Read the columns to see the ranking, then follow the cumulative line to see how much of the total is covered by the leading categories. A practical cutoff might be where the line first reaches or passes 80%, but the cutoff is a decision aid, not a mandatory threshold. If the fourth category takes the total from 76% to 84%, it is reasonable to include it when choosing a group of contributors that covers at least 80%.
Use judgment alongside the chart. A category just below the cutoff may be important because it is dangerous, expensive, or legally sensitive, even if it is not frequent. Conversely, a high-frequency category may not deserve the first intervention if it has low impact or cannot be changed. Pareto analysis helps rank observed contributions; it does not establish causation, quantify uncertainty, or set priorities on its own.
Common Excel problems and fixes
- The chart includes a total as a category: remove the grand-total row from the selected range. The total belongs in the calculation, not as another contributor.
- The ranking looks wrong: inspect the source values and labels, then sort the manual table by its value column from largest to smallest. Do not sort only the labels.
- The line ends below 100%: check that the cumulative formula covers every value and that its denominator includes the same complete set. Also confirm no categories were excluded from the chart.
- Blank or unexpected categories appear: check the source for blank labels, extra spaces, misspellings, and formula cells that return empty text. Clean or consolidate labels before summarizing.
- The chart has too many columns to read: group minor categories into Other only when that grouping is meaningful, and keep the underlying detail available. Avoid hiding a category that is important to the decision.
- The percentage line is hard to read: in a manual combo chart, place it on the secondary axis and format that axis as percentages from 0% to 100%.
Also consider whether the time period is representative. A single unusual week can move the ranking substantially. Comparing separate periods or repeating the analysis over time can reveal whether the leading contributors are persistent or temporary.
Pareto chart versus a regular bar chart
A regular bar chart is often better when categories have a natural order, when comparisons should be made without a cumulative line, or when the categories are not ranked by contribution. A Pareto chart is useful when the question is specifically which categories contribute most to a total and how quickly the leading categories accumulate toward that total. Its descending order makes the ranking immediately visible.
A Pareto chart also differs from a histogram. A histogram groups numeric measurements into intervals, such as delivery times from 0–1 day and 1–2 days. A Pareto chart ranks named categories such as late shipment, damaged item, or address error. Excel’s chart menu places Pareto among statistical charts, but that does not make it a histogram of continuous measurements.
Frequently Asked Questions
Does the 80/20 rule have to be exact in a Pareto chart?
No. The phrase describes a commonly observed imbalance, not a guaranteed ratio. Your chart may show 60/40, 90/10, or another pattern. Report the actual cumulative shares rather than forcing the data to fit 80/20.
How do I show the 80% point in Excel?
Read the cumulative-percentage line and identify the first category where it reaches or passes 80%. In a manual chart, the percentage values are in the helper column; you can also add a separate reference line if readers need to see the threshold clearly.
Can a Pareto chart use costs instead of counts?
Yes. Use one comparable cost total per category instead of event counts. The columns then rank cost contribution, and the cumulative line shows each category’s share of the overall cost. State the currency and period in the chart title or labels.
Why does my Pareto chart not sort the categories?
The built-in statistical chart normally ranks categories by value. If you are using a manual chart, sort the complete category-and-value rows in descending order before calculating cumulative totals. Sorting labels separately breaks the data.
Can I make a Pareto chart in Excel for the web?
Available chart commands can vary in Excel for the web. If you cannot find the Pareto chart option, calculate cumulative percentages in helper columns and create a column-and-line combo chart, or open the workbook in the Windows desktop version of Excel.
Related
- Microsoft Excel: How to Flip a Row to a Column Quickly
- Microsoft Excel Format Cells: A Practical Guide for Data
- Microsoft Excel Employee Schedule Template: Setup Tips
Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.
More in Charts & pivot tables
- Excel Trendline for Part of Data: Steps and OptionsExcel applies a trendline to a chart series, not to a hand-selected handful of points within a series.…
- How to Make a Histogram in Excel With Custom BinsTo make a histogram in Excel, select your numeric data, choose Insert > Insert Statistic Chart > Histogram,…
- Geographical Location Chart in Excel: Map Chart GuideIn Excel for Microsoft 365 on Windows, select a table containing geographic areas and numeric values, then choose…
- Excel Live Dashboard: Build, Refresh, and Share OneAn Excel live dashboard is a worksheet that summarizes data and updates when its source data is changed…
- Excel Waterfall Chart: Remove Connector LinesTo remove connector lines from a waterfall chart in Excel for Microsoft 365 on Windows, select the chart,…
- How to Make a Chart in Excel With X and Y AxesTo make a chart in Excel with numeric X and Y axes, put the X values in one…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.