Yes. In Excel, a Pareto chart displays categories from largest to smallest and plots their running total as a cumulative-percentage line; for a manual chart, calculate cumulative count with =SUM($B$2:B2) and divide by the overall total with =C2/SUM($B$2:$B$6). With counts 40, 25, 15, 12, and 8 in B2:B6, the cumulative percentages are 40%, 65%, 80%, 92%, and 100%.
What cumulative percentage means
A cumulative percentage answers the question: how much of the total is accounted for by this category and every category above it? The first point represents the largest category alone. Each following point adds that row’s count to the running total, then expresses the result as a share of all observations. The final point should reach 100% if the data and formulas include every category.
For example, imagine a support team records 100 service complaints: 40 concern late delivery, 25 concern damaged items, 15 concern billing, 12 concern product questions, and 8 fall into other issues. Late delivery accounts for 40% of complaints. Late delivery plus damaged items account for 65%; adding billing brings the cumulative share to 80%. This makes it easier to see how a small number of high-frequency causes contribute to the overall total.
The line is not another count series. It is a running proportion, usually shown on a secondary vertical axis scaled from 0% to 100%. The bars show individual category counts, while the line shows the total share covered as you move across the ranked categories.
Why sort categories from largest to smallest
The descending order is central to the Pareto view. If the smallest category appears first, the line still adds up mathematically, but the chart no longer makes the most common causes easy to spot. Sorting puts the categories with the greatest counts at the left and makes the cumulative curve rise quickly when a few categories dominate.
This idea is often linked to the 80/20 rule, but that is a rule of thumb, not a requirement or a promise. A dataset may reach 80% after two categories, after most categories, or not at a neat boundary at all. The useful question is what the data actually show, rather than whether they match an assumed ratio.
Use Excel’s built-in Pareto chart
For a quick chart in Excel for Microsoft 365 on Windows, put category names in one column and nonnegative counts in the next. Include a single header row if you have one, select the data, then choose Insert > Insert Statistic Chart > Pareto. Excel creates descending bars and a cumulative-percentage line, so you do not need to calculate the line yourself for a basic chart.
Check the result before using it in a report. Confirm that Excel recognized the category labels, that the largest bar is first, and that the line ends at 100%. If the chart looks wrong, check the selected range and the source values. Extra blank rows, totals included as if they were categories, or multiple columns of unrelated measures can make Excel interpret the input differently than intended.
- Best for: a fast overview from one category-and-count table.
- Less suitable for: a chart that must retain a particular category order, display a custom calculation, or remain tightly controlled as a report template.
- For those cases: calculate the cumulative percentage in worksheet cells and build a combo chart from the results.
The chart gallery and available formatting controls can vary slightly by Excel release and platform. On Excel for Mac, look for the statistical chart options on the Insert tab; if Pareto is unavailable in your build, use the manual method below. Excel for the web may not expose every chart-creation or formatting control found in the Windows desktop app, so a prepared chart or desktop Excel may be needed for more detailed edits.
Calculate cumulative percentages in worksheet cells
A manual calculation makes the source of every point visible and gives you control over the ordering. Start with categories in A2:A6 and their counts in B2:B6. Sort the rows by count from largest to smallest before calculating, keeping each category beside its count. In C2, calculate the running count. In D2, divide that running count by the total. Copy both formulas down through row 6.
With counts 40, 25, 15, 12, and 8, the total is 100. In C2, the formula =SUM($B$2:B2) returns 40; filled down, it returns 40, 65, 80, 92, and 100. With the same layout and the running count in C2:C6, enter =C2/SUM($B$2:$B$6) in D2. It returns 40%, and when filled down it returns 40%, 65%, 80%, 92%, and 100%.
Format D2:D6 as percentages using Home > Number > Percent Style. Increase or decrease decimal places if the underlying totals make whole percentages misleading. The formula stores a fraction such as 0.4; percentage formatting displays it as 40%. If the total is zero, a division error is expected because a percentage of a zero total is undefined. Investigate the source data rather than treating that error as a valid chart value.
For a longer table, select the formulas in C2:D2 and fill down to the last category. If you add new categories later, extend the total range in the denominator and copy the formulas into the new rows. When categories are added or counts change, re-sort the entire category-and-count table before relying on the cumulative order.
Build a combo chart with a percentage line
Once the sorted table has category, count, and cumulative-percentage columns, select A1:B6 and D1:D6, including the headers. If the ranges are nonadjacent, select the first range, then hold Ctrl on Windows while selecting the second. On Mac, use Command instead of Ctrl. Choose Insert > Insert Combo Chart > Create Custom Combo Chart. Set the count series to Clustered Column and the cumulative-percentage series to Line, then enable Secondary Axis for the percentage series. If the chart gallery offers a Recommended Charts route instead, choose the Combo chart type there and assign the same series types.
Assigning a secondary axis matters because counts and percentages use different scales. Without it, a percentage line may look nearly flat beside counts such as 40 or 100, or the chart may be difficult to interpret. The left axis should describe counts and the right axis should describe cumulative percentage. Add a clear chart title and axis titles so readers do not mistake the line for another count measure.
To format the right axis, select it and open Format Axis. Set its minimum to 0 and maximum to 1 when the series contains decimal percentages, then apply Percentage number formatting. A maximum of 1 is displayed as 100%. If the axis is already formatted as a percentage, verify that the plotted values are decimals such as 0.8 rather than whole-number entries such as 80; otherwise the scale can be wrong.
Read the chart without overstating what it proves
Use the bars to identify the largest observed categories and the line to see how quickly their combined share accumulates. In the example, the first three categories reach 80%, so a team investigating complaint volume might begin by examining delivery, damage, and billing processes. That is a way to prioritize investigation, not proof that those causes are the only ones worth addressing or that fixing them will reduce complaints by exactly 80%.
A Pareto chart describes the data you supplied. It does not establish why a category is frequent, whether categories were recorded consistently, or whether the sample represents future events. If the underlying period, process, or definitions change, the ranking and cumulative shares can change too. Include the date range and a brief definition of what was counted when readers might otherwise misinterpret the chart.
Also distinguish cumulative share from the percentage of a single category. In the example, billing alone is 15% of the total, but its cumulative point is 80% because it follows the first two categories. The bar describes billing by itself; the line at the billing position describes the first three categories together.
Common problems and practical checks
- The line does not end at 100%: Check that the final formula includes every category and that the denominator covers the same count range. Look for omitted rows or a total row accidentally included in the categories.
- The line is flat or off the chart: Confirm that it uses cumulative percentages, not raw counts, and that it is plotted on the secondary axis. Check whether percentages are stored as decimals or as whole numbers.
- The categories are in an unexpected order: Sort the source rows by count descending before making a manual combo chart. The built-in Pareto chart sorts its bars for the chart, but a manual chart follows the order in the worksheet.
- There is a divide-by-zero error: A zero overall count cannot produce meaningful percentages. Check whether the counts are missing or filtered out, and do not hide the issue by presenting an invented 100% result.
- Some bars are negative or fractional: Standard Pareto analysis is generally intended for frequencies or nonnegative quantities. Reconsider whether the measure is appropriate, and make sure the categories are mutually defined and counted consistently.
For clarity, do not add a grand-total row to the category series: it is not a separate category and can distort both the bars and the cumulative calculation. If you want to show the total, state it in a subtitle or a nearby worksheet cell instead.
Frequently Asked Questions
Does Excel calculate cumulative percentage automatically in a Pareto chart?
Yes. Excel’s built-in Pareto statistical chart creates the cumulative-percentage line from the category counts. Use worksheet formulas and a combo chart when you need to inspect or customize the calculation.
What formula calculates cumulative percentage in Excel?
For sorted counts in B2:B6, put the running sum in C2 with =SUM($B$2:B2), then divide by the full count total in D2 with =C2/SUM($B$2:$B$6). Fill both formulas down; format the percentage results as Percentage.
Why does a Pareto chart use a secondary axis?
The bars represent counts, while the line represents a share from 0% to 100%. A secondary axis lets each series use a scale that readers can interpret, instead of forcing counts and percentages onto one vertical scale.
Does a Pareto chart always show an 80/20 split?
No. The 80/20 phrase is a common heuristic, not a chart rule. The cumulative line reports the actual distribution in the data, which may or may not put roughly 80% of the total in roughly 20% of the categories.
Can I use percentages instead of counts?
You can chart nonnegative values that represent category shares, but make sure they refer to the same whole and that the total is meaningful. For a conventional Pareto analysis, category frequencies or counts are usually the clearest input; the cumulative line then expresses their running share of the count total.
Related
- Microsoft Access vs Excel: Which Tool Fits Your Data?
- Merge Excel Sheets to Find Specific Names in Excel
- How to Merge Two Tables in Excel Using Power Query
Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.
More in Charts & pivot tables
- Free Excel Dashboard Templates XLS: Find and Use ThemYes—free Excel dashboard templates are available to download, but many are offered as .xlsx files rather than the…
- Excel Bar Chart With Line Overlay: How to Create OneTo make an Excel bar chart with a line overlay, select your category labels and data series, then…
- How to Make a 4-Quadrant Graph in ExcelTo make a 4-quadrant graph in Excel, plot your paired X and Y values in an XY scatter…
- Excel Waterfall Chart Not Showing Total? Fix ItIf an Excel waterfall chart is not showing a total, select the bar that should represent the total,…
- Excel Trendline Formula: Find, Read, and Calculate ItAn Excel trendline formula is the equation Excel fits to data in a chart; for a linear trendline,…
- Packed Bubble Chart in Excel: Options and AlternativesExcel for Microsoft 365 does not include a native packed bubble chart. You can make a standard bubble…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.