To make an automatic Pareto chart in Excel for Microsoft 365 on Windows, select a single column of raw category observations, then choose Insert > Insert Statistic Chart > Pareto. Excel counts repeated categories, sorts them from most to least frequent, and adds a cumulative-percentage line; if your data is already summarized as category-and-count pairs, use a combo chart instead.
What an automatic Pareto chart does
A Pareto chart combines descending columns with a line showing cumulative contribution. The tallest column represents the most frequent or largest category; the line shows how much of the total is accounted for as you move across the categories. It is useful for prioritizing defect types, customer complaints, support requests, causes of delays, or other categories measured on a comparable scale.
In Excel’s built-in chart, the automatic part is the counting, sorting, and cumulative-percentage calculation. It does not determine whether categories are meaningful, fix inconsistent labels, or decide what action to take. The chart is only as trustworthy as the source data and the measure being counted.
A Pareto chart is related to the 80/20 rule, but the chart does not have to show an exact 80/20 split. That rule is a useful general observation, not a requirement or a promise. Your data may show that a few categories account for most of the total, or it may show a more even distribution.
Use Excel’s built-in Pareto chart with raw observations
The built-in chart is usually the quickest option when each row records one occurrence. For example, if a quality log has one defect type per row, Excel can count how often each type appears. Put a clear heading above the data and keep the observations together in one column. Remove blank rows within the list and check that the same category is spelled consistently: Damaged, damaged, and Damaged item may otherwise be treated as separate labels.
- Select the heading and the raw category values in the single column.
- On the ribbon, choose Insert > Insert Statistic Chart > Pareto. In some ribbon layouts, Insert Statistic Chart appears as a chart dropdown in the Charts group.
- Review the resulting columns and cumulative line. Excel sorts categories by frequency rather than preserving the order in the source list.
- Add a descriptive chart title and, if useful, axis titles from Chart Design > Add Chart Element.
The chart is a statistical chart type, not simply a regular column chart with a line drawn over it. Excel calculates the frequencies from the observations and creates the cumulative percentage. When source values change, the chart can update to reflect those changes. Adding new source rows is a separate issue: whether the chart includes them depends on the source range and how it was set up.
Why summarized counts need a different method
Many workbooks already contain a summary such as Category in column A and Count in column B. For instance, a report may say Setup, 40; Late delivery, 25; Damaged, 15; Wrong item, 10; and Missing parts, 10. Those numbers already represent category totals. Selecting the two-column summary as if it were a list of raw observations does not tell Excel to use the count column as the frequency for each label. The built-in Pareto chart is designed to count observations in a list, so a two-column summary is better handled with a combo chart.
A combo chart lets the counts appear as columns and the cumulative percentage appear as a line on a secondary axis. For a fixed list, sort the counts from largest to smallest, calculate a running total, divide it by the grand total, and plot those three columns. The example totals 100, so its cumulative percentages are 40%, 65%, 80%, 90%, and 100%.
Build a formula-driven summary that sorts categories
If categories or counts may change, keep the source in an Excel Table. Select the source range and choose Insert > Table; confirm that the table has headers. In Table Design, give it a clear name such as Defects, and use the column headers Category and Count. A table expands its structured references when you add rows directly below it, which makes formulas easier to maintain. The following formulas assume positive numeric counts and a table named Defects with those two columns.
With Defects[Category] and Defects[Count] as the source columns, enter this in A2 of a blank summary area to list unique categories in descending order of their summed counts:
=SORTBY(UNIQUE(Defects[Category]),SUMIF(Defects[Category],UNIQUE(Defects[Category]),Defects[Count]),-1)
With the category list spilling from A2, enter this in B2 to total the count for each listed category:
=SUMIF(Defects[Category],A2#,Defects[Count])
With the totals spilling from B2, enter this in C2 to calculate the running cumulative percentage:
=SCAN(0,B2#,LAMBDA(acc,item,acc+item))/SUM(B2#)
These dynamic-array formulas require Microsoft 365. Format column C as Percentage. For the example counts 40, 25, 15, 10, and 10, the formulas return the descending category order, the same totals, and cumulative percentages of 40%, 65%, 80%, 90%, and 100%. The values in the last percentage should reach 100% when the counts are positive and all categories are included.
Keep the spill formulas outside the source Table; Excel does not allow a spill range to expand inside a table body. If your category names contain extra spaces or inconsistent spellings, clean the source before relying on a unique-category list. If the grand total is zero, a cumulative-percentage formula would divide by zero, so use meaningful positive counts or handle that case separately.
Create the combo chart from the summary
Once the summary has columns for Category, Count, and Cumulative percentage, select the headers and the output rows. For the five-category example, that is A1:C6 if the headers are in row 1. Choose Insert > Insert Combo Chart > Create Custom Combo Chart. Set Count to Clustered Column and Cumulative percentage to Line, then select Secondary Axis for the percentage series. Click OK.
The secondary axis matters because counts and percentages use different scales. Without it, a percentage line can look flat beside counts that reach 40, or the chart can become difficult to interpret. Format the secondary vertical axis as percentages and, if appropriate for the data, set its bounds from 0 to 1 (equivalent to 0% to 100%). Check that the categories are ordered from largest count to smallest; a Pareto chart is misleading if the bars are left in an arbitrary order.
When a new category appears, the formulas may spill an extra row, but a chart’s data source does not always extend to that new row automatically. Check the plotted categories after adding data. If needed, update the chart data range or use a dynamic chart source. A standard chart based on a Table can expand with the table, but formula spill ranges and statistical chart types do not behave identically in every workbook setup.
Keeping the chart accurate and useful
Set up the source so that the measure is clear. A count of incidents answers a different question from total cost, total hours, or severity-weighted points. If the purpose is to prioritize cost, use cost totals rather than the number of incidents. Make sure each row is counted once unless repeated records are intentional, and decide how to treat missing or uncategorized values.
- Use consistent labels: standardize spelling, capitalization, and leading or trailing spaces before counting.
- Choose a relevant time period: mixing several years of records may hide a recent change in the main causes.
- Show the measure and period: a title such as Defects by Type, January to March is more informative than Pareto Chart.
- Keep the line interpretable: label it as cumulative percentage and format its axis as a percentage.
- Review ties and small categories: tied counts can appear in either order, and a long tail of rare categories may make labels crowded.
Excel does not decide that categories above the 80% point should be removed, nor does it identify a root cause. The cumulative line helps show how quickly the total builds, but a category’s frequency alone does not establish why it occurs or which fix is safest or most cost-effective.
Common problems and practical fixes
If the chart shows unexpected categories, inspect the source for spelling variants, spaces, blank cells, or values that should have been grouped. If it shows only one category, check that the selected cells contain the actual observations rather than a heading or a single summary total. If the cumulative line does not finish at 100%, confirm that the totals and the plotted categories cover the same complete set and that the denominator is the full total.
If the chart does not include new records, first check the chart’s data range. For raw observations, convert the list to a Table before creating or resetting the chart source, then confirm that added rows are included. For a formula-driven combo chart, check whether the data range includes all spilled rows. Excel for Mac has comparable charting features, but ribbon placement and labels may vary by release. Excel for the web may not expose every statistical-chart option; if Pareto is unavailable there, create the chart in the desktop app or build the summary and combo chart where the required chart controls are available.
When to choose another Excel approach
Use the built-in Pareto chart when you have raw one-column observations and want Excel to count and rank categories with minimal setup. Use the formula-and-combo-chart approach when you have summarized values, need control over the measure, or want to make the calculation steps visible. A PivotTable can also summarize larger logs by category; add the relevant count or sum, sort the results, and create a chart from the summary. Pivot-based reports generally need refreshing after source data changes, so they are not automatically current merely because new rows exist.
For a small fixed list, manual formulas are easy to audit. For a recurring report with changing inputs, a Table-backed source and formulas reduce repeated work, but verify the chart range whenever the number of categories changes. The best method depends less on the word automatic than on the shape of your source data and how much control you need over the result.
Frequently Asked Questions
Can Excel make a Pareto chart automatically?
Yes. In desktop Excel, select a single column of raw category observations and choose Insert > Insert Statistic Chart > Pareto. Excel counts, ranks, and calculates the cumulative percentage. For an existing category-and-count summary, make a combo chart instead.
Can I make a Pareto chart from a table of counts?
Yes, but the most reliable route is to sort the counts in descending order, calculate cumulative percentages, and insert a combo chart with columns and a line on a secondary axis. The built-in Pareto chart is intended to count repeated raw observations.
Does an Excel Pareto chart update when I add data?
It updates when values within its source range change. New rows are included only if the chart’s source range expands to include them. Tables and dynamic formulas help, but verify the chart range after adding rows or new categories.
How do I show the cumulative percentage as a line?
For a summarized dataset, add a cumulative-percentage column and use Insert > Insert Combo Chart > Create Custom Combo Chart. Set that series to Line and select Secondary Axis, then format the axis as a percentage.
Does every Pareto chart have to reach exactly 80%?
No. The 80/20 idea is a guideline, not a rule built into the chart. The cumulative line shows the actual share represented by the ranked categories, which may reach 80% after a few categories, many categories, or not at a particularly meaningful category boundary.
Related
- Excel Merge Cells in Each Row Without Losing Values
- Excel Merge Cells Greyed Out in a Table? Here’s Why
- Excel Merge Cells Across Rows: Methods, Limits, Tips
Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.
More in Charts & pivot tables
- Excel SmartArt Org Chart: Create and Format a HierarchyTo create an org chart in Excel for Microsoft 365 on Windows, select Insert > SmartArt > Hierarchy,…
- Excel Dashboard Inspiration: Practical Ideas and Design TipsExcel dashboard inspiration starts with a clear question: what should someone understand or decide after looking at the…
- How to Create an Org Chart in Excel: Steps and TipsTo create an org chart in Excel for Microsoft 365 on Windows, select Insert > SmartArt > Hierarchy…
- Excel Gantt Chart Schedule Template: Build, Format, and UseAn Excel Gantt chart schedule template combines a task list with a dated timeline, then uses conditional formatting…
- How to Move the Y-Axis to the Right in ExcelTo move a chart’s vertical (Y) axis to the right in Excel for Microsoft 365 on Windows, select…
- Excel Bar Chart Different Color Bars: How to Set ThemYes. In Excel for Microsoft 365 on Windows, select the chart, right-click a bar, choose Format Data Series,…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.