To calculate a Pareto chart in Excel, arrange your categories and counts, sort the counts from largest to smallest, calculate each category’s cumulative share of the total, and chart the counts as columns with cumulative share as a line. The chart helps you see which few causes account for most of the total, so you can prioritize investigation or action.
The steps below use a summary table, which is useful when you already know the count for each category. They apply to Excel for Microsoft 365 on Windows. You can also use Excel’s built-in Pareto chart for raw, ungrouped observations, but a combo chart gives you more control over summarized data and labels.
What You’ll Need
- Excel for Microsoft 365 and a worksheet with category names and a nonnegative count for each category.
- A complete, consistently defined set of categories for the period or group you are analyzing. Decide what counts as one observation before you tally the data.
- At least two categories. A Pareto chart is most useful when there are several categories to compare.
Step-by-Step
-
Enter your category counts. Put category names in column A and their counts in column B. In row 1, enter the headings Category and Count. For example, use these rows: Late delivery, 32; Damaged, 24; Wrong item, 18; Missing parts, 15; Billing issue, 7; Other, 4. The total is 100 observations. Counts can represent defects, complaints, delays, or another consistently counted event.
Use one row per category and make sure the figures in the count column are numeric, not text that merely looks like a number. Do not include a total row among the category data; the formula will calculate the denominator itself.
-
Sort the counts in descending order. Select the data, including both columns and their headings, then choose Data > Sort. Sort by Count, from Largest to Smallest, and confirm that the data has headers. Sorting the complete rows keeps every category paired with its correct count. In the example, the categories are already in descending order.
This order is essential: a Pareto chart ranks categories from highest to lowest. If you later change or add counts, sort the rows again before interpreting the chart.
-
Add a cumulative percentage column. Enter Cumulative % in C1. With category names in A2:A7 and sorted counts in B2:B7, enter this formula in C2:
=SUM($B$2:B2)/SUM($B$2:$B$7)For the example layout, the first result is 32% because the first count is 32 out of a total of 100. Copy the formula down through C7. The running numerator expands as the formula is filled down, while the dollar signs keep the total range fixed. The resulting cumulative percentages are 32%, 56%, 74%, 89%, 96%, and 100%.
-
Format the cumulative values as percentages. Select C2:C7, then choose Home > Number > Percent Style. Set the decimal places as needed, for example zero or one decimal place. The underlying values are proportions, so percentage formatting displays 0.32 as 32%; it does not change the calculation.
Check the final value. It should be 100% (or very close to it if you are displaying rounded percentages). If it is not, check that the formula includes every count and that there are no blank or unintended rows within the data.
-
Select the data for the chart. Select A1:C7, including the headers, and choose Insert > Insert Combo Chart > Create Custom Combo Chart. In the combo chart dialog, set the Count series to Clustered Column and the Cumulative % series to Line. Check Secondary Axis for Cumulative %, then select OK.
The secondary axis is important because the counts and percentages use different scales. Without it, the line may sit near the bottom of the chart or be difficult to read. Category names should appear along the horizontal axis, with counts on the primary vertical axis and cumulative percentage on the secondary one.
-
Set the percentage axis to a useful scale. Click the right-hand vertical axis, which represents the cumulative percentage, and open Format Axis. Under Axis Options, set the minimum to 0 and maximum to 1 if the axis is using decimal values, then format the axis number as Percentage. Excel may set a suitable range automatically; check that the line reaches 100% at the last category and that the axis labels are readable.
Keep the left-hand axis for counts. Do not format both axes as percentages or use the count axis to interpret the cumulative line.
-
Make the chart easy to interpret. Add a clear chart title, such as Customer Issues by Type, and retain a legend that identifies the count columns and cumulative percentage line. If needed, use Chart Design > Add Chart Element to add axis titles or data labels. Make sure the categories remain in descending order and that the category names are not cut off.
You can also draw a horizontal 80% reference line if that threshold is useful for your analysis. It is a visual guide, not a rule that every dataset must follow. The cumulative line tells you how many of the ranked categories are needed to reach a chosen share of the total.
-
Read the result in context. In the example, the first three categories contribute 74% of the 100 observations, and the first four contribute 89%. That shows where the largest shares are concentrated; it does not prove why those issues occur or guarantee that addressing them will eliminate the same share of future problems. Use the chart to guide follow-up, then verify causes with additional evidence.
Alternative for raw observations: If your source data is one column containing an individual category for every observation, you can try Excel’s built-in chart. Select the raw category values, then choose Insert > Insert Statistic Chart > Pareto. Excel groups and ranks the categories for the chart. Use the formula-and-combo-chart method above when you have an already summarized count table or need direct control over its calculations.
Common Mistakes
- Sorting only the counts. This separates numbers from their category labels. Always sort the full table, with headers identified, by the count column.
- Using the wrong denominator. The cumulative percentage must use the total of all included categories. A total that omits a category makes the final percentage exceed 100% or otherwise misstates the shares.
- Adding the cumulative percentage as a column series. A cumulative series should be a line on a secondary axis. If it is plotted as columns on the count scale, the chart can look misleading.
- Including a total row as a category. A total row duplicates the values in the data and inflates the denominator. Keep it out of the chart source range.
- Mixing measurement periods or definitions. Counts are only comparable when the categories were counted over the same time window and according to the same rules.
- Treating the 80/20 idea as a fixed law. Some datasets have a few dominant categories; others are more evenly distributed. Report what your chart shows rather than forcing it to match a particular ratio.
Tips
- Turn the source data into an Excel Table. Select the data and use Home > Format as Table. Tables make headings clear and can expand when rows are added. Check that the formula and chart ranges include new categories, and sort again after updates.
- Choose a sensible category level. Categories that are too broad hide useful detail; categories that are too narrow create a long, hard-to-read chart. Group rare items as “Other” only when that grouping is appropriate, and explain what it contains.
- Use counts that answer a clear question. A chart of total complaints identifies the largest complaint counts, but it may not identify the highest complaint rate. If groups differ greatly in size, consider whether a rate per order, customer, or unit is more relevant before charting.
- Keep precision in the worksheet. Format percentages for readability without rounding the actual formula results. The line should be calculated from full-precision values.
- Make the reporting period visible. Put the date range in the chart title, subtitle, or a nearby note. This helps readers understand which observations the ranking covers.
Troubleshooting
- The chart uses the wrong categories or series: Select the chart, then use Chart Design > Select Data to inspect the chart data range and series names. Confirm that the headings are included once and that the category labels come from column A.
- The cumulative line appears flat or tiny: Open the combo chart settings and confirm that Cumulative % is a line on the secondary axis. Check that its axis is formatted as a percentage and uses a sensible range, normally 0% to 100%.
- The last point is not 100%: Verify that the formula’s denominator includes every count and that the filled-down formula ends at the last category. Check for counts stored as text, omitted rows, or a total row mistakenly included in the formula range.
- The chart is not in descending order: Sort the entire source range by Count, largest to smallest. If the chart does not update as expected, check Chart Design > Select Data and confirm that the chart references the sorted cells.
- The percentages look like decimals: Select the cumulative values or the secondary axis and choose Home > Number > Percent Style or format the axis number as Percentage. A stored value of 0.32 represents 32%.
- The built-in Pareto option is unavailable: Use the summarized-table and combo-chart method in this guide. If creating a built-in chart, select the raw one-column observations rather than a two-column summary table.
Frequently Asked Questions
What is the formula for cumulative percentage in a Pareto chart?
With sorted counts in B2:B7, enter =SUM($B$2:B2)/SUM($B$2:$B$7) in C2 and fill it down. Format the results as percentages. The formula divides the running count by the total count.
Does Excel have a built-in Pareto chart?
Yes. In Excel for Microsoft 365 on Windows, select raw category observations and choose Insert > Insert Statistic Chart > Pareto. It is convenient for raw data; a combo chart is more flexible for a summary table of category counts.
Can I make a Pareto chart from summarized data?
Yes. Sort your category-count table in descending order, calculate the cumulative percentage, and insert a combo chart with counts as columns and cumulative percentage as a line on a secondary axis. This method also makes the calculations visible in the worksheet.
Why does the cumulative percentage finish at 100%?
At the last category, the running count includes every included observation, so it equals the total in the denominator. If the result differs from 100%, inspect the ranges, data types, and any blank or extra rows.
How do I create the chart on a Mac?
The worksheet calculations are the same. In Excel for Mac, use the Insert tab and look for the combo chart option; depending on the ribbon layout, you may find it through Recommended Charts > All Charts > Combo. Set the cumulative series to a line and enable its secondary axis. Menu wording can vary slightly by Excel release.
Should I always use an 80% cutoff?
No. An 80% line is sometimes used as a reference, but it is not a required boundary. Choose a threshold that suits the decision you are making, and interpret the actual cumulative percentages and category context.
Related
- How to Add a Black Border in Excel to Cells and Tables
- How to Add Borders in Excel With Shortcut Keys
- Add a Zero Before a Number in Excel Without Losing It
Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.
More in Charts & pivot tables
- Create an Org Chart in Excel From a ListYes. Put each person in a list with their manager, then use Insert > SmartArt > Hierarchy >…
- Waterfall Chart in Excel: Sample Data and StepsTo make a waterfall chart in Excel for Microsoft 365 on Windows, enter categories in one column and…
- Excel Histogram Bin Range: Meaning and How to Set ItIn Excel’s Data Analysis Histogram tool, the bin range is a worksheet range containing the upper limits of…
- Excel Pivot Table by Month: Group Dates the Right WayTo make an Excel PivotTable by month, put a genuine date field in the Rows area, put the…
- Add a Series to a Google Sheets Chart: Steps and TipsTo add a series to a chart in Google Sheets, open the chart editor, choose Setup, and select…
- Excel Line Chart With Two Y-Axes: How to Create OneYes. In Excel for Microsoft 365 on Windows, create a line chart, select the series that needs its…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.