How to Draw a Pareto Diagram in Excel | Sorted Bars & Cumulative Line

A handful of causes usually drive the majority of any problem, and a Pareto diagram in Excel makes those few stand out with two chart elements working together. The bars drop from largest to smallest while a line traces the running percentage across categories. Excel 2016 and later include a one-click Pareto chart type, and older versions can build the same result with a combination chart.

What Makes a Pareto Diagram Different?

A Pareto diagram layers a cumulative percentage line over descending frequency bars, letting you spot where the 80/20 breakpoint falls. The left vertical axis measures counts or costs, the right axis tracks cumulative percentage, and the line rises fastest in the first few categories. Categories before the steep part of the line are the “vital few” — the ones worth fixing first. This structure separates a Pareto chart from a plain sorted bar chart, which shows magnitude but not the running contribution toward the total.

Setting Up Your Data the Right Way

Your data needs one column of labels and one column of numbers. Categories like complaint types, defect names, or cost centers go in the first column; the corresponding frequencies, counts, or dollar values go in the second. Arrange each row so the highest number sits at the top — select any value in the number column, then click Data > Sort Largest to Smallest. Highlight the full range before sorting so the labels travel with their numbers. A total row at the bottom (SUM of the number column) helps later if you build the chart manually, but the built‑in Pareto type handles the total automatically.

This table shows a typical Pareto dataset sorted from most frequent to least, with a total row ready for manual cumulative calculations.

Drawing a Pareto Diagram in Excel: Two Methods Compared

Your Excel version determines which method works best. Excel 2016 and later include a dedicated Pareto chart type that creates the descending bars and cumulative line in one step. Older versions need a manual combination chart using a clustered column and a line on a secondary axis.

Method 1: Use the Built-In Pareto Chart (Excel 2016+)

Select any cell inside your data table, then go to Insert > Insert Statistic Chart and choose Pareto from the Histogram section. Excel inserts a chart with bars in descending order and an automatic cumulative percentage line on a secondary axis. A faster alternate route: click Insert > Recommended Charts, switch to the All Charts tab, select Histogram on the left, then pick the Pareto thumbnail. Either path gives the same result. Microsoft’s official documentation walks through this same workflow and confirms the Pareto chart lives inside the Histogram chart family.

Method 2: Build a Manual Combination Chart (Any Excel Version)

Start with your data sorted descending and add two helper columns. In the first cell of the Cumulative Count column, enter a formula that references the first frequency value. In the cell below, add that cell to the next frequency value, then drag the formula down to fill the rest. In the Cumulative % column, divide each cumulative count by the grand total using an absolute reference to the total cell, then drag down. Select your Category, Frequency, and Cumulative % columns (hold Ctrl to pick non‑adjacent ranges), then go to Insert > Column and choose Clustered Column. Right‑click the Cumulative % series, pick Change Series Chart Type, set it to Line with Markers, and check the Secondary Axis box. Finally, right‑click the right axis and set the Maximum to 100 so the line reaches the top of the chart.

Both methods produce the same visual when set up correctly — a descending bar set with a rising percentage line across the top.

How to Customize Your Pareto Chart?

Right‑click any element to open formatting options. To add data labels to the bars, click the + (Chart Elements) button next to the chart and check Data Labels. For axis titles that describe what each side measures, use the same + button and check Axis Titles. The left axis typically shows Count or Frequency, while the right axis shows Cumulative %. Give the chart a title that identifies the dataset and time period — right‑click the default title and type something specific. If your bars look clipped or the line doesn’t reach 100%, right‑click the percentage axis, open Format Axis, and set the Maximum boundary to 100 under Bounds.

Common Pareto Chart Mistakes and How to Fix Them

A Pareto diagram loses its meaning when the data isn’t sorted or the cumulative line isn’t calculated correctly. The bars must fall from highest to lowest, and the line must reflect the running percentage against the correct total.

Each mistake has a straightforward fix that takes less than a minute once you know where to look.

Reading Your Results: Where Is the 80/20 Breakpoint?

Find the point where the cumulative percentage line crosses roughly 80%. The categories to the left of that crossing are your “vital few” — the ones that together account for most of the total. In the example dataset, the first two categories (delivery delays and missing parts) reach about 67% of total frequency, and adding the third (incomplete paperwork) pushes past 80%. Those three categories are where corrective effort delivers the highest return. The remaining categories fall in the “useful many” — worth monitoring but not the same priority.

Final Reference: Steps to Your First Pareto Diagram

1. Organize categories and counts in two columns.
2. Sort the count column largest to smallest.
3. For Excel 2016+: Insert > Insert Statistic Chart > Pareto.
4. For older Excel: add Cumulative Count and Cumulative % helper columns, insert a Clustered Column chart, then change the cumulative series to a Line on the Secondary Axis.
5. Set the percentage axis maximum to 100 and add a clear chart title.
6. Read the chart where the cumulative line crosses 80% to identify priority categories.