To make a Pareto chart in Excel for Microsoft 365 on Windows, select a column of raw category observations and choose Insert > Insert Statistic Chart > Pareto. If your data is already summarized as category names and counts, sort the counts from largest to smallest, calculate a running percentage, and build a combo chart with columns and a line on a secondary axis.
What a Pareto chart shows
A Pareto chart combines two views of the same data. Its columns show categories in descending order of frequency, cost, or another measured value. A line shows the cumulative percentage as you move across those columns. This makes it easier to see which categories account for the largest share of a total.
For example, a service team might count customer complaints by issue. A Pareto chart can show whether a few issues account for most complaints, or whether the counts are spread more evenly. The chart helps prioritize investigation; by itself, it does not explain why an issue occurs or prove that fixing one category will resolve the others.
The name is associated with the Pareto principle, often summarized as an 80/20 rule. That phrase is a useful reminder to look for a concentration of impact, not a guarantee that exactly 20% of categories will produce 80% of the result. Your data may have a different pattern.
Choose the right method for your data
Excel offers a built-in Pareto chart under its statistical charts. It is intended for a list of individual observations: for example, one row per returned product, with each row containing a defect type. Excel counts the occurrences and orders the categories for the chart.
A summary table is different. It already has one row per category and a count or value beside it, such as “Packaging” and 40. For that layout, a regular combo chart is usually the clearer method. It lets you control the category totals and cumulative-percentage line directly, and avoids accidentally treating summary numbers as a fresh set of observations.
- Use the built-in Pareto chart when you have one column of raw category labels that Excel can count.
- Use a combo chart when you already have category totals, need to chart costs or other weighted values, or want direct control over sorting and calculations.
Make a Pareto chart from raw observations
Put a clear heading above the observations, then place one category value in each row. A simple list could contain “Late delivery,” “Wrong item,” and “Damaged item,” with repeated labels wherever those issues occurred. Avoid including a grand-total row in the observation list, because it is not another category to count.
- Select the heading and the nonblank observation cells in the single data column.
- On the ribbon, choose Insert > Insert Statistic Chart > Pareto. If the chart is not visible in the menu, open the chart options and look under Histogram or All Charts, then select Pareto.
- Check that the columns represent the categories and that they appear from most frequent to least frequent. The cumulative-percentage line should rise from the first category toward 100% at the last.
- Add a descriptive chart title, such as “Customer complaints by issue,” and check that the category labels are readable.
The built-in chart updates when its source data changes, but it may not provide the same calculation and formatting control as a chart built from a summary table. If the result does not match your intended categories, or if you need to show values other than simple counts, use the manual combo-chart method instead.
Build a combo chart from category totals
Suppose a sorted table has defect categories in A2:A6 and counts in B2:B6. Use this example: Packaging 40, Incorrect item 25, Damage 15, Missing part 10, and Other 10. Enter the headings “Category” and “Count” in A1 and B1, and sort the complete table by Count from largest to smallest. Sorting whole rows keeps each label with its count.
Add “Cumulative %” in C1. With the category names in A2:A6, counts in B2:B6, and the cumulative percentage heading in C1, enter this 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%, 65%, 80%, 90%, and 100%, because the counts total 100.
Select A1:C6, then choose Insert > Insert Combo Chart > Create Custom Combo Chart. In the chart setup, assign the Count series to a clustered column chart and the Cumulative % series to a line chart. Set the Cumulative % series to use the secondary axis, then confirm the chart. The category labels provide the horizontal axis; if Excel assigns them incorrectly, edit the chart’s data selection so column A supplies the category labels.
Format the secondary vertical axis as a percentage and set its scale from 0% to 100% if needed. Add a title and, if the legend is unclear, rename the series headings. This creates the familiar Pareto form: descending count columns with a cumulative-percentage line. The running percentage calculation assumes the categories are sorted from largest count to smallest; if the rows are reordered, sort them again before interpreting the chart.
Format the chart for easy reading
A useful Pareto chart should make the ranking and the cumulative share obvious without visual clutter. Give it a title that says what was counted and, where useful, the time period. “Support tickets by reason, October” is more informative than “Pareto chart.” Use a vertical axis title such as “Number of tickets” when the unit is not self-evident.
- Keep category names short enough to read. If labels are long, widen the chart, reduce the font slightly, or shorten labels in the source table without changing their meaning.
- Make the line visually distinct from the columns, and ensure it uses the percentage axis rather than the count axis.
- Use a consistent counting period and definition. Comparing categories gathered over different time spans can make the ranking misleading.
- Show the data source or reporting period nearby if the chart will be copied into a presentation or report.
Excel’s chart design and formatting options can vary somewhat by build. On Mac, use the Insert tab and look for the statistical chart or combo-chart options; the labels and dialog arrangement may differ from Windows. Excel for the web may have a more limited set of chart controls in some workbooks. If a built-in Pareto or custom combo option is missing, calculate the cumulative percentages in cells and use a column chart plus a line series where the web version permits combo charts, or open the workbook in desktop Excel.
Interpret the ranking without overclaiming
The first few columns and the point where the cumulative line reaches a chosen share can help identify categories for follow-up. In the example, the first three categories account for 80% of the recorded defects. That makes them reasonable candidates for closer investigation, but it does not establish that they have the same causes, costs, or ease of correction.
Counts are not always the best measure of impact. A frequent low-cost issue could matter less than a rare safety-related or expensive failure. If prioritization should reflect cost, hours, severity, or another measure, summarize that measure by category and chart it instead of raw counts. State the unit clearly so readers do not mistake a cost-based Pareto chart for a frequency chart.
Also consider the denominator. A category can rise in count simply because overall activity increased. When comparing periods, it may be useful to examine rates per order, customer, or operating hour as well as totals. A Pareto chart ranks the values you supply; it does not normalize them or account for changes in exposure.
Common problems and fixes
- The chart is in the wrong order: For a manual chart, sort the entire category-and-value table by the measured value, largest to smallest. Do not sort just the count column, or labels will no longer match their data.
- The line does not finish at 100%: Check that the cumulative formula includes every category and that the denominator sums the same complete range. Look for omitted rows, blank categories, or values stored as text.
- The line is almost flat or appears at the bottom: It may be plotted on the count axis. In the combo-chart settings, make the cumulative series a line on the secondary axis, then format that axis as a percentage.
- There are duplicate-looking categories: Excel may count “Late delivery” and “Late delivery ” separately if one label has a trailing space, or treat spelling variants as different categories. Clean the source labels before making the chart.
- The chart includes a total as a category: Remove grand totals from the source observations or category table. A total is not an individual observation and will distort the ranking.
Frequently Asked Questions
Can I make a Pareto chart from a table that already has counts?
Yes. Sort the summary table by count in descending order, calculate a cumulative percentage column, and use a combo chart with columns for counts and a line on a secondary percentage axis. This gives you control over how the summarized values are represented.
Why can’t I find Pareto under Excel’s chart options?
Make sure you are using desktop Excel and check the Insert tab’s statistical chart options or the full chart list. The available controls can vary by Excel version and platform. A combo chart built from sorted totals and a cumulative-percentage calculation is a practical alternative.
Does a Pareto chart always show an 80/20 split?
No. The 80/20 phrase is a rough principle, not a rule built into Excel. The chart displays the distribution in your data; the cumulative line may reach 80% after one category, several categories, or not until near the end.
Can the chart show costs instead of counts?
Yes. Summarize the cost by category, sort those totals from highest to lowest, and calculate each category’s cumulative share of total cost. Label the axis and chart clearly so readers know the bars represent money rather than occurrences.
How do I update a Pareto chart when new data arrives?
Add new observations within the chart’s source range or extend the summary table and its formulas, then check that the chart includes the added rows. A formatted Excel Table can make expanding source ranges more manageable, but verify the chart and cumulative-percentage formula after adding categories.
Related
- Excel Unhide Shortcut: Reveal Hidden Rows and Columns
- How to Unhide Rows in Excel With a Shortcut
- Unhide Columns in Excel: Keyboard Shortcuts and Steps
Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.
More in Charts & pivot tables
- Power Pivot Date Table: Setup, Marking, and Best PracticesA Power Pivot date table is a dedicated table with one row for every date in the reporting…
- 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…
- How to Delete Sparklines in Excel Without Losing DataTo delete a sparkline in Excel for Microsoft 365 on Windows, select the cell or cells containing the…
- Add a Trendline in Excel for the Web: Step-by-StepTo add a trendline in Excel for the web, select a supported chart, open the Chart tab, choose…
- Bubble Chart in Excel: Create, Format, and TroubleshootTo make a bubble chart in Excel for Microsoft 365 on Windows, arrange your data in three numeric…
- Can You Rotate a Pie Chart in Excel? Steps and TipsYes. In Excel for Microsoft 365 on Windows, select the pie chart, right-click a slice, choose Format Data…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.