To make a Pareto chart in Excel for Microsoft 365 on Windows, first put each issue or category beside its count, sort the counts from largest to smallest, calculate a running percentage, then insert a combo chart with counts as columns and the running percentage as a line on a secondary axis. This method works well for category-and-count data and gives you control over the chart; Excel also has a built-in Pareto chart option for suitable source data.
What a Pareto chart shows
A Pareto chart combines two views of the same data. Its bars rank categories from most frequent or largest to least, while a line shows the cumulative share accounted for as you move across the bars. The bar heights answer which categories matter most individually; the line answers how much of the total is covered by the leading categories together.
For example, a repair team might record 100 service complaints: 40 about late delivery, 25 about damaged packaging, 15 about missing parts, 12 about instructions, and 8 about billing. A Pareto chart makes the ranking visible and shows that the first three categories account for 80 of the 100 complaints.
The chart is associated with the Pareto principle, often summarized as an 80/20 rule. Treat that as a way to look for uneven distributions, not as a promise that 20 percent of causes will always explain 80 percent of results. The actual proportions depend on the data, how categories are defined, and the period being measured.
Prepare and check your data
For the manual method below, use one row per category and a numeric count or measure. The example table should have headers in A1 and B1, with categories in A2:A6 and counts in B2:B6:
- Late delivery — 40
- Damaged packaging — 25
- Missing parts — 15
- Instructions — 12
- Billing — 8
Sort the rows by count in descending order so the largest value is first. Select the whole table, then use Data > Sort. In the Sort dialog, choose the count column and Largest to Smallest; make sure the option indicating that the data has headers is set appropriately. Sorting entire rows preserves the match between each category and its count.
Before charting, confirm that counts are numeric, categories are consistently named, and the total represents a meaningful set of observations. Combine duplicate labels such as Packaging damage and Damaged packaging if they mean the same thing. Decide how to handle blank categories, missing records, and any categories called Other. A large Other category can hide useful detail, while splitting every small issue into its own label can make a chart difficult to read.
Build the cumulative percentage
Add a third column headed Cumulative % in C1. With categories in A2:A6 and their descending counts in B2:B6, enter this formula in C2:
=SUM($B$2:B2)/SUM($B$2:$B$6)
This layout assumes the counts are in B2:B6 and have already been sorted from largest to smallest. The formula returns 40% in C2 for the example, because the first category has 40 complaints out of 100. Copy it down through C6. The results should be 40%, 65%, 80%, 92%, and 100%. Format C2:C6 as Percentage using Home > Number, or the Percentage Style button on the Home tab.
The dollar signs keep the total range fixed as you copy the formula, while the first part expands from B2 to B3, B4, and so on. If you add more categories, extend the total range in the formula and the chart source. If the total is zero, the formula will return a division error; check that the data contains positive counts before calculating percentages.
Create the combo chart
- Select A1:C6, including the headers.
- Choose Insert > Insert Combo Chart > Create Custom Combo Chart. If that command is not shown in your ribbon layout, look for the Combo chart options on the Insert tab.
- Set the count series to Clustered Column and the Cumulative % series to Line. Select Secondary Axis for the cumulative percentage series.
- Choose OK. Excel displays category names along the horizontal axis, count bars on the primary vertical axis, and a cumulative line against the secondary vertical axis.
The secondary axis matters because counts and percentages use different scales. Without it, the cumulative series may be compressed against the bottom of the chart or otherwise hard to interpret. Since the percentage column ends at 100%, the cumulative line should rise from the first category to 100% at the last one.
Excel for Mac also provides combo charts from the Insert tab, though labels and ribbon placement can differ slightly by release. Select the data, choose the combo chart option, and assign the cumulative percentage series to a secondary axis. The steps here are for the desktop version of Excel; the options available in Excel for the web can vary.
Try Excel’s built-in Pareto chart
Excel for Microsoft 365 includes a Pareto chart in its statistical chart choices. Select an appropriate source range and choose Insert > Insert Statistic Chart > Pareto. Excel creates a descending bar chart with a cumulative percentage line. The exact result depends on the shape of the selected data: the statistical chart is designed around frequency distributions and may treat numeric observations as values to group into bins rather than interpret a two-column category-and-count table as you intended.
For that reason, inspect the chart before relying on it. If your source is already summarized by named categories and counts, the manual combo-chart method above is a dependable way to control the category labels and series. If you start with individual numeric measurements, the built-in Pareto option may be useful, but review its bins and labels to confirm that they answer your question. On Mac, look for Pareto among the statistical chart options on the Insert tab; names or placement may vary with the Excel version.
Format the chart for clear reading
Give the chart a specific title, such as Complaints by Type, and name the axes if readers may not know what the numbers represent. The left axis should describe the count or measure, and the right axis should indicate cumulative percentage. Format the secondary axis as a percentage and, when appropriate, set its bounds from 0 to 1 (equivalent to 0% to 100%). You can format an axis by right-clicking it and choosing Format Axis.
Use a restrained color scheme so the bars remain easy to compare and the cumulative line stands out. Data labels can help readers see exact counts, but labeling every point and bar may clutter a chart with many categories. To add or remove chart elements, select the chart and use the Chart Design tab’s Add Chart Element control, or the Chart Elements button beside the chart when it appears.
You may want to mark a decision threshold, such as 80%, but do not imply that it is a universal cutoff. One option is to add a helper series with the same target percentage for every category and display it as a line on the secondary axis. Explain what the threshold means for the decision at hand—for example, a team may choose to investigate categories that together account for 80% of recorded complaints.
Interpret the result without overclaiming
Read the bars from left to right to identify the largest categories, then use the cumulative line to see how quickly the total builds. In the example, the first two categories account for 65% of complaints, and the first three account for 80%. That can help prioritize investigation, but the chart does not establish why late delivery occurs or whether fixing it is the best use of resources.
Counts also do not show severity, cost, or exposure unless those are what you measured. A rare defect with a serious consequence may deserve attention even if it appears at the far right. Likewise, a complaint count can be misleading if one product or location had far more customers than another. Consider whether rates per shipment, hours lost, or total cost would better match the question. A Pareto chart summarizes the measure supplied; it cannot correct a biased or incomplete collection process.
Common problems and fixes
- The cumulative line is flat or missing: Check that the cumulative percentage column contains numbers, not text, and that it is included as a separate chart series. In a combo chart, set it to Line and select Secondary Axis.
- The percentages do not end at 100%: Check the formula’s total range and confirm that all category counts are included. A total that omits rows will distort every cumulative value.
- The bars are not in descending order: Sort the entire category-and-count table by the count column, not just the count cells. Recheck the chart after sorting.
- Excel shows unexpected bins or labels: The built-in statistical chart may be interpreting raw numeric values as a distribution. For named categories with precomputed counts, create the cumulative column and combo chart manually.
- There are too many tiny categories: Consider grouping genuinely related low-frequency items into Other, but document the grouping and retain the detailed source data for follow-up.
When to use another chart
A Pareto chart is useful when the question is about ranking categories and their cumulative contribution. A regular bar chart may be clearer when the goal is simply to compare a few categories and a running total adds no value. A histogram is better for showing the distribution of a continuous numeric measure, such as delivery times; its bins represent ranges of values, not named causes. A line chart is more appropriate when the main question is how a measure changes over time.
For recurring reporting, a PivotTable can summarize a longer event list by category, and the summary can feed a Pareto-style combo chart. Refresh the PivotTable after adding records, then confirm that the categories remain sorted by the measure. If the data is maintained as an Excel Table, new rows are easier to include in related analysis, though chart-source behavior still depends on how the chart was created.
Frequently Asked Questions
Does Excel have a Pareto chart?
Yes. Excel for Microsoft 365 on Windows includes Pareto under Insert > Insert Statistic Chart. For a pre-summarized table of named categories and counts, a manual combo chart may give you more predictable labels and control.
How do I sort a Pareto chart from highest to lowest?
Sort the source table by its count or measure column in descending order, making sure the category labels move with their values. A manually built chart follows the order of its source data.
Why does my Pareto chart not show an 80/20 split?
The 80/20 phrase is a general observation, not a rule that every dataset follows. Your chart may show a different distribution, such as 65% from the top two categories or 90% from the top five.
Can a Pareto chart use percentages instead of counts?
Yes, if percentages are the measure you want to rank and they are comparable across categories. Be clear about what each percentage represents, and calculate the cumulative share from the underlying values if the goal is to show each category’s contribution to a total.
Can I make a Pareto chart from raw data?
Often, but the right setup depends on what the raw data contains. The built-in statistical chart can group numeric observations into bins; for event labels such as complaint types, first summarize the records into category counts, then build the combo chart.
Related
- Microsoft Excel Snowball Debt Payoff Template Setup
- Microsoft Excel Redo Shortcut: How to Restore an Action
- Microsoft Excel Project Timeline Template: How to Build One
Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.
More in Charts & pivot tables
- Excel Pivot Table Instructions for Building Clear ReportsTo create a PivotTable in Excel for Microsoft 365 on Windows, select a cell in a clean data…
- Treemap Chart in Excel: How to Create One Step by StepTo create a treemap chart in Excel for Microsoft 365 on Windows, arrange your data in hierarchy columns…
- Excel Stack Chart: How to Create and Format OneTo make a stack chart in Excel for Microsoft 365 on Windows, arrange categories in the first column…
- Pivot Chart Secondary Axis: How to Add and Format OneYes. In Excel for Microsoft 365 on Windows, select the PivotChart series you want to rescale, open Format…
- Excel Pie Chart With Breakdown of Slices: How to Make OneTo show the detail behind one slice in Excel, create a Pie of Pie or Bar of Pie…
- Excel Dashboard vs Power BI: Which Should You Choose?Excel is usually the better choice for a small, flexible dashboard built around spreadsheets; Power BI is usually…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.