Yes. Excel’s built-in Pareto chart includes a line for cumulative percentage, usually plotted against a percentage scale on the right side of the chart. The bars show each category’s value, while the line shows how the running total adds up as Excel orders categories from largest to smallest.
What percentage does a Pareto chart show?
A Pareto chart combines two different measures. Its columns represent the value for each category, such as the number of complaints, defects, or sales. Its line represents the cumulative percentage: the share of the overall total accounted for by the first category, then the first two, then the first three, and so on.
For example, suppose five types of service issue have counts of 40, 25, 15, 12, and 8. The total is 100. Once the categories are sorted in descending order, the cumulative percentages are 40%, 65%, 80%, 92%, and 100%. The first point means the most frequent issue accounts for 40% of all recorded issues. The second means the two most frequent issues together account for 65%.
This is not the same as showing each category’s individual percentage. The second category’s individual share in this example is 25%, but the Pareto line reaches 65% at that category because it adds the first category’s 40% to the second category’s 25%.
How to insert a Pareto chart in Excel
In Excel for Microsoft 365 on Windows, arrange the data in two columns: a category label and a nonnegative numeric value. Select the data, including the headers, then choose Insert > Insert Statistic Chart > Pareto. Excel sorts the categories by value and creates the columns and cumulative-percentage line.
- Put category names in one column and their counts or other comparable values in the next.
- Select the full range, including the column headings.
- Choose Insert > Insert Statistic Chart > Pareto.
- Check that the bars are in descending order and that the line rises toward 100%.
For the example above, the data could have Issue in A1 and Count in B1, with the five labels in A2:A6 and the values 40, 25, 15, 12, and 8 in B2:B6. The total is 100, so the final cumulative point should be 100%. If your chart shows an unexpected order or the line does not end at 100%, check the source range, values, and category labels.
On Mac, use the Insert tab and look among the statistical chart types for Pareto; the precise ribbon grouping can vary with the Excel update. Excel for the web may show an existing chart but can offer fewer chart-creation or formatting controls. If Pareto is not available in the version you are using, a manually calculated combination chart is a dependable alternative.
How to read the line and axes
The left vertical axis generally measures the bar values, such as number of cases. The right vertical axis measures cumulative percentage, from 0% to 100%. Read a line point using the right axis, not the left one. The category labels along the bottom are normally rearranged from the largest bar to the smallest.
The rising line helps identify how concentrated the total is. In the example, the first two categories contribute 65% of all cases, and the first three contribute 80%. That can help a team decide which categories merit closer investigation. It does not prove why those categories are common, or guarantee that addressing them will reduce the total by the same percentage; the chart summarizes the data, while cause and effect require further investigation.
The familiar 80/20 idea is a heuristic, not a rule Excel imposes. A Pareto chart may show that a few categories account for most of a measured total, but it may also show a gradual distribution with no sharp cutoff. Use the observed cumulative values rather than assuming the first 20% of categories must explain 80% of the results.
Show exact percentages with a custom chart
The built-in Pareto chart supplies a cumulative line, but a helper column is useful when you want to inspect the exact percentages, control the chart series, or add custom labels. First sort the source rows by value from largest to smallest. In this example, assume category names are in A2:A6 and counts are in B2:B6, with 40, 25, 15, 12, and 8 in that order. Enter this in C2:
With values in B2:B6 and the first cumulative result in row 2, use =SUM($B$2:B2)/SUM($B$2:$B$6) in C2, then fill it down through C6. The results are 40%, 65%, 80%, 92%, and 100% when column C is formatted as a percentage.
To chart these values, select the category, count, and cumulative-percentage columns. Choose Insert > Combo Chart, or select an existing chart and choose Chart Design > Change Chart Type > Combo. Set the count series to Clustered Column and the cumulative-percentage series to Line. Turn on Secondary Axis for the percentage series, then confirm the chart. The exact combo-chart dialog can vary slightly between updates.
Format the right-hand axis as Percentage so values such as 0.8 display as 80%. If needed, open the axis formatting pane and set its minimum to 0 and maximum to 1; Excel stores 100% as 1. Add data labels to the line if readers need exact values without estimating from the axis. This custom approach also makes the cumulative percentages visible in cells, where they can be checked or reused.
Individual share versus cumulative percentage
Readers often see a point at 65% and assume the category at that position represents 65% by itself. It does not: the point is the running total through that category. To show both measures, calculate an individual share in another helper column and retain the cumulative column for the line.
With category values in B2:B6, the total in B2:B6, and the first individual-share result in D2, enter =B2/SUM($B$2:$B$6) in D2 and fill it down. In the example, the individual shares are 40%, 25%, 15%, 12%, and 8%. The cumulative series is 40%, 65%, 80%, 92%, and 100%. You can display the individual shares in labels or a table, but avoid putting too many competing series on one chart.
Prepare the data and choose the measure
A useful Pareto chart depends on consistent categories and a meaningful value column. Decide what one row represents and what the values measure before charting. Counts of incidents are different from costs, hours, or rates; combining unlike measures can make the ranking misleading.
- Use one row per category, with a single numeric measure for that category.
- Combine duplicate labels if they refer to the same category, or deliberately keep them separate and explain why.
- Check for blanks, text stored as numbers, and totals accidentally included as if they were another category.
- Choose a suitable time period and population, and state it in the chart title or a note.
- Use nonnegative values that add meaningfully. A chart of counts or costs is usually easier to interpret than one mixing positive and negative values.
Categories matter as much as arithmetic. For instance, separate labels such as Late delivery and Delivery late may split one underlying issue into two bars. Conversely, grouping distinct issues under Other can conceal important differences. If you use an Other category, state what it includes and whether its value is a combined total.
Common problems and how to fix them
If there is no visible percentage line, first check that you inserted a Pareto chart rather than a regular column chart. If you are using a custom combo chart, verify that the cumulative series is included, set to Line, and assigned to the secondary axis. A line can appear nearly flat when it is accidentally plotted against the count axis.
If percentages look like decimals, format the secondary axis or helper cells as Percentage. A value of 0.65 is 65%, not 0.65%. If the line ends below or above 100%, review the selected source range and confirm that the denominator includes exactly the values represented in the chart. A manually entered total that includes extra rows can also cause a mismatch.
If the bars are not in descending order, check whether the chart is a built-in Pareto chart or a manually created column chart. For a custom chart, sort the source rows by the measure before calculating the cumulative percentages. Sorting only the bars after making the helper calculations can disconnect the displayed line from its categories.
Labels can become crowded when there are many categories. Consider narrowing the analysis to a clearly defined period, grouping genuinely minor categories, or using a larger chart. Do not remove categories merely because they make the result less tidy; omissions change the total and can alter the conclusion.
Built-in Pareto chart or custom combination chart?
Use Excel’s built-in Pareto chart when you want a quick ranking with the cumulative-percentage line and do not need elaborate control over the underlying calculations. It is convenient for an initial analysis and automatically orders the categories by value.
Choose a custom combo chart when you need visible formula-driven percentages, specific axis limits, additional labels, or a chart that must follow a particular layout. The trade-off is that you must sort the categories and maintain the cumulative calculations yourself. If the source data changes, check that the chart range and formulas still include the new rows and that the order remains descending.
Frequently Asked Questions
Does an Excel Pareto chart show percentage by default?
Yes. The built-in chart includes a cumulative-percentage line and a percentage scale on the secondary, usually right-hand, axis. The line is cumulative, not the individual percentage for each bar.
How do I add percentage labels to the Pareto line?
For a custom combo chart, select the line series and use Chart Design > Add Chart Element > Data Labels, then format the labels as percentages if necessary. If you need precise control, calculate the cumulative percentages in worksheet cells and use those values for the line.
Why does my Pareto chart not end at 100%?
Check that the chart includes every intended category and that the percentage denominator uses the same set of values. In a custom chart, ensure the cumulative formula reaches the last row and that the final row is included in the chart range.
Can I change the order of categories in a Pareto chart?
A Pareto chart is defined by descending values, so changing the order conflicts with its central purpose. If you need a chosen order, use a regular column chart or a custom chart and make clear that the displayed order is not a Pareto ranking.
Is the 80% point a required cutoff?
No. The chart does not require the line to cross 80% at any particular category. Treat 80% as a possible point for discussion, not a guaranteed threshold or a substitute for deciding what matters in your analysis.
Related
- Excel Number Format Color Codes and Custom Formats
- Excel Number Format Brackets for Negative Numbers Explained
- Excel Number Format for Millions: Codes and Examples
Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.
More in Charts & pivot tables
- Power Pivot Add-In: What It Does and How to Enable ItThe Power Pivot add-in is an Excel tool for building data models: it can connect tables, define relationships,…
- Excel Line Chart Not Showing All Data? How to Fix ItIf an Excel line chart is not showing all your data, first check the chart’s source range and…
- Bar Chart Maker in Excel: Create and Format ChartsExcel has a built-in bar chart maker: select your category labels and values, then choose Insert > Insert…
- Correlation Graph in Excel: Create and Interpret OneTo make a correlation graph in Excel, select two columns of paired numeric data and choose Insert >…
- How to Make a Column Graph in Google Sheets: A GuideTo make a column graph in Google Sheets, select your data, choose Insert > Chart, then select Column…
- Excel Pivot Chart Secondary Axis: The WorkaroundIn Excel for Microsoft 365 on Windows, you generally cannot put a native PivotChart series on a true…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.