To make a Pareto chart in Microsoft Excel for Microsoft 365 on Windows, arrange categories and their counts in two columns, select the data, then choose Insert > Insert Statistic Chart > Pareto. Excel sorts the categories from largest to smallest and adds a cumulative-percentage line; if you want more control, you can build the same chart with a few formulas and a combo chart.
What a Pareto chart shows
A Pareto chart combines descending columns with a line showing the running share of the total. The columns make it easy to see which categories occur most often or have the greatest measured impact. The line shows how quickly the leading categories account for the whole dataset. It is commonly used to prioritize problems, causes, requests, costs, or other items that can be grouped into categories.
The chart is associated with the Pareto principle, often summarized as an 80/20 rule: a relatively small number of causes may account for a large share of an effect. Treat that as a useful possibility to investigate, not a promise that every dataset will follow an exact 80/20 split. A Pareto chart describes the values supplied; by itself, it does not prove why a category is high or what action will fix it.
Prepare and check your data
Use one row per category, with a clear heading in the first row and a numeric count or value beside each label. For example, a service team could record the number of complaints by issue type. Before charting, decide what the value represents: number of incidents, hours of delay, dollars of cost, or another consistent measure. Do not mix measures in the same column.
- Remove blank rows within the data and check that counts are numeric, not numbers stored as text.
- Use consistent category names. Excel may treat spelling variants such as Late delivery and Late deliveries as separate categories.
- Decide whether repeated records should be counted individually or grouped into category totals. If you already have one row per incident, summarize it first so the chart uses category frequencies.
- Include zero-count categories only if they matter to the analysis; a long list of empty categories makes the chart harder to read.
For a small table, enter the counts directly. For a large log of individual events, create a PivotTable or otherwise summarize the records into category totals first. The chart should reflect the period and population you intend to compare—for instance, complaints received in one month rather than an unclear mixture of months.
Create a Pareto chart with Excel’s built-in option
In current desktop Excel for Microsoft 365 on Windows, the built-in chart is the quickest route when your data is already arranged as categories and values. Select both columns, including their headings, and use these steps:
- Click a cell in the data or select the full category-and-count range.
- Open Insert > Insert Statistic Chart > Pareto. If the chart gallery is collapsed, look for the Statistical Chart options in the Insert tab.
- Check that the bars represent the intended numeric values and that the categories appear in descending order. Excel calculates the cumulative percentage for the line.
- Add a title and, if needed, axis titles using the Chart Design and Format tabs that appear when the chart is selected.
The built-in chart is convenient because it handles sorting and the cumulative line for you. If Excel produces an unexpected chart, check that the selection includes the category labels and numeric values, that there are no blank or text-formatted counts, and that the intended rows are included. Menu wording can vary slightly by Excel build. Excel for the web and Mac may present chart galleries differently; if the Pareto option is unavailable in your build, use the manual method below. On Mac, the Command key replaces Ctrl for selecting non-adjacent ranges, but the built-in chart command depends on the available version and ribbon.
Build one manually with formulas
A manual chart is useful when you want to control the calculation, format, or data layout. It also makes the cumulative values visible in the worksheet for checking and reuse. Start with the category-and-count table in A1:B6, with headings in row 1 and five categories in rows 2 through 6. Sort the rows by count from largest to smallest using Data > Sort, sorting by the count column in descending order. Keep each label attached to its count while sorting.
With the sorted counts in B2:B6, put Cumulative count in C1. With the first sorted count in B2 and the running total in C2, enter =SUM($B$2:B2) and fill the formula down through C6. The dollar signs keep the start of the range fixed while the ending row advances. Then put Cumulative percentage in D1. With cumulative totals in C2:C6 and all counts in B2:B6, enter =C2/SUM($B$2:$B$6) in D2 and fill down. Format D2:D6 as Percentage from Home > Number, choosing an appropriate number of decimal places.
For example, suppose the counts in descending order are 40, 25, 20, 10, and 5. The total is 100, so the cumulative counts are 40, 65, 85, 95, and 100, and the cumulative percentages are 40%, 65%, 85%, 95%, and 100%. These results provide a useful check: the final cumulative count should equal the total, and the final percentage should be 100% apart from minor display rounding.
For a straightforward chart, place a clean chart table in another range with three columns: Category, Count, and Cumulative percentage. Select those columns and choose Insert > Insert Combo Chart > Create Custom Combo Chart. Set Count to Clustered Column and Cumulative percentage to Line, then select Secondary Axis for the percentage series. In some ribbon layouts, you may first open Insert > Insert Combo Chart and choose the custom combo option. Confirm that the right-hand axis runs from 0% to 100%, and that the line uses the cumulative-percentage values rather than the counts.
Read the chart and choose a useful cutoff
Read the bars from left to right: the first bar is the largest category, and each following bar is smaller or equal. Follow the cumulative line to see how much of the total is covered by the categories so far. In the example, the first three categories account for 85% of the 100 cases. If a team is deciding where to investigate first, those categories may be a sensible starting point, but the chart does not mean the remaining two are unimportant.
An 80% cutoff is a reference, not a rule. If the line crosses 80% after two categories, that is one possible prioritization signal. If it does not reach 80% until most categories are included, the causes may be more evenly distributed. Use practical considerations too: severity, cost, customer impact, and the effort needed to address a category can matter more than its frequency alone. For example, a rare safety-related issue may warrant attention even if it contributes little to the total count.
Format the chart for clear communication
Give the chart a descriptive title that names the metric and, where useful, the period covered, such as Complaint causes, January–March. Label the left axis with the unit being counted and the secondary right axis as cumulative percentage. The line should finish at 100%; if it does not, check the formula range and whether every count is included. Use readable category labels and avoid excessive decoration that makes the bars or line harder to compare.
If labels are long, widen the chart or shorten labels consistently in a separate display column. You can also use data labels selectively, such as showing values on bars or percentages at important points, but labeling every element can clutter a chart with many categories. Keep the descending order visible; sorting is fundamental to the Pareto view, not just a cosmetic choice.
Common problems and how to fix them
- Bars are not in descending order: For a manual chart, sort the complete rows by count from largest to smallest. Do not sort only the count cells, which would detach values from category names.
- The line ends below 100%: Check that the denominator includes every category and that the cumulative formula is filled through the final row. In a manual chart, make sure the line series includes the last percentage cell.
- Percentages appear as counts: In a combo chart, set the cumulative series to Line and enable Secondary Axis. Format the percentage values as percentages rather than applying percentage formatting to the count data.
- Some categories are missing or duplicated: Check the selected range and standardize labels, including spaces and spelling. If raw records contain repeated categories, summarize them before charting.
- A category has a negative value: A standard Pareto chart is generally intended for nonnegative contributions. Investigate why the measure is negative or choose a chart suited to signed values instead of presenting a misleading cumulative share.
Built-in chart or manual method?
Choose the built-in Pareto option when the data is clean and you want a quick chart with Excel handling the order and cumulative line. Choose the manual method when you need to inspect every cumulative calculation, customize the chart’s series, or make the underlying values part of a report. Both methods depend on sensible category definitions and a consistent measure; neither can correct a misleading source table automatically.
For repeated monthly reporting, consider keeping a structured source table and refreshing a summary or PivotTable before updating the chart. Avoid copying totals from different periods into one chart unless the comparison is intentional. If your purpose is to show change over time rather than the contribution of categories at one point or over one defined period, a line chart may answer the question better.
Frequently Asked Questions
Does Excel sort a Pareto chart automatically?
Excel’s built-in Pareto chart sorts categories by value in descending order. For a manually constructed combo chart, sort the source rows yourself before plotting them.
Can I make a Pareto chart from raw data?
Yes, if the data is arranged so Excel can identify categories and their values. When you have many individual event records, first summarize them into counts by category, such as with a PivotTable, then chart those totals.
Why does my Pareto line use a second axis?
The columns show counts or other measured values, while the line shows a percentage that accumulates toward 100%. A secondary axis lets both scales remain readable. In a manual combo chart, assign the percentage line to the secondary axis.
Can a Pareto chart have a 100% final value if categories are tied?
Yes. Tied categories can appear in either order among themselves, but their values should remain attached to their labels. The final cumulative percentage should still reach 100% when all positive values are included.
Is the 80/20 rule required for a Pareto chart?
No. The chart is useful even when the leading categories do not account for 80% of the total. The 80/20 phrase is a heuristic, not a condition the data must satisfy.
Related
- Accidentally Didn’t Save an Excel File? Recover It
- Access Database vs Excel Spreadsheet: Which to Use?
- How to Protect Cells in Excel and Control Editing Safely
Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.
More in Charts & pivot tables
- Excel Sales Tracking Template with DashboardYes, you can build an Excel sales tracking template with a dashboard using PivotTables, Slicers, and a few…
- Generate an Org Chart from Excel: Steps and TipsYes. In Excel for Microsoft 365, you can create an org chart with Insert > SmartArt > Hierarchy…
- Excel Bar of Pie Chart: How It Works and When to Use ItAn Excel Bar of Pie chart separates smaller categories from a pie chart and displays those categories as…
- Excel Pivot Chart Secondary Axis: The WorkaroundIn Excel for Microsoft 365 on Windows, you generally cannot put a native PivotChart series on a true…
- Creating an Org Chart in Excel: Steps and Options ExplainedTo create an org chart in Excel for Microsoft 365 on Windows, choose Insert > SmartArt > Hierarchy…
- Power Pivot Slicers: How to Add and Connect ThemA Power Pivot slicer is a clickable filter for a PivotTable built from Excel’s Data Model. In Excel…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.