To build a Pareto chart in Excel, summarize your categories and counts, sort the counts from largest to smallest, calculate a running cumulative percentage, and plot the counts as columns with the percentage as a line on a secondary axis. In Excel for Microsoft 365 on Windows, a combo chart gives you direct control over the result; Excel also offers a built-in Pareto chart for suitable data.
What a Pareto chart shows
A Pareto chart combines two views of the same data. Its bars show how often each category occurs, ordered from most frequent to least frequent. A line shows the cumulative share of the total as you move across those bars. The first bar represents the largest category; the final point on the line reaches 100%.
This layout helps answer questions such as “Which types of defects account for most of our returns?” or “Which complaint topics make up most customer reports?” It brings both the size of individual categories and their combined contribution into view. A regular column chart can show counts, but it does not make the cumulative share as easy to read.
The chart is associated with the Pareto principle, often summarized as an 80/20 rule. That is a useful prompt to look for a small number of important contributors, not a guarantee that exactly 20% of categories will cause 80% of outcomes. Your data may have a different distribution, and the chart describes the data you collected rather than proving why a problem occurs.
Prepare and sort the source data
For the manual method, begin with a two-column summary table: one column for category names and one for counts. Each category should appear once, and each count should measure the same thing over the same period. For example, do not mix numbers of customer cases with dollar values unless the chart is explicitly meant to compare those different measures.
Suppose a delivery team recorded 100 service issues in a month. A summary might contain late shipment (42), damaged item (25), wrong item (18), billing issue (10), and other (5). Put the category names in A2:A6 and their counts in B2:B6, with headers in row 1. Sort the rows by count in descending order so the largest category is first. On Windows, select the table and use Data > Sort; choose the count column and select Largest to Smallest. Keep the category and count cells together when sorting so labels remain matched to their values.
Check the input before making the chart. Counts should normally be nonnegative, and the total should be greater than zero. Decide how to handle blank categories, spelling variants, and categories that occur only once. For instance, “late delivery” and “late shipment” may need to be combined if they represent the same cause. These decisions affect the chart, so document them when the analysis will inform a business decision.
Calculate the cumulative percentage
Add a third column titled Cumulative %. With categories in A2:A6, counts in B2:B6, and the first cumulative calculation in C2, enter this formula:
With counts in B2:B6 and the first cumulative percentage in C2, use =SUM($B$2:B2)/SUM($B$2:$B$6) and fill it down through C6.
The formula adds the counts from the first row of the sorted list through the current row, then divides by the total. Format C2:C6 as Percentage using Home > Number > Percent Style. In the example, the cumulative percentages are 42%, 67%, 85%, 95%, and 100%. The first point represents 42 of 100 issues; the second represents 42 + 25 = 67 of 100.
Sorting first is important: the cumulative line is most useful when it adds categories in descending order of contribution. If you change counts or add categories later, re-sort the table and extend the formula range as needed. Otherwise, the line may no longer represent a properly ordered Pareto analysis.
Create a combo chart from the summary table
To make the chart, select A1:C6, including the headers. In Excel for Microsoft 365 on Windows, choose Insert > Insert Combo Chart > Create Custom Combo Chart. In the dialog, set the count series to Clustered Column and the Cumulative % series to Line. Select Secondary Axis for the cumulative percentage series, then choose OK. The category names supply the horizontal axis labels.
The secondary axis matters because counts and percentages use different scales. If both series share one axis, the percentage line can appear flattened or the columns can become hard to interpret. The secondary axis lets the bars use the count scale on the left and the cumulative percentage use a percentage scale on the right.
Format the right-hand axis so its minimum is 0 and maximum is 1, then apply Percentage number formatting. To set bounds, right-click that axis, choose Format Axis, and use the Bounds controls in Axis Options. Excel may set a different maximum automatically; setting it to 1 makes the full 0%–100% scale explicit when your percentages are stored as decimal values. Add a descriptive chart title, such as “Service Issues by Type,” and label the axes if readers might not know what the bars or line represent.
On Mac, Excel also provides combo charts, although ribbon placement and dialog wording can vary by release. Look under the Insert tab for the Combo chart option. In Excel for the web, chart options and formatting controls can differ from the Windows desktop app; if a particular combo-chart control is unavailable, open the workbook in desktop Excel to finish formatting the secondary axis.
Use Excel’s built-in Pareto chart
Excel for Microsoft 365 on Windows includes a built-in Pareto chart under Insert > Insert Statistic Chart > Pareto. It is a convenient option when the selected source data is appropriate for Excel’s statistical chart workflow, especially when you have observations recorded one per row and want Excel to group them into categories. Review the generated categories and counts to ensure they match your intended analysis.
For an existing summary table with category names and pre-calculated counts, the manual combo-chart method is often easier to inspect and adjust. It makes the ordering, cumulative-percentage calculation, and axis settings visible in worksheet cells. If you use the built-in chart, check the chart output rather than assuming the input was interpreted as intended. The Pareto chart command may be absent in older Excel editions or differ across platforms, while a manually built combo chart is a broadly useful fallback.
Improve readability and interpretation
A Pareto chart should make the ranking and cumulative contribution easy to read without adding visual clutter. Keep category labels concise, use a chart title that names the measure and time period, and make the cumulative line visually distinct from the bars. If there are many categories, long labels can crowd the horizontal axis. Consider combining genuinely minor categories into “Other,” but state the rule used and avoid hiding a category that matters to the decision.
- Show the count scale on the primary axis and cumulative percentage on the secondary axis.
- Use consistent category definitions and a clearly stated date range.
- Consider data labels for a small number of categories, but remove them if they overlap.
- Keep the order descending by count; do not alphabetize the categories after calculating the cumulative line.
- Use the chart to prioritize investigation, then examine causes and context before choosing a remedy.
The example’s line passes 80% at “wrong item”: 42 + 25 + 18 = 85 issues, or 85% of the total. That tells the team that the first three categories account for most recorded issues in this sample. It does not establish that fixing those categories will necessarily prevent the same proportion of future issues. The categories may differ in cost, severity, or ease of prevention, and the underlying records may be incomplete.
Common problems and how to fix them
If the cumulative line looks almost flat, check whether it is plotted on the secondary axis and whether its axis is formatted as a percentage. Also verify that the cumulative cells contain ratios such as 0.42 rather than values typed as 42 and then formatted as percentages; 42 formatted as a percentage displays as 4,200%.
If the line does not end at 100%, inspect the formula’s total range and confirm it includes every count. Check for blanks, text values, or rows left out of the chart selection. If the result exceeds 100%, make sure the cumulative range does not include extra rows or duplicate counts. A total of zero cannot produce a meaningful cumulative percentage, so correct the source data before charting.
If bars appear in the wrong order, sort the full category-and-count rows by count descending and confirm that the chart references the sorted table. If Excel has treated category names as separate series or placed numbers on the horizontal axis, recreate the chart using the complete table with headers or adjust the series and category labels in Select Data. For unusual layouts, rebuilding from the simple three-column arrangement is often quicker than repairing a chart with incorrect source ranges.
When a Pareto chart is not the right choice
A Pareto chart is suited to ranking categories by a count or another additive measure and showing cumulative contribution. It is not the best choice for showing change over time; a line chart may better show monthly trends. It can also mislead when categories overlap, when the total is not meaningful, or when counts ignore differences in severity. For example, ten minor defects may not carry the same importance as one safety-critical defect.
For comparisons across teams or periods, use consistent definitions and consider whether raw counts should be adjusted for volume. A department processing twice as many orders may naturally have more reported issues. A rate such as issues per 1,000 orders can be more informative for comparison, but calculate that rate consistently and explain its denominator. A Pareto ranking can then be built from comparable measures rather than incomparable totals.
Frequently Asked Questions
Does Excel make a Pareto chart automatically?
Yes. In Excel for Microsoft 365 on Windows, select suitable data and choose Insert > Insert Statistic Chart > Pareto. For a summarized category-and-count table, a manual combo chart gives you clear control over the cumulative calculations and axes.
How do I show the cumulative percentage line?
Calculate a running total divided by the grand total, plot that series as a line, and put it on a secondary axis. Format the axis as a percentage with a 0%–100% range.
Does a Pareto chart always follow the 80/20 rule?
No. The rule is a common shorthand, not a required pattern. Your chart may show that a different share of categories accounts for 80% of the total, or that contributions are spread more evenly.
Can I make a Pareto chart from a pivot table?
You can use a pivot table to summarize categories and counts, then chart a stable summary range with a cumulative-percentage series. Refresh the pivot table when source records change, and recheck the sort order and formula range before relying on the chart.
Why does my cumulative line finish above or below 100%?
Usually the formula or chart range omits rows, includes extra rows, or uses counts that are stored as text. Compare the final cumulative value with the total count and verify that the calculation includes each category exactly once.
Related
- Excel Not Responding? How to Save Your Work
- Excel Not Responding? Recover an Unsaved File
- Excel Nested Group Rows: How to Create and Use Them
Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.
More in Charts & pivot tables
- Excel Trendline Equation in a Cell: How to Calculate ItYes. For a linear trendline, calculate its slope and intercept in worksheet cells, then use those values to…
- How to Rename Series in an Excel Chart: Steps and TipsTo rename a series in an Excel chart, select the chart, go to Chart Design > Select Data,…
- Microsoft Excel Pivot Charts: How to Create and UseA Microsoft Excel PivotChart is a chart linked to a PivotTable, so you can summarize and explore data…
- Excel Pivot Table Dates by Week: Grouping and FixesTo show Excel PivotTable dates by week, place the date field in Rows, right-click a date in the…
- Excel Sparklines Line Chart: How to Add and Format OneIn Excel for Microsoft 365 on Windows, select the data, choose Insert > Sparklines > Line, and specify…
- Excel Waterfall Chart X-Axis Labels: How to Wrap TextYes. In Excel for Microsoft 365 on Windows, the reliable way to wrap text on a waterfall chart’s…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.