To add a Pareto line in Excel, either select your category and count data and choose Insert > Insert Statistic Chart > Pareto, or build a combo chart with a cumulative-percentage series on a secondary axis. The built-in Pareto chart is the quickest option; a custom combo chart gives you more control over sorting, labels, and formatting.
What a Pareto line shows
A Pareto chart combines descending columns with a line showing the cumulative share of the total. The columns represent the size of each category, such as the number of defects caused by each issue. The line answers a different question: how much of the total is accounted for as you move across the categories?
For example, a line that reaches 80% after the third category tells you that the first three categories account for 80% of the measured total. It does not mean every real-world dataset will follow an exact 80/20 split. The familiar 80/20 idea is a rule of thumb, not a condition a chart must satisfy.
The line normally uses a percentage scale on a secondary vertical axis, while the columns use the original units on the primary axis. Without that second scale, a percentage such as 65% may be difficult to compare with counts such as 40 or 25. A clear axis title and labels help readers distinguish the two measures.
Choose the built-in chart or a custom combo chart
Use Excel’s built-in Pareto chart when you have a straightforward list of categories and values and want Excel to arrange the categories from largest to smallest and calculate the cumulative line. It is usually the fastest route for a one-off analysis.
Choose a custom combo chart when you need to keep a particular category order, use a prepared cumulative-percentage calculation, add another series, or control the chart more precisely. The trade-off is that you must sort the data correctly and maintain the cumulative formula yourself. If your chart needs to update as source data changes, a formula-based table can make the custom approach easier to manage.
- Choose the built-in chart for a quick Pareto view of one category-and-value dataset.
- Choose a custom combo chart when presentation requirements or data handling call for extra flexibility.
Insert Excel’s built-in Pareto chart
In Excel for Microsoft 365 on Windows, arrange category names in one column and their nonnegative counts or values in the adjacent column. Include a heading for each column, and avoid blank rows within the data. Then:
- Select the category and value cells, including their headings.
- Choose Insert > Insert Statistic Chart > Pareto.
- Check that Excel has treated the first column as category labels and the second as values. Add or edit the chart title and axis titles as needed.
The built-in chart sorts the categories by value in descending order and draws the cumulative-percentage line. This is useful because the largest contributors appear first, which is the usual convention for Pareto analysis. If you later edit the source values, the chart can reflect those changes, though you should check the category order and labels after substantial edits.
Ribbon names and available chart options can vary between Excel versions and platforms. If you do not see the Pareto command in Excel for the web or your desktop build, use the custom combo-chart method below. On Mac, look for Pareto among the statistical chart options on the Insert tab; the exact ribbon layout may differ from Windows.
Build a custom Pareto line with a formula
A custom chart needs a cumulative percentage for each category. First sort the rows by value from largest to smallest. In this example, categories are in A2:A6 and counts are in B2:B6; the headings are in row 1. If the rows are not in descending order, the calculation will still work, but the line will accumulate in the wrong order for a conventional Pareto chart.
With category labels in A2:A6, values in B2:B6, and the largest value at the top, enter this formula in C2:
=SUM($B$2:B2)/SUM($B$2:$B$6)
Copy C2 down through C6, then format C2:C6 as Percentage using Home > Number or the percentage button on the ribbon. The expanding first range adds each value reached so far, while the fixed denominator keeps the total consistent. The final cumulative result should be 100% if all included values are numeric and the total is not zero.
To chart the result, select A1:C6 and insert a clustered column chart. Select the chart and choose Chart Design > Change Chart Type > Combo. Set the count series to Clustered Column, set the cumulative-percentage series to Line, and select Secondary Axis for the cumulative series. Confirm the change, then check that the right-hand axis is scaled as a percentage. If Excel does not assign the series correctly, use the combo dialog to set each series explicitly.
Format the line and axes for readability
A Pareto chart is most useful when the two scales are obvious. The left vertical axis should describe the original measure, such as number of returns or minutes of downtime. The right vertical axis should describe cumulative percentage. In a custom chart, right-click the secondary axis and choose Format Axis; set its number format to Percentage if Excel displays decimal values.
Set the secondary axis minimum to 0 and maximum to 1 when the underlying percentages are stored as decimals and you want a 0%–100% scale. Excel may choose a suitable scale automatically, but checking it prevents a truncated or confusing line. Add axis titles using Chart Design > Add Chart Element > Axis Titles, where that command is available in your version.
- Use a distinct line color and markers so the cumulative series is easy to follow.
- Keep category labels readable; rotate or shorten them rather than shrinking the chart text excessively.
- Use data labels selectively. Labeling every bar and every point can make a crowded chart harder to interpret.
- Include units in the column-axis title, especially when values are not simple counts.
Worked example: turn counts into a cumulative line
Suppose the sorted counts are 40, 25, 15, 12, and 8, for a total of 100. With those values in B2:B6 and categories in A2:A6, the formula in C2 and its copies down the column return 40%, 65%, 80%, 92%, and 100%. The first two categories account for 65% of the total; the first three account for 80%.
On the chart, the first column has height 40 and the first line point is 40%. At the third category, the column height is 15, while the cumulative line has risen to 80%. Those are not competing measurements: the bar shows that category’s individual count, and the line shows the combined share from the first category through that one. This distinction is why the secondary percentage axis matters.
For meaningful results, make sure the categories are mutually understandable and the values use the same measure and time period. Mixing defect counts with repair costs, for example, does not produce a useful single total unless you deliberately convert them to a common measure.
Common problems and how to fix them
- The line looks flat or too low: It may be plotted on the count axis. Edit the combo chart and assign the cumulative series to the secondary axis.
- The line ends below 100%: Check that the chart includes every category and that the denominator in the formula covers all values. Look for text stored in place of numbers, excluded rows, or a formula copied only partway down.
- The categories are in an unexpected order: The built-in chart sorts by value, while a custom chart follows the source-row order. Sort the source values largest to smallest before calculating the custom cumulative series.
- Percentages display as 0.4 rather than 40%: Apply Percentage number formatting to the cumulative cells or the secondary axis. The formula returns a decimal fraction, which Excel formats as a percentage.
- The last point is not 100%: Confirm that the values are all included and that the denominator is the sum of the same range being accumulated. If the total is zero, a percentage calculation is not meaningful.
When using a table or a changing range, also check whether new rows are included in the chart and in the formula denominator. A chart can look polished but still be misleading if its source range omits recent categories.
Built-in Pareto chart versus a manual chart
The built-in chart handles sorting and cumulative percentages for you, so it reduces setup and formula maintenance. It is a good fit when the chart’s standard behavior matches the question you need to answer. A custom combo chart exposes the calculation and gives you greater control, but it also makes sorting and range management your responsibility.
Neither option is automatically better for every dataset. If category order matters for a reason other than contribution size, a conventional Pareto chart may not be the right presentation; a normal column chart could be clearer. If you want to identify a threshold such as 80%, you can explain that point in a note or add a separate reference series, but the cumulative line itself does not prove that a particular cutoff is operationally correct.
Frequently Asked Questions
Can I add a Pareto line to an existing Excel chart?
Yes. Add a cumulative-percentage column to the source data, then use Chart Design > Change Chart Type > Combo to set that series to Line on the Secondary Axis. If the chart is already a suitable column chart, you do not need to rebuild it from scratch.
Does Excel automatically sort a Pareto chart?
Excel’s built-in Pareto chart sorts categories by value in descending order. A manually created combo chart generally follows the order of the source rows, so sort those rows before calculating cumulative percentages.
Why does my Pareto line not reach 100%?
Check that all categories are included, the cumulative formula is filled through the last row, and its denominator covers the full value range. Also check for text values or omitted categories.
Can a Pareto chart use percentages instead of counts?
It can use numeric values that represent percentages, but be consistent about what each value means. If the values already sum to 100%, a cumulative line may reach 100% by the final category; label the columns clearly so readers know whether they show percentages, counts, or another measure.
Is the 80/20 rule required for a Pareto chart?
No. A Pareto chart shows categories in descending order and their cumulative share. The data may show a different distribution, and the chart remains useful even when no category boundary lands at exactly 80%.
Related
- Excel Not Responding and Not in Task Manager
- Excel Not Responding? How to Save Your Work
- Excel Not Responding? Recover an Unsaved File
Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.
More in Charts & pivot tables
- Does Excel Have an Org Chart? How to Create OneYes. Microsoft Excel for Microsoft 365 includes an org chart option in SmartArt: select Insert > SmartArt >…
- Balance Graph in Accounting: Meaning and How to Read OneA balance graph in accounting is a chart that shows how an account balance changes over time, such…
- Free Pareto Chart Template in Excel: Build Your OwnYes. You can make a reusable Pareto chart template in Excel for Microsoft 365 by listing categories and…
- Diverging Stacked Bar Chart in Excel: How to Make OneYes. In Excel for Microsoft 365 on Windows, make a diverging stacked bar chart by splitting neutral responses…
- Chart IF Formula Examples in ExcelYes. In Excel, use an IF formula to create chart-ready values in helper columns, then select those values…
- Activate Power Pivot in Excel: Windows Setup GuideTo activate Power Pivot in Excel for Microsoft 365 on Windows, select File > Options > Add-ins, choose…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.