To make a dynamic Pareto chart in Excel for Microsoft 365 on Windows, put one category and its count on each row of an Excel Table, add a cumulative-percentage formula, sort the counts from largest to smallest, and build a combo chart with columns for counts and a line on a secondary axis for cumulative percentage. Because the chart uses the Table, it can expand when you add rows; you still need to keep the rows sorted if their values change.
What a Pareto chart shows
A Pareto chart combines two views of the same data: bars show the size of each category, ordered from largest to smallest, and a line shows the running share of the total. It is useful for spotting which causes, product types, complaints, or defects account for a large proportion of the observed total. The line is a cumulative percentage, not a second count series.
For example, suppose a service team records 40 late deliveries, 25 damaged items, 15 billing errors, 12 missing parts, and 8 other complaints. The total is 100. In descending order, the cumulative shares are 40%, 65%, 80%, 92%, and 100%. The chart makes it easy to see that the first three categories account for 80% of the recorded complaints.
The familiar “80/20 rule” is a useful idea, not a guarantee that every dataset will have an 80/20 split. A Pareto chart describes the distribution in the data you collected; it does not prove that one cause produced another or that fixing a category will eliminate the same percentage of problems.
Prepare the data and make it dynamic
Start with a summary table containing one row per category and a numeric count. If your source is a log with many individual records, summarize it first—for example, with a PivotTable—or calculate a count for each category. A Pareto chart needs comparable, nonnegative totals. It is not appropriate for categories that overlap in a way that makes their counts add up to more than the underlying events.
- Enter headings such as Category and Count in two adjacent columns, followed by the category names and counts.
- Select the range and press Ctrl+T on Windows. Confirm that the table has headers, then select OK. On Mac, the equivalent shortcut is usually Command+T; you can also use Insert > Table.
- On the Table Design tab, set the Table Name to
Pareto. Clear table names make structured formulas easier to read. - Add a third table column headed Cum %. Enter the formula below in its first data row; Excel normally fills the calculated column down the Table.
With a Table named Pareto and columns named Category, Count, and Cum %, enter this formula in the first data cell of Cum %:
=IF([@Category]="","",SUM(INDEX(Pareto[Count],1):[@Count])/SUM(Pareto[Count]))
The formula totals the Count values from the first table row through the current row, then divides by the total of the Count column. With counts 40, 25, 15, 12, and 8 in that order, it returns 40%, 65%, 80%, 92%, and 100%. Format the Cum % column as Percentage. If the table is empty or its counts total zero, the percentage calculation needs separate handling; avoid charting a zero-total dataset.
Sort the categories and build the combo chart
Sort the Table by Count, largest to smallest, before creating the chart. Select the filter arrow in the Count heading and choose Sort Largest to Smallest. The cumulative formula recalculates in the displayed order, so the line will match the sorted bars. If you later edit counts, check the sort again: an Excel Table expands with new records, but it does not automatically re-sort itself whenever a value changes.
To create the chart, select the Category, Count, and Cum % columns, including their headings, then choose Insert > Insert Combo Chart > Create Custom Combo Chart. In the dialog, set Count to Clustered Column and Cum % to Line. Select Secondary Axis for Cum %, then choose OK. The category labels should appear along the horizontal axis, with counts on the primary vertical axis and cumulative percentage on the secondary one.
Format the right-hand axis as a percentage. Right-click that axis and choose Format Axis; under Axis Options, set the bounds to 0 and 1 if you want the scale to run from 0% to 100%. If the line seems flat or the two axes have confusing scales, verify that Cum % is assigned to the secondary axis and that its cells are genuine percentage values rather than text.
Since the source is an Excel Table, adding a new category and count at the bottom extends the Table and its calculated formula column. A chart built from the Table generally includes the added row as the source expands. Confirm that it appears in the chart and that the Table remains sorted by Count. If you create the chart from an ordinary fixed cell range instead, new rows may fall outside the chart’s source range.
What “dynamic” means—and what it does not
In this setup, dynamic means that the table and chart can accommodate added rows and that the cumulative percentages recalculate as the table data changes. It does not mean that Excel will automatically identify new categories in a raw event log, aggregate them, reorder every row, and update the chart without any setup or refresh. Those are separate tasks.
For an automatically ranked view, Microsoft 365 also has dynamic-array functions such as SORTBY and FILTER. They can create a separate, formula-driven display from a clean summary list and sort that display by count. However, charts based on spilled ranges can require extra care with chart series references, especially when the spill range changes size. For many workbooks, a Table with a calculated column is simpler to maintain; for a regularly refreshed report, a PivotTable and PivotChart may be more convenient.
If you need the chart to reflect changes in a raw list, decide how that list becomes category totals. A PivotTable can group and count records, but changes to its source usually require a refresh. A Table-based summary updates only when the summary values themselves are updated. Keeping those steps clear prevents the chart from looking current while actually showing stale counts.
Use Excel’s built-in Pareto chart or a custom chart?
Excel includes a statistical Pareto chart. Select a suitable category-and-count range and choose Insert > Insert Statistic Chart > Pareto. This is a fast way to get the standard descending bars and cumulative line without setting up the cumulative-percentage column yourself. The built-in chart is useful when its formatting and behavior meet your needs.
A custom combo chart is preferable when you want an explicit cumulative formula, a specific secondary-axis scale, or control over the series and formatting. It also makes the calculation visible in the worksheet, which helps when someone needs to audit how the cumulative percentages were produced. The built-in chart and the custom combo chart are different ways to visualize Pareto data; neither automatically fixes incomplete or incorrectly grouped source data.
For either option, use an Excel Table when you expect rows to be added. Check the chart after adding data, especially if the chart was made before the source was converted to a Table. Excel for Mac and Excel for the web provide many of the same chart tools, but ribbon labels and some chart-editing controls can differ from Windows. If a particular combo-chart control is unavailable in your version, create or edit the chart in the desktop version of Excel.
Common problems and how to fix them
- The bars are not in descending order: Sort by Count, not by category name. Re-sort after editing counts or adding a category that should rank higher.
- The cumulative line does not finish at 100%: Check that every category is included, the denominator covers the complete Count column, and the chart uses the Cum % column rather than a count column.
- The line is hard to see: Assign Cum % to the secondary axis, format that axis as Percentage, and check the line’s color and markers.
- New categories do not appear: Confirm that they were added inside the Table or directly below it so it can expand. Check the chart’s data source if it still uses a fixed range.
- The percentages exceed 100% or behave oddly: Look for negative values, duplicate categories that should have been combined, or counts that were entered as text. A conventional Pareto chart assumes nonnegative category totals.
- The chart has a blank or misleading category: Remove blank category rows from the summary or leave them outside the Table. A blank category can be treated as a plotted item rather than ignored.
Also check whether the data is a complete period or only a partial extract. A chart of incomplete records can still calculate perfectly while giving a misleading picture of which categories matter most. Use a consistent date range and counting rule before comparing two Pareto charts.
Interpretation, grouping, and practical limits
The cumulative line answers a descriptive question: what share of the recorded total is covered by the first several categories? It does not tell you which action is cheapest, safest, or most effective. A rare category may have unusually serious consequences, while a frequent category may be inexpensive to address. Use the chart to prioritize investigation, then consider impact, effort, and evidence before choosing a response.
Category definitions matter. If “late delivery” and “missed delivery window” describe the same event, decide whether to combine them before calculating the totals. Conversely, combining unrelated issues into “Other” can hide an important pattern. If you keep an Other category, define which records it includes and consider showing its size so readers can judge how much detail is being grouped away.
For a fair comparison between teams or months, use the same category definitions and comparable exposure. For instance, raw complaint counts can rise simply because a team handled more orders. A rate per order may be more informative in that situation, but it answers a different question and should be labeled clearly. Pareto charts organize the measure you provide; they do not choose the right measure for you.
Frequently Asked Questions
Does a Pareto chart in Excel update automatically?
A chart based on an Excel Table can expand when you add rows, and table formulas recalculate. Excel does not automatically keep the categories sorted by count after every edit, and a PivotTable-based source may need to be refreshed.
Can I make a Pareto chart without a formula?
Yes. Select the category and count data and use Insert > Insert Statistic Chart > Pareto. Use a custom combo chart with a cumulative-percentage column when you want more control over the calculation or formatting.
Why does my cumulative percentage not equal 100% on the last row?
Check that the formula includes all rows in the total and that all plotted categories are present. Also verify that the counts are numeric and nonnegative, and that the chart’s final point comes from the last row in descending order.
Should the Pareto bars be sorted before I calculate cumulative percentage?
Yes. Sort counts from largest to smallest first. The cumulative percentage is a running total, so its meaning depends on the row order; sorting afterward changes the correct running values.
Can I use a Pareto chart for percentages instead of counts?
You can chart nonnegative measures such as rates or costs, provided each category uses a consistent measure and the values can be meaningfully added. Label the measure clearly. If the percentages overlap or use different denominators, their sum may not represent a meaningful total.
Related
- Column J Number in Excel: How to Find and Use It Easily
- Excel Column K Number: What It Means and How to Find It
- ICD-10 Z Codes List in Excel: Find, Import, and Check
Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.
More in Charts & pivot tables
- Excel Pivot Table: Hide Columns Without Losing DataIn Excel for Microsoft 365 on Windows, select the worksheet column letter above the PivotTable and choose Home…
- Excel Consolidate Pie Charts: Methods and LimitsTo consolidate data for a pie chart in Excel, combine the category totals into one summary table, select…
- Excel Pivot Table: How to Expand All GroupsTo expand a PivotTable field across all its items in Excel for Microsoft 365 on Windows, right-click an…
- Excel Chart Color Based on Value: How to Set It UpYes. In Excel for Microsoft 365 on Windows, the most reliable way to color chart points according to…
- Dynamic Pie Chart in Excel: Build One That UpdatesA dynamic pie chart in Excel updates when its source data changes. For a chart that grows as…
- Excel Trying to Refresh Pivot Table? Fix It NowIf Excel is trying to refresh a pivot table and nothing happens, or it shows an error, the…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.