Yes—but Excel cannot create a Pareto chart as a native PivotChart. The practical workaround is to build a PivotTable, sort its categories from largest to smallest, calculate a cumulative percentage beside it, and use those results to create a regular combo chart with columns and a line.
What a Pareto chart shows
A Pareto chart combines two views of the same categories. Descending columns show the size of each category, while a line shows the running share of the total as you move from left to right. For example, a report might show which product types account for most returns, or which issue types generate most support tickets.
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 outcome. Treat that as a useful pattern to investigate, not a promise that every dataset will have an 80/20 split. The cumulative line makes the actual distribution visible.
Starting with a PivotTable is useful when the source data contains many records. The table can group and summarize those records by a field such as product, defect, department, or month before you chart the results.
Why a PivotChart cannot be a native Pareto chart
Excel’s built-in Pareto chart is a statistical chart type, available from Insert > Insert Statistic Chart > Pareto when you chart suitable source data. A PivotChart has a different set of supported chart types and does not offer Pareto as a chart choice. It also does not provide the combination of descending columns and a cumulative-percentage line that defines a Pareto chart.
This distinction matters when following instructions that say to make a Pareto PivotChart. Often, the instructions mean a chart built from PivotTable results, not a true PivotChart object. A regular combo chart based on the PivotTable and a helper column can deliver the Pareto appearance, but it does not have all the interactive behavior of a PivotChart.
If you need slicers to control the chart, a regular chart based on PivotTable cells may still reflect changes to those cells, but its source range and helper calculations need to be checked when the PivotTable changes size. If you need the full field-button and PivotChart behavior, use a regular PivotChart instead and accept that it will not be a native Pareto combo.
Prepare and sort the PivotTable
Start with a clean source table: one header row, one record per row, and a category column plus a numeric measure. For example, a returns table could have Product and Return Cost columns. Convert the source range to an Excel Table with Insert > Table if you want new source rows to be included more reliably when you refresh.
- Select a cell in the source data and choose Insert > PivotTable. Confirm the table or range and choose where to place the PivotTable.
- In the PivotTable Fields pane, drag the category field to Rows and the numeric field to Values. If Excel counts records instead of summing amounts, open the value field settings and choose Sum where appropriate.
- Sort the value field from largest to smallest. Right-click a value in the summary and choose Sort > Sort Largest to Smallest. A Pareto chart’s bars should descend from left to right.
- Exclude any Grand Total, subtotal, or blank category from the chart’s category list. Those rows are summaries, not additional categories.
Check that the chosen measure answers the question. Counting incidents produces a different ranking from summing their cost or duration. A category can be frequent but inexpensive, or rare but costly, so choose the value that matches the decision you are trying to inform.
Add a cumulative percentage column
Put a helper column beside the PivotTable’s category and value columns. In the example below, assume the PivotTable has category labels in A6:A10 and descending values in B6:B10, with five categories and no total row included in those ranges. Enter a heading such as Cumulative % in C5.
With the five PivotTable values in B6:B10, enter =SUM($B$6:B6)/SUM($B$6:$B$10) in C6, then fill the formula down through C10. The first result is the first category’s share; the last result is 100% because the numerator has accumulated all five values and the denominator is their total. Format C6:C10 as Percentage.
For a concrete check, suppose the values in B6:B10 are 420, 250, 180, 100, and 50. Their total is 1,000, so the cumulative percentages should be 42%, 67%, 85%, 95%, and 100%. If your last result is not 100%, check that the denominator includes every category and that the PivotTable’s total row was not accidentally included as another value.
This fixed-range example is intentionally simple. If the number of PivotTable categories changes, adjust the formula’s denominator and fill the helper formula for the new rows. Do not include a Grand Total row in either the running values or the denominator when the categories already sum to that total.
Create the Pareto-style combo chart
Select the category labels, summarized values, and cumulative percentages, such as A5:C10 in the example, and insert a combo chart. In Excel for Microsoft 365 on Windows, use Insert > Combo > Create Custom Combo Chart. Set the summary values to Clustered Column, set Cumulative % to Line, and select Secondary Axis for the cumulative percentage series. The category labels should be used for the horizontal axis.
The secondary axis is important because the columns may represent counts, dollars, or another measure while the line represents a fraction from 0 to 1. Format the secondary vertical axis as Percentage and, if appropriate for your data, set its bounds from 0 to 1. This makes the line’s 100% endpoint clear. If the axis instead displays 0.42, 0.67, and similar decimals, change its number format to Percentage.
Give the chart a specific title, such as Returns by Product and Cumulative Share. Add axis titles if the units are not obvious. Keep the bars in descending order and make the line visually distinct. A horizontal 80% reference line can be added for analysis, but it is optional: it should not be confused with a universal threshold or a target that applies to every dataset.
The Windows combo-chart dialog may differ from the chart controls in other Excel versions. On Mac, use the Insert chart options to create a combo chart and look for the series chart-type and secondary-axis controls; labels and dialog layout can differ by release. Excel for the web also has a different interface, and some chart-formatting controls may be limited compared with the Windows desktop app.
Keep the chart useful after refreshes
When you add or change source records, refresh the PivotTable with Data > Refresh All. Then check the category sort, helper formulas, and chart range. A regular chart does not necessarily expand its source range just because a refreshed PivotTable gained more categories. If the number of categories varies, update the chart range or use a deliberately sized helper area that you maintain as the PivotTable grows.
For a filtered PivotTable, the cumulative percentages should normally be calculated from the categories currently displayed. The example formula sums the listed summary cells, so it recalculates from those cells when their values change. However, if a filter hides categories but leaves them represented in a separate helper range, make sure the denominator matches the intended population. State clearly whether the chart represents all records or only the current filter selection.
A regular chart can be based on cells that change when a PivotTable is filtered, but it is not the same as a PivotChart connected directly to the PivotTable. Test the filter behavior and category range with your actual workbook before relying on it for a report. If interactive filtering is central, consider a standard PivotChart for exploration and a separate Pareto-style combo chart for a fixed or carefully managed view.
Common problems and how to fix them
- The bars are not descending: Sort the PivotTable by the summarized value from largest to smallest, then check that the chart uses the same rows in the same order.
- The line ends above or below 100%: Check the cumulative formula’s first and last rows, denominator, blank categories, and Grand Total. The final cumulative share should be 100% when all included categories are counted.
- The line looks almost flat: Put the cumulative series on the secondary axis and format that axis as a percentage. A count scale and a percentage scale should not share one axis.
- The total appears as the largest bar: Remove the Grand Total row from the chart source. A total is not a category and distorts both the bar ranking and cumulative calculation.
- New categories are missing: Refresh the PivotTable, extend the helper formulas, and verify the regular chart’s source range includes the added rows.
- Negative values produce confusing percentages: A conventional Pareto chart is easiest to interpret with nonnegative contributions. Returns, credits, or adjustments can make a running share behave unexpectedly; investigate whether the measure should be separated or defined differently rather than hiding the effect.
When to use Excel’s built-in Pareto chart instead
If the source data is already a simple list of categories and values and you do not need PivotTable summarization or pivot filtering, select the relevant data and use Insert > Insert Statistic Chart > Pareto. Excel creates a Pareto chart from that data without requiring you to build the cumulative helper column manually.
Use the PivotTable-plus-combo-chart approach when you need to aggregate detailed records first, such as totaling defect counts by defect type or return costs by product. Use the built-in chart when the category totals are already prepared and a direct statistical chart is sufficient. In either case, verify the sort order, the data being counted or summed, and the meaning of the denominator.
Frequently Asked Questions
Can I make a Pareto chart directly from a PivotTable?
Not as a native PivotChart Pareto type. Create a PivotTable, add a cumulative percentage helper column, and insert a regular combo chart using columns for the values and a line on a secondary axis.
Can a Pareto chart use a PivotChart slicer?
A native PivotChart can use PivotTable filters and slicers, but it cannot be changed into a Pareto combo chart. A regular combo chart linked to PivotTable output may respond to changed cell values, but its range and helper formulas need checking after filtering or refreshes.
Does the cumulative line have to reach 100%?
Yes, if it includes every category in the stated population and the denominator is the sum of those categories. If it does not, check for omitted rows, an incorrect denominator, or values that were excluded from the chart.
What does the 80% point mean on a Pareto chart?
It identifies how many of the leading categories together account for 80% of the measured total. Some datasets reach that point quickly and others do not; 80% is a reference point, not a rule every chart must satisfy.
Why can’t I see the Pareto option for my PivotChart?
Excel does not offer Pareto in the PivotChart chart-type choices. The built-in Pareto option is a statistical chart for ordinary source data, while the PivotTable workaround uses a regular combo chart.
Related
- Excel Number Format for Millions: Codes and Examples
- Excel Decimal Comma: Change Number Separators
- Excel Number Format to Hide Zeros Without Changing Values
Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.
More in Charts & pivot tables
- Excel Format Chart Area Size Greyed Out? FixesIn Excel for Microsoft 365 on Windows, a greyed-out chart-area size control usually means Excel is not treating…
- Create a Pivot Table in Excel: Steps and Tips That WorkTo create a PivotTable in Excel for Microsoft 365 on Windows, select a cell in a well-organized data…
- Excel Pivot Table Fields in Filters and Rows ExplainedIn Excel for Microsoft 365 on Windows, a PivotTable field normally cannot occupy the Filters and Rows areas…
- Excel Pivot Chart Type Changes With Slicer?No: in Excel for Microsoft 365, a slicer filters the data shown in a PivotChart, but it does…
- Does Excel Have a Gantt Chart Template? How to Use ItYes. Excel has Gantt chart templates you can find through its template gallery or Microsoft Create, although the…
- Excel Pivot Table Row Limit: What You Need to KnowIn Excel for Microsoft 365 on Windows, a PivotTable displayed on a worksheet is limited by the worksheet…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.