Yes. In Excel for Microsoft 365 on Windows, a reusable Pareto chart template can be made from a category-and-count table, a cumulative-percentage formula, and a combo chart with columns and a line. Sort the counts from largest to smallest, then chart the counts as columns and the running percentage on a secondary axis; this gives you a clear template even when Excel’s built-in Pareto chart does not fit your data layout.
What an Excel Pareto template contains
A Pareto chart ranks categories by their contribution to a total. Its bars run from largest to smallest, while a line shows the cumulative share as you move across the categories. It is useful for investigating defects, customer complaints, delays, expenses, support requests, or other outcomes that can be counted consistently.
A practical workbook template usually has three columns:
- Category: the issue, cause, product, or other group being compared.
- Count: the number of observations in that group, or another nonnegative measure that can be meaningfully added.
- Cumulative %: the running total of counts divided by the grand total.
The chart is a combination of the count bars and cumulative-percentage line. The line normally uses a secondary vertical axis scaled from 0% to 100%. The chart does not prove why a problem happens; it shows which measured categories account for the largest portions of the selected total.
Prepare and sort the source data
Start with a summary table rather than a list of individual transactions. Put category names in A1 and counts in B1, then enter the categories in A2:A11 and their counts in B2:B11. The example below uses ten categories, but you can use more or fewer; update formula ranges to match the actual last row.
Sort the rows by count from largest to smallest before creating the chart. Select a cell in the table and use Data > Sort, choose the count column, and select Largest to Smallest. Keep each category paired with its count. If you sort only the count column, labels will no longer describe the right values. For a small template, sorting the entire table this way is simple and transparent.
Check the source before charting. Counts should be numeric, not numbers stored as text, and categories should use consistent names. For example, “Late delivery” and “Late deliveries” will appear as separate bars unless you combine them first. Decide whether blank categories, cancellations, or unusual observations belong in the analysis, and apply that decision consistently.
Calculate the cumulative percentage
In C1, enter Cumulative %. With category names in A2:A11 and counts in B2:B11, enter this formula in C2 and fill it down through C11:
=SUM($B$2:B2)/SUM($B$2:$B$11)
The formula divides the running sum of counts by the total count. Format C2:C11 as Percentage using Home > Number, or use the Percent Style button. With nonnegative counts, the cumulative line should rise toward 100%; the last row should equal 100% apart from display rounding. The dollar signs keep the total range fixed as you fill the formula down, while the first part expands one row at a time.
If you add or remove categories, extend the formula’s total range and fill the formula through the new last row. A formatted Excel Table can make a template easier to extend, but the fixed-range formula above is straightforward for a first version and makes the calculation visible to anyone reviewing the sheet.
Build the chart in Excel
With headers and data in A1:C11, select the full range. On Windows in Excel for Microsoft 365, choose Insert > Recommended Charts > All Charts > Combo. Assign Count to Clustered Column and Cumulative % to Line, and enable Secondary Axis for the percentage series. Confirm the chart, then give it a descriptive title such as Pareto of Service Issues.
If the Combo option is not immediately visible, open the All Charts tab in the chart dialog and choose Combo. The series should be listed separately; check that the cumulative-percentage series is the line, not another set of columns. Excel for Mac also offers combo charts, though ribbon placement and dialog appearance can vary by release. Excel for the web may have different chart-editing controls; use the desktop app if the secondary-axis settings are not available in your interface.
Format the right-hand axis so it is easy to read. Select that axis, open Format Axis, and set its bounds to 0 and 1 if the underlying percentages are decimal values. Set the number format to Percentage. The left axis should show counts. Avoid putting both series on the same scale: a count such as 320 and a percentage such as 0.32 are different units, so a shared axis can make the line misleading or almost invisible.
Worked example: reading the results
Suppose a service team records 1,000 complaints across ten categories, with the sorted counts 320, 210, 150, 110, 80, 50, 35, 20, 15, and 10. The cumulative percentages are 32%, 53%, 68%, 79%, 87%, 92%, 95.5%, 97.5%, 99%, and 100%. The first four categories account for 79% of the recorded complaints; adding the fifth takes the cumulative share to 87%.
This lets a reader see both scale and accumulation. A bar identifies an individual category’s count, while the line answers how much of the total is covered by the largest categories so far. If a team uses an 80% reference point, it would fall just above the fourth category in this example, so the fifth category is where the cumulative line first passes it. That is a prioritization clue, not a universal rule that every project should target exactly 80%.
Make the template easier to use
Keep the input area distinct from calculated columns. Use clear headers, a note identifying the date range and data source, and a reminder that the rows must be sorted by count before charting. Apply a consistent number format: whole numbers for counts and percentages for the cumulative series. A concise chart title should identify what is counted and, where relevant, the period covered.
Consider adding a target line only if the reader has a meaningful threshold to compare against. For example, a horizontal 80% line can make the crossing point easier to spot, but it is not part of the basic Pareto calculation. It requires an additional series containing the same target percentage for every category and formatting that series as a line. Label it clearly so it is not mistaken for another cumulative measure.
Do not remove small categories simply to make the chart shorter without explaining what happened to them. Combining minor categories into “Other” can be useful, but it changes the visible ranking and can conceal a category that matters for a separate reason, such as severity or regulatory risk. Keep the underlying detail available and describe any grouping rule.
Built-in Pareto chart or manual template?
Excel includes a built-in Pareto chart under Insert > Insert Statistic Chart > Pareto. It is convenient when the source is a column of individual observations and the chart’s automatic grouping is appropriate. It can also be a quick way to explore data without building cumulative calculations yourself.
A manual combo-chart template is often preferable when you already have a summarized category-and-count table, need a visible cumulative-percentage column, want control over sorting and labels, or need to preserve a consistent chart layout for repeated reports. The two approaches are not interchangeable in every situation: the built-in statistical chart is designed to group observations, whereas the manual template treats the rows you provide as the categories to display. Check the result against the source data before sharing either chart.
Common problems and how to fix them
- The cumulative line does not end at 100%: check that the formula includes every count row and that the total range matches the data. Also check for negative values or excluded categories.
- The line looks flat or too low: verify that Cumulative % is plotted on the secondary axis and that the axis is formatted as percentages with a suitable scale.
- The bars are not in descending order: sort the complete category-and-count rows by count, then confirm the chart reflects that order. Re-sort after changing counts.
- Percentages exceed 100% or behave oddly: look for duplicated totals, formulas that point to the wrong range, or counts stored as text. Ensure the numerator is a running sum and the denominator is the grand total.
- The chart misses new rows: expand the selected source range or use an Excel Table so new rows can be included more conveniently. Check the chart’s data source after expanding the table.
A Pareto chart can also overstate certainty if the categories are inconsistent or the observation period is too short. Compare like with like: counts from different periods may need a rate or exposure measure if the number of opportunities changed substantially. A chart of raw counts answers “how many were recorded?”; it does not automatically answer “which group has the highest risk?”
Frequently Asked Questions
Does Excel have a Pareto chart template?
Excel has a built-in Pareto chart type, but a pre-made worksheet template is not required. You can create a reusable template with category, count, and cumulative-percentage columns, then add a column-and-line combo chart.
How do I make a Pareto chart from a frequency table?
Sort the frequency table by count in descending order, calculate the cumulative share of the total, and chart counts as columns with cumulative percentage as a line on a secondary axis. This manual method preserves your existing category totals.
What does the line on a Pareto chart represent?
It represents the cumulative percentage of the total as categories are added from largest count to smallest. It is not a separate count series and should be read against a percentage axis.
Does a Pareto chart always follow the 80/20 rule?
No. The chart reveals the distribution in the data; it does not guarantee that 80% of an outcome comes from 20% of categories. Treat any threshold as a chosen guide, not a mathematical requirement.
Can I use percentages or costs instead of counts?
You can use another additive, nonnegative measure, such as total cost by category, if the question is which categories contribute most to that measure. Label the units clearly and ensure the cumulative percentage uses the same measure as the bars.
Related
- Excel Shortcut to Select to the End of a Column Explained
- Excel Shortcut to Refresh a PivotTable and All Data
- Excel Shortcut to Select All Data in a Column Quickly
Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.
More in Charts & pivot tables
- Cannot Group That Selection in a Pivot Table? FixesIf Excel says “Cannot group that selection” when you try to group PivotTable items, first check the source…
- Excel Graph With Dates on the X-Axis: Setup and FixesTo make an Excel graph with dates on the x-axis, put real Excel dates in one column and…
- Org Chart Template for Google SheetsTo make an org chart in Google Sheets, put each person or position in column A and that…
- Enable Power Pivot in Excel: Steps and CompatibilityTo enable Power Pivot in Excel for Microsoft 365 on Windows, select File > Options > Add-ins, choose…
- How to Remove a Trendline from an Excel ChartTo remove a trendline from an Excel chart in Microsoft 365 for Windows, click the trendline to select…
- Add Labels to a Bubble Chart in Excel: Steps and TipsTo add labels to a bubble chart in Excel for Microsoft 365 on Windows, select the chart, choose…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.