With categories in A2:A6 and their counts in B2:B6, enter =SUM($B$2:B2) in C2 for the running total and =C2/SUM($B$2:$B$6) in D2 for the cumulative percentage; fill both formulas down after sorting the counts from largest to smallest. The cumulative percentage formula is the key to a formula-built Pareto chart in Excel; you can then plot counts as columns and cumulative percentages as a line.
What a Pareto chart shows
A Pareto chart combines descending bars with a line showing the running share of a total. It helps reveal which categories account for the largest portion of a measured problem or result. For example, a quality team might compare defect types, while a support team might compare reasons for customer contacts. The bars show each category’s count, and the line shows how quickly the categories add up to the whole.
The chart is associated with the Pareto principle, often summarized as “80/20.” That phrase is a useful reminder to look for a small number of important contributors, not a promise that every dataset will follow an exact 80/20 split. The actual cumulative percentages come from your data. A chart might reach 80% after three categories, or not until most of them have been included.
In Excel, “Pareto chart formula” usually means the formulas for the running total and running percentage. Excel also has a built-in Pareto chart type, which can calculate and display the cumulative line without worksheet formulas. The formula method is useful when you want to inspect or reuse the calculations, customize the chart, or add a target such as an 80% reference line.
Set up and sort the source data
Use one row per category and one numeric value per category. A compact example is:
- Defect A — 42
- Defect B — 28
- Defect C — 16
- Defect D — 9
- Defect E — 5
The counts total 100, so the cumulative percentages are easy to check: 42%, 70%, 86%, 95%, and 100%. In a real workbook, the total may be different. The important preparation step is to sort the count column in descending order while keeping each category paired with its count. Select the source table, then use Data > Sort and sort by the count column, largest to smallest.
Do not sort only the numbers: doing so would disconnect them from their category labels. Also check that the values are genuine numbers rather than text that merely looks numeric. Remove or resolve blank category rows and decide how to handle negative values or missing observations before charting. A conventional Pareto chart assumes that the values represent nonnegative contributions to a meaningful total.
Enter the cumulative formulas
Suppose row 1 contains headers: Category in A1, Count in B1, Cumulative count in C1, and Cumulative percentage in D1. With the first sorted count in B2, the running-total formula in C2 is =SUM($B$2:B2). Copy it down through C6. The dollar signs keep the start of the range fixed while its end expands from row to row.
With counts in B2:B6 and the running total in C2:C6, enter =C2/SUM($B$2:$B$6) in D2 and fill it down through D6. Format D2:D6 as percentages using Home > Number > Percent Style. The final cumulative percentage should be 100% (or very close to it if displayed rounding is involved). In the example, C2:C6 becomes 42, 70, 86, 95, and 100; D2:D6 becomes 42%, 70%, 86%, 95%, and 100%.
These formulas rely on the data already being sorted. If the source order changes, the calculations still run, but the line no longer represents a Pareto ranking from the largest contributor downward. For a dataset with many rows, use the same formula pattern in the first data row and fill down to the final category. Update the total range if you add or remove rows, or convert the source into an Excel Table and make sure the formulas and chart include the new records.
Build the chart with the formulas
For a formula-driven chart, use the count column for bars and the cumulative percentage column for the line. First select the category and count data, including their headers, such as A1:B6. Insert a clustered column chart with Insert > Insert Column or Bar Chart > 2-D Column > Clustered Column. Then add the cumulative percentage as a chart series: select the chart and use Chart Design > Select Data > Add. Set the series name to D1 and the series values to D2:D6. Set the horizontal category labels to A2:A6 if Excel has not assigned them automatically.
With the chart selected, choose Chart Design > Change Chart Type > Combo. Assign the count series to Clustered Column and the cumulative percentage series to Line, then select Secondary Axis for the percentage series. The secondary axis matters because the counts and percentages use different scales. Format that axis as a percentage and set its bounds from 0 to 1 if Excel displays the underlying decimal scale; depending on the chart and version, Excel may show 0% to 100% after percentage formatting. Add a title and axis titles through Chart Design > Add Chart Element.
Excel for Microsoft 365 on Windows may label or arrange chart commands slightly differently as the ribbon width changes, but the combo-chart settings are available from the chart design tools. In Excel for Mac, use the Insert tab for chart insertion and the chart design or format controls for series and axis settings; ribbon labels can vary by release. Excel for the web has a more limited set of chart editing controls than the desktop app, so finish a custom combo chart in desktop Excel if a needed secondary-axis option is unavailable.
Use Excel’s built-in Pareto chart
If you do not need visible worksheet calculations, Excel can create the statistical chart directly. Select the category and count data, including headers, and choose Insert > Insert Statistic Chart > Pareto. Excel creates descending columns and a cumulative percentage line. In some ribbon layouts, the statistic-chart command appears in the Charts group under Insert. This route is quick for a straightforward list of categories and values, but it gives you less direct control over the underlying cumulative calculations than the formula method.
Check the result rather than assuming that the chart’s appearance proves the source is right. Confirm that the category labels are correct, the bars are ordered as intended, and the cumulative line reaches 100% at the last category. If you need to change the chart’s series, axes, or formatting, select the chart and use the Chart Design and Format tabs. On Mac, look for the Pareto option in the Insert tab’s statistical chart choices; if the command is not present in your Excel release, use the formula-and-combo-chart method instead.
Add an 80% reference line
An 80% line can make it easier to see which categories together reach a chosen threshold, but it is a visual guide, not a rule that every dataset must satisfy. With categories in A2:A6 and cumulative percentages in D2:D6, enter 80% in E2 and fill that value down through E6. Add E1:E6 as another chart series, then set that series to Line on the secondary axis in the combo chart. Format it with a contrasting color or a dashed line so it is distinguishable from the cumulative curve.
Read the intersection carefully: the first category at which the cumulative line meets or passes 80% marks the point where the running total reaches that threshold. In the example, the first three categories add to 86%, so the chart passes 80% at Defect C. This does not mean those categories are automatically the right ones to fix first. Cost, feasibility, severity, and the reason the counts occur may also matter.
Common formula and chart problems
- The cumulative percentage ends above or below 100%: Check that the denominator includes every intended count and that no rows were omitted or counted twice. For a simple positive dataset, the final running total should equal the denominator.
- The line zigzags or categories appear in the wrong order: Sort the entire category-and-count table by count, largest to smallest, before relying on the cumulative calculation. Recheck the horizontal labels after sorting.
- The percentage line looks flat near zero: It may be plotted on the count axis. Change the chart to a combo chart and put the percentage series on a secondary axis.
- The axis shows decimals: Format the secondary vertical axis as Percentage. Excel stores 80% as 0.8, so the underlying decimal scale is normal.
- New data is missing: The formulas, denominator range, or chart series may stop at the old final row. Extend each range or use an Excel Table so the source expands more predictably.
- There are many tiny categories: The chart can become difficult to read. Consider combining low-frequency items into an “Other” category only when that grouping is meaningful, and document what it contains.
A further point to check is what each number represents. A Pareto chart of raw event counts answers a different question from one based on cost, downtime, or severity. If one category occurs rarely but has a very large impact, ranking only by frequency can hide it. Choose a consistent measure that matches the decision you are trying to make, and label the chart so readers know what the bars count.
Choosing between formulas and the built-in chart
Use the built-in Pareto chart when you want a quick visualization of a simple category-and-value list and do not need the cumulative calculations displayed in cells. Use formulas and a combo chart when you want to audit the running totals, reuse the percentages in another report, add a target line, or exercise more control over chart series and formatting. Both approaches depend on sound input data and a clear definition of the total.
Neither option automatically explains why a category is high or proves that addressing it will improve a process. The chart is a prioritization aid: it summarizes how the selected measure is distributed among categories. Follow it with investigation of causes and context before treating the largest bar as the only worthwhile action.
Frequently Asked Questions
What is the formula for a Pareto chart in Excel?
For sorted counts in B2:B6, calculate the cumulative count in C2 with =SUM($B$2:B2), then calculate the cumulative percentage in D2 with =C2/SUM($B$2:$B$6). Fill both down. Use the counts as columns and cumulative percentages as a line on a secondary axis.
Does Excel have a built-in Pareto chart?
Yes. In desktop Excel, select the category and value data and choose Insert > Insert Statistic Chart > Pareto. If that chart type is not available in your release, calculate the cumulative percentages in cells and make a combo chart.
Do I have to sort the data first?
For the formula method, yes: sort the complete category-and-value table from largest value to smallest before calculating the cumulative percentages. The built-in Pareto chart generally orders the categories for the chart, but you should still confirm the labels and ordering in the output.
Why does my Pareto chart show the wrong percentage?
Check the denominator range, whether every intended category is included, and whether the percentage line uses a secondary axis. Also verify that the count data are numeric and that the final cumulative total matches the sum of the source values.
Can I make a Pareto chart with an 80% line?
Yes. Add a column containing 80% for each category, add it as a chart series, and format it as a line on the secondary percentage axis. It marks a reference threshold; it does not imply that the data must follow an 80/20 distribution.
Related
- Microsoft Excel Quick Repair: Steps and What It Fixes
- Microsoft Excel Password Protection: How It Works
- Microsoft Excel Profit and Loss: Build a P&L Report
Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.
More in Charts & pivot tables
- Excel Pivot Table Calculated Fields: Formulas ExplainedTo add a calculated field in an Excel pivot table, go to PivotTable Analyze > Fields, Items &…
- Add an Upper Limit Line to an Excel ChartTo add an upper limit line to an Excel chart, create a helper series whose value repeats the…
- Excel Column Chart Colors Based on Values: How to Set ThemTo color an Excel column chart by value, use separate helper series for the value ranges you want…
- Stacked Column Chart in Excel: Create and Read ItTo make a stacked column chart in Excel for Microsoft 365 on Windows, arrange categories in the first…
- Excel Pivot Table Dates by Week: Grouping and FixesTo show Excel PivotTable dates by week, place the date field in Rows, right-click a date in the…
- Excel Pivot Table Won’t Refresh? How to Fix ItIf an Excel PivotTable won’t refresh, select a cell inside it and choose PivotTable Analyze > Refresh in…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.