Yes. You can make a reusable Pareto chart template in Excel for Microsoft 365 by listing categories and counts, calculating a cumulative percentage, and charting the counts with that percentage. For a quick chart from individual observations, use Excel’s built-in Pareto chart; for a template based on a prepared summary table, use a combo chart.
What a Pareto chart template is for
A Pareto chart combines bars, which rank categories from most frequent to least frequent, with a line showing the running cumulative percentage. It helps answer a practical question: which few categories account for the largest share of the total? Teams use it to examine things such as recurring product defects, customer complaints, reasons for delayed orders, or types of support requests.
The chart is associated with the Pareto principle, often summarized as an 80/20 rule. That phrase is a useful reminder to look for concentration, not a promise that exactly 20% of categories will always account for 80% of outcomes. Your actual data may show a different distribution. A template makes the calculation and display repeatable; it does not establish why a category is common or what action will fix it.
A useful workbook can include the data period, the source, a clear definition of what counts as an event, and a note about exclusions. For example, a chart of complaints is hard to compare month to month if one month includes email and phone contacts while another includes only phone calls.
Choose a built-in chart or a summary-table template
Excel’s built-in Pareto chart is convenient when you have raw observations: one cell per recorded event, with a category such as Scratch or Late delivery in each cell. Excel counts the categories and presents a sorted Pareto chart. In Excel for Microsoft 365 on Windows, select the observation cells, then choose Insert > Insert Statistic Chart > Pareto.
A manual combo chart is usually better when you already have one row per category and a frequency count, or when you want visible calculations that can be checked. It also gives you more control over labels, formatting, targets, and workbook layout. The steps below create that reusable version.
Excel for Mac and Excel for the web can have different chart menus or feature availability depending on the version. If you cannot find a Pareto chart command, use the summary-table and combo-chart method instead. The worksheet formulas described here work in current desktop Excel versions.
Set up the worksheet
Create a sheet named Pareto and add these column headings in row 1: Category, Count, and Cumulative %. Enter one category and its count on each row, with no blank rows inside the data. Use counts from the same time period and apply the same counting rules to every category.
For a concrete example, enter the following counts in rows 2 through 6: Label, 42; Scratch, 25; Wrong part, 15; Missing screw, 10; Other, 8. Put the largest count first. This is important because the cumulative line represents the running share as you move from the most frequent category down the ranking.
- Keep category names short enough to remain readable along the horizontal axis.
- Use whole-number counts when events are counted individually; do not mix counts with rates or percentages.
- Put a total beneath the data if you want a visible check, but exclude that total row from the chart.
- Record the reporting period and data source in a note or in separate cells above the table.
Calculate the total and cumulative percentage
With the headings in row 1, category counts in B2:B6, and the total in B7, enter this formula in B7:
With counts in B2:B6, this formula returns the total number of events in B7: =SUM(B2:B6). For the example, the result is 100.
Next, with the counts in B2:B6, the total in B7, and cumulative percentages beginning in C2, enter this formula in C2 and fill it down through C6:
=SUM($B$2:B2)/$B$7
The dollar signs keep the starting count and total fixed as you copy the formula down, while the final row reference expands. Format C2:C6 as Percentage using Home > Number and the percentage format. The example returns 42%, 67%, 82%, 92%, and 100%. If Excel displays decimal values instead, the calculation may still be correct; apply percentage formatting rather than multiplying the formula by 100.
For a different number of categories, adjust the formulas and ranges to include all data rows. Do not include the total row in the cumulative formula’s running sum. If counts are added later, extend the formula range and the chart source so the new categories appear.
Create the combo chart
Select A1:C6, including the headings but not the total row. In Excel for Microsoft 365 on Windows, choose Insert > Insert Combo Chart > Create Custom Combo Chart. Set Count to Clustered Column and Cumulative % to Line, then select the Secondary Axis option for Cumulative %. Confirm the chart. If the combo option is not visible, insert a chart first, then use Chart Design > Change Chart Type > Combo.
The count bars should use the primary vertical axis, while the cumulative line uses the secondary vertical axis. Format the secondary axis as a percentage and, if necessary, set its minimum and maximum bounds to 0 and 1 so it spans 0% to 100%. You can right-click an axis and choose Format Axis to adjust its number format and bounds. Add a descriptive chart title, such as Defects by Type, and check that both axis labels are clear.
In the example, the line reaches 82% at Wrong part. That means the first three categories together represent 82 of the 100 recorded events. It does not mean those categories caused 82% of the problem, nor does it show how costly or serious each event was. Add a separate measure, such as repair cost or severity, if frequency alone is not the decision you need to make.
Use raw observations with Excel’s Pareto chart
For raw data, put one observation in each cell of a single column, with a heading such as Defect type. A list might contain Label repeated 42 times, Scratch repeated 25 times, and so on. Select the heading and observations, then use Insert > Insert Statistic Chart > Pareto in Excel for Microsoft 365 on Windows. Excel groups repeated categories, orders them by frequency, and draws the cumulative-percentage line.
This route avoids building a summary table by hand, but it is less transparent if readers need to inspect or reuse the category counts. It also depends on consistent labels: Scratch, scratch, and Scratch with extra spaces may be treated as distinct entries. Clean inconsistent text before charting. For a regularly refreshed report, a summary table can make the counted values easier to validate and the calculation easier to audit.
A chart made from a fixed range may not automatically include new records pasted below the original selection. After adding data, check the chart source and confirm that counts and percentages have updated. A formatted Excel table can help manage expanding data, but verify the chart’s source range rather than assuming every chart expands exactly as you intend.
Read the result without overclaiming
Read the bars from left to right to see the ranking, then follow the cumulative line to estimate the share represented by the categories included so far. A steep early rise means a small number of categories make up a large share of events. A gradual rise suggests the events are spread more evenly. A tie in counts is not a meaningful difference in rank; tied categories may appear in either order unless you choose a consistent tie-breaking rule.
Consider a support team with 200 tickets: 70 about password resets, 50 about billing, 40 about setup, and 40 about other issues. The cumulative shares are 35%, 60%, 80%, and 100%. The chart shows that the first three categories account for 80% of ticket volume, but it cannot tell you whether password resets take more staff time than billing questions. If that matters, collect handling time or cost and analyze that measure separately.
Also check the denominator. If your categories exclude some events, say so; a cumulative line ending at 100% means 100% of the included total, not necessarily every event that occurred. A clear title, date range, source, and definition make the chart far more useful to someone who did not build it.
Common template problems and fixes
- The cumulative line is not increasing. Check that counts are sorted largest to smallest and that the cumulative formula starts at the first count and expands row by row.
- The line appears nearly flat. It may be plotted on the same axis as counts. Put Cumulative % on a secondary axis and format that axis as a percentage.
- The last percentage is not 100%. Check that the total includes every category and that the final cumulative formula reaches the last data row.
- The bars show categories in an unexpected order. Sort the category-and-count rows together by Count, largest to smallest. Do not sort just one column, or the labels will no longer match their counts.
- Blank or duplicate-looking categories appear. Look for empty cells, spelling differences, leading or trailing spaces, and inconsistent capitalization in the source data.
Before sharing the workbook, check a few calculations independently and confirm the chart excludes the total row. If several people will reuse the file, label input cells clearly and distinguish them from formula cells. This reduces the chance that someone overwrites the calculations while updating counts.
Cost, sharing, and alternatives
The worksheet layout and formulas above do not require a paid add-in. Excel itself requires access to the application or service, which may depend on a Microsoft 365 subscription, a workplace or school license, or another valid Excel license. If you already have Excel, creating this template adds no separate chart-template fee. No external download is needed for the steps here.
You can share the workbook as an .xlsx file or store it in a location your team can access. If the underlying counts contain confidential customer or employee information, share only with people authorized to see them and consider whether aggregated categories are sufficient. A chart is not anonymous merely because it omits names if the counts or labels reveal sensitive details.
For a simple snapshot, Excel’s built-in Pareto chart may be the quickest option. For a transparent, reusable report, the manual summary and combo chart provides more control. A sorted bar chart without a cumulative line is another reasonable alternative when ranking is enough and the cumulative share would not affect the decision.
Frequently Asked Questions
Does Excel have a free Pareto chart template?
Excel includes a Pareto chart type in supported desktop versions, and you can make a reusable summary-table template with ordinary worksheet formulas and a combo chart. The steps above show how to create the template; they do not link to a separate downloadable workbook.
Can I make a Pareto chart from an existing frequency table?
Yes. Sort the category rows by count from largest to smallest, calculate the running cumulative percentage, and chart the counts as columns with the percentage as a line on a secondary axis. That is the manual method described above.
Why does my Pareto chart not show 80% at the fourth category?
The 80/20 phrase is a rule of thumb, not a required result. Your cumulative line reaches 80% wherever your actual counts add up to that share. Some data sets reach it quickly; others do not reach it until many categories have been included.
Can a Pareto chart use percentages instead of counts?
It can, but use a consistent measure and be explicit about what it represents. If category percentages are shares of the same total, the cumulative percentage is also a running share. Avoid charting pre-calculated percentages alongside an unrelated count total.
How do I update the template with new data?
Update the category counts, make sure the rows remain sorted in descending order, and check that formulas and chart ranges include every category. Then verify that the last cumulative percentage is 100% of the included total.
Related
- Excel Text Box Wrap Text: How to Enable It
- Excel Template for a Social Media Calendar
- Excel Job List Template: Track Applications and Follow-Ups
Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.
More in Charts & pivot tables
- Excel Chart Group by Category: How to Set It Up RightTo group Excel chart data by category, first summarize the records so each category has one row, then…
- Excel Bar of Pie Chart: How It Works and When to Use ItAn Excel Bar of Pie chart separates smaller categories from a pie chart and displays those categories as…
- Excel Gantt Chart Automatic? Setup, Updates, and LimitsYes. In Microsoft Excel for Microsoft 365 on Windows, a Gantt chart can update automatically when you change…
- Excel Stacked Bar Chart Not Proportional? Fix the ScaleIf an Excel stacked bar chart does not look proportional, first check whether it is a regular Stacked…
- Excel Histogram Equation: Formulas, Bins, and ChartsExcel has no single “histogram equation”: a histogram groups numeric values into intervals called bins and shows how…
- P Chart in Excel: Build Control Limits and Read ResultsTo make a p chart in Excel, calculate each subgroup’s fraction nonconforming, find the overall fraction and three-sigma…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.