Excel Pareto Chart from PivotTable: How to Build One

Charts & pivot tables · Updated November 19, 2026

Excel for Microsoft 365 on Windows cannot make a true Pareto chart directly as a PivotChart. Instead, summarize and sort your data in a PivotTable, link or copy its category totals into a regular worksheet range, calculate cumulative percentages, and build a combo chart with descending columns and a line on a secondary axis.

Why a PivotTable needs a separate chart range

A Pareto chart combines two views of the same categories: bars ranked from largest to smallest and a cumulative-percentage line that rises from 0% to 100%. The line helps show how much of the total is accounted for by the highest-impact categories. A common reference point is 80%, although real data does not have to follow an exact 80/20 split.

PivotTables are useful for producing the category totals, especially when the source data contains many records or needs filtering. However, Excel does not offer the Pareto statistical chart type as a PivotChart type. PivotCharts also have restrictions on chart combinations that matter here. A regular chart built from a normal cell range gives you control over both the column series and the cumulative line.

The separate range acts as a small chart-ready summary. It can be on the same sheet or a dedicated chart-data sheet. If you link it to PivotTable cells, refreshed totals can flow through; if you paste values, you will need to replace them after refreshing the PivotTable.

Prepare and summarize the source data

Start with a rectangular source table that has one header row, no blank header names, and one record per row. For example, a service log might have columns for Issue, Date, Region, and Case ID. Use a numeric measure such as cost, units, or cases when that is what you want the bars to represent. Decide what “impact” means before making the chart: counting cases answers a different question from summing repair costs.

  1. Click a cell in the source data. If it is not already an Excel table, press Ctrl+T on Windows and confirm that the table has headers. Excel tables generally make it easier to include new records in a later PivotTable refresh.
  2. Choose Insert > PivotTable, confirm the table or range, and choose where to place the PivotTable.
  3. In the PivotTable Fields pane, drag the category field, such as Issue, to Rows. Drag the appropriate numeric field to Values. If you want a frequency chart, use a field with one nonblank value per record and check that the summary uses Count rather than Sum.
  4. If needed, open the Values field settings and choose the intended calculation, such as Sum or Count. Use a clear value-field name so it is easy to identify later.

For example, if each row is one customer complaint, putting Issue in Rows and Case ID in Values with Count selected gives complaints per issue. If each row contains a defect cost, summing Cost instead measures financial impact. Do not use a count of a field that can be blank unless excluding those records is intentional.

Sort categories before building the chart

Sort the PivotTable values from largest to smallest. Click a value in the Values column and use right-click > Sort > Sort Largest to Smallest, or use the sort options on the PivotTable’s Row Labels drop-down. The exact label can vary with the field layout, but the sort must be based on the summarized value, not alphabetically on the category name.

Check the result before moving on. The largest category should be first, and the final data row should be the smallest included category. Do not include the PivotTable Grand Total as if it were another category: doing so counts the total again and makes the cumulative line incorrect. If filters or slicers are part of the analysis, note which selections are active because they change both the totals and the chart’s conclusion.

Create a regular chart-data range

On a normal worksheet range, set up three columns: Category, Total, and Cumulative %. Copy the sorted category names and values from the PivotTable into the first two columns, leaving out blank rows and Grand Total. You can paste values for a quick, static chart. For a chart that should reflect refreshed PivotTable values, use cell references to the corresponding PivotTable cells instead; keep those references outside the PivotTable so a refresh is less likely to overwrite the helper range.

Assume the chart-ready range has category names in A2:A6 and descending totals in B2:B6. Enter this formula in C2, then fill it down through C6:

=SUM($B$2:B2)/SUM($B$2:$B$6)

With totals of 42, 30, 18, 7, and 3 in B2:B6, the total is 100. The formula returns 42% in C2, then 72%, 90%, 97%, and 100% down to C6. Format C2:C6 as Percentage. The expanding first range in the formula adds the current row to all earlier rows, while the fixed denominator remains the grand sum of the five categories.

If your number of categories changes, adjust the formula’s denominator and the chart range to include the new rows. A formula copied too far can also include blank categories, while a range that stops too soon omits them. For a repeatable report, leave space for expected growth, verify the helper range after each refresh, or use a carefully maintained chart-data table that is updated alongside the PivotTable.

Build the Pareto-style combo chart

  1. Select the three columns of the regular chart-data range, including their headers.
  2. Choose Insert > Insert Combo Chart and select the option for a clustered column and line on a secondary axis. If that preset is unavailable in the menu, choose the custom combo option and set Total to Clustered Column and Cumulative % to Line.
  3. Set the Cumulative % series to use the secondary axis. The totals and percentages use different scales, so plotting both on the same axis would make the line hard to interpret.
  4. Click OK. Confirm that the columns are in descending order and that the line climbs to 100% at the last category.

To make the right axis read clearly, click it and open Format Axis. Under Axis Options, set the minimum to 0 and maximum to 1 if Excel has not already done so, then set the number format to Percentage. The chart-data values are stored as decimal fractions, so 1 represents 100%. Add a descriptive chart title, such as “Complaints by issue: cumulative share,” and label the axes when the units are not obvious.

On Windows, Ctrl+T creates an Excel table and Ctrl+Z undoes an ordinary worksheet edit. On Mac, use Command+T to create a table; menu names and chart controls can differ slightly from Windows, so use the Insert tab and the chart-type or Format Chart Area/Format Axis panes available in that Excel version. Excel for the web has more limited chart editing controls than the desktop app; if a combo option or axis setting is missing, finish chart formatting in Excel for Windows or Mac.

Keep the chart accurate after a refresh

When the source records change, refresh the PivotTable using PivotTable Analyze > Refresh, or right-click the PivotTable and choose Refresh. Then inspect the helper range and chart. A regular chart linked to fixed worksheet cells will update when those cells’ values change, but it will not necessarily expand automatically when a refresh adds new category rows. Similarly, a new sort order in the PivotTable does not help if the helper range contains pasted values that were not replaced.

  • Confirm that the PivotTable’s source includes newly added records. An Excel table as the source can help it expand, but a refresh is still required.
  • Check that the sort remains largest to smallest after filtering or refreshing.
  • Make sure the helper range still excludes the Grand Total and includes every intended category.
  • Check the last cumulative percentage. It should be 100% when all included categories contribute to the denominator.

If you want interactive filtering, place slicers or report filters on the PivotTable and link the helper cells to its displayed results. Test the chart after changing a filter. A filtered-out category should not remain in a pasted summary, and the cumulative calculation should use the filtered totals rather than an unrelated, unfiltered denominator.

Common problems and what they mean

The cumulative line is flat or on the wrong scale: confirm that the percentage series is assigned to the secondary axis and that its values are decimals such as 0.42, not text like “42%”. Format the axis as Percentage; do not multiply the formula result by 100 and then apply percentage formatting, which would display values one hundred times too large.

The line finishes above or below 100%: verify that the denominator covers exactly the same categories shown in the chart and that Grand Total is excluded. If negative values are present, the usual Pareto interpretation becomes less straightforward: cumulative shares can decrease or behave unexpectedly, so investigate whether a nonnegative measure is more appropriate.

The bars are not descending: sort by the summarized value, not by the category label. Also make sure the helper range follows that sorted order. The chart does not automatically know that category ranking is part of the analysis.

The built-in Pareto chart is not the same thing: Excel’s statistical Pareto chart is designed for raw observations, where it groups and counts occurrences. A PivotTable may already contain pre-aggregated totals or sums. Treating those summary rows as raw observations can produce a different result from the one you intend; a combo chart from the summarized range makes the calculation explicit.

When this approach is useful—and its limits

A PivotTable-based Pareto chart is especially useful when the source has many records, categories need filtering, or the measure must be calculated by region, month, product, or another field. The PivotTable handles the aggregation; the helper range and regular chart handle the ranking and cumulative display. This separation also makes it easier to audit how each bar and percentage was calculated.

Interpret the 80% point as a decision aid, not a rule that guarantees a small number of causes explains most of the result. In the example, the first three categories account for 90% of cases, so they may be sensible starting points for investigation. The chart alone does not explain why those issues occur or prove that fixing them will reduce the total by the same percentage. Check definitions, data quality, time period, and operational context before acting on the pattern.

Frequently Asked Questions

Can I create a Pareto chart directly from a PivotTable?
No, not as a native Pareto PivotChart in Excel for Microsoft 365. Use the PivotTable for the summary, then create a regular combo chart from a separate range containing the sorted totals and cumulative percentages.

Should I use Count or Sum in the PivotTable?
Use Count when each record represents one event and you want the number of events in each category. Use Sum when a numeric measure, such as cost or units, represents the impact you want to rank. Choose the calculation that matches the question.

Does the chart update when I refresh the PivotTable?
It updates if its chart-data cells are linked to the PivotTable and the chart range still covers those cells. Pasted values do not update, and a fixed chart range may omit categories added by a refresh. Check the helper range after refreshing.

Why does my cumulative percentage not end at 100%?
Usually the formula denominator and chart categories do not match, or the Grand Total was included as a category. Confirm that the denominator sums all and only the categories represented in the chart.

Is a Pareto chart the same as a PivotChart?
No. A PivotChart is tied to a PivotTable and offers chart types supported for PivotCharts. A Pareto-style chart made from PivotTable results is a regular chart, typically a clustered-column and line combo with the percentage line on a secondary axis.

Related

Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.

More in Charts & pivot tables

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.