A Pareto chart in Excel uses categories and their counts or costs, sorted from largest to smallest, to show which causes contribute most to a total. For example, enter defect types and counts in two columns, then choose Insert > Insert Statistic Chart > Pareto; Excel creates descending columns and a cumulative-percentage line.
What a Pareto chart shows
A Pareto chart combines a bar chart with a cumulative line. Each bar represents one category, such as a type of product defect, customer complaint, or delay. The bar height shows its value, usually a count or cost. The line adds the categories cumulatively from left to right and expresses the running total as a share of all categories.
This format is associated with the Pareto principle, often summarized as an 80/20 rule: a small number of causes may account for a large share of a problem. Treat that as a useful question to investigate, not a guarantee. A real chart might show that the leading few categories account for 65%, 82%, or some other proportion.
A Pareto chart is most useful when categories are comparable and the measure has a meaningful total. Counts of separate defects are a natural example. If one event can be counted in several categories, explain that overlap; otherwise, readers may assume the bars represent distinct events and that the cumulative line is a share of unique cases.
A worked Pareto chart data example
Suppose a team records 100 product defects and groups them into five causes. Enter the following summary in Excel, with category names in column A and counts in column B:
| Defect category | Count | Cumulative share |
|---|---|---|
| Scratches | 42 | 42% |
| Missing parts | 25 | 67% |
| Wrong label | 15 | 82% |
| Late shipment | 10 | 92% |
| Other | 8 | 100% |
The counts add to 100. Scratches are the most frequent cause, and the first three categories account for 82% of the recorded defects. That does not prove the first three causes are the easiest or cheapest to fix; it identifies where further investigation may have the greatest potential impact.
For Excel’s built-in Pareto chart, the two essential columns are the category and its numeric value. The cumulative-share column is included here to make the calculation visible, but the built-in chart calculates its own cumulative line from the values.
Prepare the data before charting
Make a clean summary table rather than charting a raw log with one row per event. A summary gives Excel a single value for each category and makes the chart easier to check. Use consistent category names, numeric values, and a clear heading for each column.
- Decide what one observation means: a defective unit, a customer case, a minute of downtime, or a dollar amount.
- Use categories that are distinct enough to interpret. Combine tiny categories into Other only when that grouping will not conceal an important issue.
- Choose a consistent time period and population, such as returns received during one quarter.
- Check for blanks, text-formatted numbers, duplicate category labels, and totals that accidentally include a grand-total row.
- Sort the summary values from largest to smallest when making a custom chart. Excel’s built-in Pareto chart arranges categories by value.
If the source is a transaction list, summarize it first with a PivotTable or formulas. In Microsoft 365 for Windows, select the source data and choose Insert > PivotTable. Put the category field in Rows and an event identifier in Values, then set the Values calculation to Count if Excel does not choose it automatically. For monetary impact, use a numeric cost field and summarize it by Sum instead. A PivotTable is handy when the source changes and needs refreshing; a small fixed example can simply be summarized by hand.
Create Excel’s built-in Pareto chart
In Excel for Microsoft 365 on Windows, select the category and value columns, including their headings, and choose Insert > Insert Statistic Chart > Pareto. Excel inserts a chart with descending columns and a cumulative-percentage line. If the Pareto option is unavailable or disabled, check that the selection contains category labels and numeric observations, and that you are using a version of Excel that supports statistical charts.
- Click the chart title and replace it with a specific description, such as Defects by Cause, Q2.
- Check that the largest category is at the left and the cumulative line rises toward 100% at the right.
- Add axis titles or a data source note when the metric or reporting period might otherwise be unclear. Use Chart Design > Add Chart Element for chart elements such as axis titles.
- Keep the category labels readable. If there are many long labels, widen the chart or shorten labels in the source table without making their meaning ambiguous.
The built-in statistical chart is quick, but it offers less control over the layout than a chart built from a prepared cumulative-percentage column. Menu labels can vary slightly with the Excel update channel. Excel for Mac also has statistical chart options, but the ribbon layout can differ; look on the Insert tab for the statistical chart menu. Excel for the web may not offer the same chart-creation and formatting controls as the Windows desktop application.
Calculate cumulative percentages for a custom chart
A custom chart is useful when you want to display a particular cumulative calculation, add a target line, or control the axes. First sort the categories by count in descending order. With the five example categories in A2:A6 and their counts in B2:B6, enter this in C2 and fill it down through C6:
With the counts in B2:B6, the formula in C2 returns 42% and, when filled down, the final result in C6 is 100%:
=SUM($B$2:B2)/SUM($B$2:$B$6)
Format C2:C6 as Percentage. The running numerator expands as the formula is filled down, while the denominator remains the total of all five counts. In this example the displayed results are 42%, 67%, 82%, 92%, and 100%. If you insert or remove categories, adjust the fixed denominator range so that it still covers the complete data set.
To chart the custom calculation, select A1:C6 and choose Insert > Insert Combo Chart > Create Custom Combo Chart. Set the count series to Clustered Column and the cumulative-share series to Line, then select Secondary Axis for the line. In the Format Axis pane for the secondary vertical axis, set the minimum to 0 and maximum to 1, then use Percentage number formatting. The line will then use a 0% to 100% scale instead of sharing the count scale with the columns.
Counts, costs, and other measures
A chart’s message depends on the measure in the value column. Counting defect incidents answers which causes happen most often. Summing repair costs instead answers which causes account for the greatest total cost. Those rankings can differ: a rare failure may be more expensive overall than a frequent, low-cost issue.
Choose one measure that matches the decision. If both frequency and impact matter, make separate charts or clearly labeled comparisons rather than combining unlike units into one value column. A time-based chart can rank categories by total downtime, for example, but it does not show how many separate stoppages occurred unless that count is charted separately.
Also make the denominator explicit. If the chart includes only one factory, product family, or month, label that scope. A cumulative percentage is only meaningful relative to the categories included in its total; excluding a major category can make the displayed shares misleading.
Common chart problems and how to fix them
- The bars are not in the expected order: For a custom chart, sort the entire category-and-value table by values from largest to smallest. Do not sort one column on its own, which would disconnect counts from their labels.
- The cumulative line is missing or looks flat: In a custom combo chart, confirm that the percentage series is a Line and uses the secondary axis. Check that the cells contain numeric percentages, not text that looks like percentages.
- The cumulative line ends below 100%: Verify the formula’s denominator includes every displayed category and that the final row has not been omitted. If categories are intentionally excluded, state that scope clearly.
- There are too many bars: Combine categories only when the grouping is meaningful, or limit the display to a defined set and report the omitted total. A long list of tiny labels can make the chart technically correct but hard to use.
- The 80% point seems arbitrary: It is a reference, not a required cutoff. Choose priorities using business impact, feasibility, risk, and the purpose of the analysis, not a rule that every chart must reach exactly 80% at a particular bar.
Reading the chart without overclaiming
Read the columns first to see the order and magnitude of categories, then use the line to judge how quickly their combined share accumulates. In the example, the third cause brings the running total to 82%, so it could be a sensible starting point for a deeper review if reducing defect volume is the goal.
The chart describes the data collected; it does not establish why a cause occurred or whether a proposed fix will work. Confirm the definitions, inspect examples from leading categories, and consider whether collection practices changed across the reporting period. A category can appear dominant because it is recorded more consistently, not necessarily because it is the only important cause.
Frequently Asked Questions
What data do I need for a Pareto chart in Excel?
At minimum, provide one column of category labels and one column of numeric values, such as incident counts or total costs. Use a clear scope and avoid including a total row as if it were another category.
Does Excel automatically sort a Pareto chart?
Excel’s built-in Pareto chart orders the categories by value. For a manually built combo chart, sort the source table from largest to smallest yourself so the cumulative line follows the intended order.
Can I make a Pareto chart from raw data?
Yes, but first summarize the records by category, using a PivotTable or another summary method. A built-in Pareto chart expects categories with values; it is not a substitute for deciding how raw records should be counted or grouped.
Why does my Pareto chart not show an 80/20 split?
The 80/20 phrase is a rough principle, not a condition a chart must satisfy. Your data may have a more even distribution, or a different small group of categories may account for most of the total.
Should I chart frequency or cost?
Use the measure that matches the question. Count events to find the most common causes; sum costs to find the largest contributors to total cost. If both matter, show them separately and explain the difference.
Related
- Microsoft Excel Quick Repair: Steps and What It Fixes
- Microsoft Excel Password Protection: How It Works
- Microsoft Excel Profit and Loss: Build a P&L Report
Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.
More in Charts & pivot tables
- Google Sheets Line Chart Maker: Create and Format a ChartGoogle Sheets has a built-in line chart maker: select your data, choose Insert > Chart, then select Line…
- Excel Pivot Table Not Showing All Data? Causes and FixesIf an Excel PivotTable is missing data, first check that its source range includes every record, then refresh…
- Add a Trendline to an Excel Bar Chart? Here’s How It WorksYes—Excel for Microsoft 365 on Windows can add a trendline to a supported 2-D bar chart. Select the…
- Pie Chart Angle Formula: How to Calculate Each SliceThe pie chart angle formula is slice angle = (category value ÷ total of all values) × 360°.…
- Excel Waterfall Chart Help: Build, Format, and Fix OneTo create a waterfall chart in Excel for Microsoft 365 on Windows, place each step of your change…
- Microsoft Excel Org Chart Add-Ins and Built-In OptionsMicrosoft Excel for Microsoft 365 does not require an add-in to make a basic org chart: use the…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.