To make a Pareto chart in Excel for Microsoft 365 on Windows, put one category per row in a single column, select the data, then choose Insert > Insert Statistic Chart > Pareto. If your data is already summarized as categories and counts, create a combo chart with descending count columns and a cumulative-percentage line instead; the built-in Pareto chart is intended for raw observations.
What a Pareto chart shows
A Pareto chart combines two views of the same data. Its columns show how often each category occurs, arranged from the most frequent to the least frequent. A line shows the running cumulative share of the total, usually read against a percentage scale on the right-hand axis. Together, they help reveal which few categories account for a large share of a problem or outcome.
For example, a support team might categorize 100 complaints. If billing errors account for 40, late deliveries 28, login problems 15, damaged items 10, and other issues 7, the first two categories make up 68% of complaints. The chart makes that concentration visible at a glance and helps the team decide which causes to investigate first. It describes the pattern in the recorded data; it does not prove why the pattern exists.
The chart is associated with the Pareto principle, often summarized as an 80/20 rule: a minority of causes may account for a majority of effects. That is a useful prompt to look for concentration, not a guaranteed mathematical result. A real dataset may have no sharp cutoff, or the leading categories may account for much less or much more than 80%.
Choose the right data layout
Before inserting a chart, decide whether you have raw records or a frequency summary. Raw records contain one observation per row, such as one complaint type for each customer complaint. A summary already has one row per category and a count beside it. The distinction matters because Excel’s built-in Pareto chart counts raw category entries; it is not the best way to chart a table whose counts have already been calculated.
- Raw observations: Use the built-in Pareto chart when each row is one event and a column records its category.
- Summarized counts: Use a combo chart when the table already lists each category and its total.
- Numeric measurements: Consider whether you need a histogram of value ranges rather than a Pareto chart of named causes. A numeric histogram groups values into bins; a Pareto chart ranks categories.
Use consistent category names and a consistent period or population. “Late delivery” and “Delivery late” will be treated as separate labels unless you standardize them. Decide in advance how to handle blank entries, duplicate records, and categories that are too vague to interpret.
Create a Pareto chart from raw data
Suppose cells A1:A101 contain a heading in A1 and 100 complaint categories in A2:A101. Each row should represent one complaint, and the category should be recorded consistently. To insert the chart in Excel for Microsoft 365 on Windows:
- Select the category column, including its heading and all observations.
- Choose Insert > Insert Statistic Chart > Pareto.
- Review the bars and cumulative line, then add a clear chart title and check that the categories are legible.
Excel counts the occurrences of each category, sorts the bars by frequency, and plots the cumulative percentage. In this example, the first column represents the most frequent complaint type; the line rises as the next categories are added and reaches 100% at the end. If the chart option is unavailable in your Excel build, create the chart manually from a summary table as described below. Excel for Mac has statistical chart options in the Insert tab, though menu wording and availability can vary by release. Excel for the web may offer fewer chart-creation controls; the manual combo-chart method is a practical fallback if the Pareto option is missing.
Build one from summarized counts
For an existing frequency table, sort the categories by count from largest to smallest before charting. Use this example: A2:A6 contains Billing, Late delivery, Login, Damaged item, and Other; B2:B6 contains 40, 28, 15, 10, and 7. The total is 100. Put a heading such as Cumulative % in C1. With these category names in A2:A6 and counts in B2:B6, enter this formula in C2 and fill it down through C6:
=SUM($B$2:B2)/SUM($B$2:$B$6)
Format C2:C6 as Percentage. The results are 40%, 68%, 83%, 93%, and 100%. For instance, the second result is (40 + 28) divided by 100, or 68%. The dollar signs keep the total range fixed as you fill the formula down, while the last cell in the running range advances from B2 to B3, B4, and so on.
To chart this summary, select the category, count, and cumulative-percentage data, then choose Insert > Insert Combo Chart > Create Custom Combo Chart. Set the count series to Clustered Column and the cumulative-percentage series to Line; select Secondary Axis for the percentage series, then confirm. If your Excel build presents a different combo-chart dialog, use the equivalent option that assigns the line to a secondary axis. Set that axis to display percentages from 0% to 100% where possible, and confirm the category order remains descending.
Interpret the bars and cumulative line
Read the columns from left to right to find the most frequent categories. Then follow the cumulative line to estimate how much of the total is covered by the leading categories. In the example, the first three categories account for 83% of complaints. If a team has limited time, that can suggest a practical starting point for investigation, but it does not mean the remaining 17% is unimportant.
Frequency is only one way to prioritize. A rare failure could be more serious than a frequent minor inconvenience. For safety, compliance, customer impact, or cost decisions, consider severity and other relevant measures alongside counts. A Pareto chart is descriptive: it helps prioritize questions and attention, while further analysis is needed to establish root causes and choose a response.
Make the chart easier to read
A useful Pareto chart needs readable labels and a clear definition of what is being counted. Give it a title such as “Customer complaints by type, January–March” rather than a generic title. Label the count axis and, for a manually built chart, the percentage axis. If category names are long, widen the chart, shorten labels carefully, or use a horizontal bar chart with a separate cumulative line if that communicates the information more clearly.
- Use a single, consistent count measure, such as number of tickets, defects, or returns.
- Keep the descending order; it is central to reading the chart.
- Use a contrasting line color and visible markers so the cumulative series is distinct from the bars.
- Show the reporting period and population in the title or a nearby note.
- Group minor categories only when the grouping is meaningful, and label it honestly, such as “Other.”
Do not add a second percentage series that duplicates the cumulative line. Also avoid interpreting the crossing of an 80% level as a universal action threshold. It is simply a point on the cumulative scale; the appropriate priority depends on the question and consequences.
Common problems and fixes
The bars are not in descending order. In a manual chart, sort the source table by count, largest to smallest, and check that the chart uses the sorted rows. In the built-in chart, verify the selected data and chart type. Avoid sorting only one column, because that can detach counts from their category labels.
The cumulative line is above 100% or ends at the wrong value. Check that the formula’s denominator includes every count exactly once and that the source contains numbers rather than numbers stored as text. In the manual example, all five counts total 100, so the final cumulative value should be 100%. A value over 100% often signals a mismatched range or duplicated total.
Categories appear as separate bars when they should be combined. Look for spelling differences, leading or trailing spaces, and inconsistent abbreviations. Clean or standardize the source labels before charting. Do not merge distinct causes just to make the chart look simpler; grouping can conceal an issue that deserves its own category.
The percentage line looks flat or confusing. In a combo chart, make sure the cumulative series is plotted as a line on a secondary axis and that the axis uses percentage bounds. If all categories have similar counts, the line may rise steadily without a dramatic bend; that can be an accurate representation rather than a chart error.
Pareto chart versus other Excel charts
A Pareto chart is useful when categories need ranking and the cumulative contribution matters. A regular bar chart can also compare categories, but it does not automatically communicate cumulative share. A histogram is for the distribution of numeric measurements across ranges, such as delivery times grouped into bins. A pie chart shows parts of a whole but makes close values harder to compare and does not show the ranked cumulative progression.
For summarized data, a manually created combo chart gives more control over labels, grouping, and the calculation. The built-in Pareto option is quicker for raw category observations. If the main task is comparing values over time, a line chart or a series of period-by-period bar charts may be more suitable; one overall Pareto chart can hide changes between periods.
Frequently Asked Questions
Can I make a Pareto chart from a table of counts?
Yes. Sort the counts in descending order, calculate a running cumulative percentage, and create a combo chart with count columns and a percentage line on a secondary axis. This is usually clearer than feeding already summarized counts into the built-in Pareto option.
Why can’t I see Pareto under Insert in Excel?
Check that you are using a desktop Excel version with statistical chart support and that the chart option is not hidden by a different ribbon layout. Availability and labels can vary by release, especially in Excel for the web or Mac. A manually constructed combo chart works when the built-in option is unavailable.
Does a Pareto chart always show an 80/20 split?
No. The 80/20 phrase is a rule of thumb, not a required shape or result. Your cumulative line may reach 80% after two categories, many categories, or not at a particularly meaningful point at all.
Can I use percentages instead of counts?
You can create a manual chart if the percentages represent comparable category shares and add up to the full population. Counts are often easier to verify and explain. Do not mix percentages with different denominators, such as rates from groups of very different sizes, without making the comparison clear.
How many categories should a Pareto chart include?
Include enough categories to answer the question without making labels unreadable. A long tail can be grouped as “Other” if the combined group is defined consistently, but keep important or high-impact categories separate. There is no universal correct number.
Related
- Merge Excel Sheets to Find Specific Names in Excel
- How to Merge Two Tables in Excel Using Power Query
- Median vs Average in Excel: Which Should You Use?
Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.
More in Charts & pivot tables
- Excel KPI Dashboard Examples and How to Build ThemUseful Excel KPI dashboard examples include a sales dashboard for revenue and quota attainment, a support dashboard for…
- How to Import a Chart Template in Excel and Apply ItTo import a chart template in Excel for Microsoft 365 on Windows, copy its .crtx file into your…
- Excel Project Dashboard Template: Build One That WorksAn Excel project dashboard template is a workbook that turns a structured project list into a quick view…
- Pivot Table Report Is Invalid Error: Causes and FixesIn Excel, a “PivotTable report is invalid” message usually means the PivotTable’s source data or its field layout…
- Excel Pivot Table Refresh Failed? Fix the Common CausesIf an Excel PivotTable refresh failed, first confirm that its source data is available and has a valid,…
- Stacked Bar Chart Excel Template: Build and Customize OneA stacked bar chart Excel template is a reusable chart setup that shows how several series contribute to…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.