How to Make a Pareto Chart in Excel (the Modern Way)

If you’re running Excel 2016 or later — and if you’re on Microsoft 365, you are — making a Pareto chart takes four clicks. Select your data, go to Insert, click Insert Statistic Chart, choose Pareto. Done. Excel sorts the categories, calculates the cumulative percentages, and draws the line for you.

That’s the whole modern method, and for most business reports it’s all you need. The reason this article isn’t four sentences long is that the native chart has real limitations you should know about before your quarterly review, and there’s a manual method worth learning for the cases where the built-in version lets you down. We’ll cover both — but unlike most tutorials still circulating, we lead with the method you should actually use.

What a Pareto Chart Is (and When to Reach for One)

A Pareto chart is a column chart sorted from largest to smallest, with a cumulative percentage line running above it. It exists to answer one question: which few causes account for most of the effect?

That’s the 80/20 rule — the Pareto principle — in chart form. Classic uses:

Which defect types cause most of your production failures
Which complaint categories generate most of your support volume
Which expense lines eat most of your budget
Which products drive most of your revenue

If your data is categories with counts or totals, and your goal is prioritization — deciding where to focus first — the Pareto chart is the right call. If you just want to compare categories without the cumulative story, a plain sorted bar chart is cleaner. (Our chart decision guide covers when each one earns its place.)

The Modern Method: Excel’s Native Pareto Chart

You need two columns: category labels and their values. That’s it. The data doesn’t even need to be sorted — Excel handles that.

Sample data (customer complaints for a month):

HomeInsertPage LayoutFormulas

142

Complaint Type	Count
Late delivery	142
Damaged item	38
Wrong item shipped	27
Billing error	19
Poor packaging	12
Rude support	8
Website issue	6
Other	4

Step-by-Step

Click any cell inside your data. You don’t need to select the whole range — Excel will detect the table. If your data sits next to other content, select the two columns explicitly to be safe.
Go to the Insert tab.
In the Charts group, click Insert Statistic Chart — the icon that looks like a small histogram.
Under Histogram, choose Pareto.

Excel inserts the chart: columns sorted descending, cumulative percentage line on a secondary axis scaled 0–100%. If you’d rather browse, the same chart lives under Insert > Recommended Charts > All Charts > Histogram > Pareto.

Rename the chart title. Click it and type something that states the finding, not the chart type. “Late Delivery Drives 60% of Complaints” beats “Complaint Pareto Chart” in every meeting you’ll ever attend.

That’s the build. Reading it is just as fast: find where the cumulative line crosses 80%, and every column to the left of that point is where your attention belongs. In the sample data, late delivery alone is more than half the problem — which is exactly the kind of thing a Pareto chart makes impossible to ignore.

One Behavior Worth Knowing

If you select two numeric columns instead of one text column and one numeric column, Excel treats your data as a histogram and groups it into bins. That’s occasionally useful (Pareto of value ranges), but if your chart comes out with strange bracketed labels like [0, 25], that’s what happened. Right-click the horizontal axis, choose Format Axis, and switch the bin settings to By Category.

Where the Native Chart Falls Short

Here’s what the four-click chart won’t do — and this is the part most tutorials skip:

No data labels on the cumulative line. You can label the columns, but not the percentage line. If your audience needs to see “78%” printed at a specific point, the native chart can’t do it.
No markers on the line. You can’t add point markers, so it’s harder to read exactly where each category lands on the cumulative scale.
Limited series formatting. The native Pareto is a statistic chart, not a standard combo chart, so several formatting options you’d expect are simply absent.
No dynamic interaction with the source sort. Excel sorts internally; your source table stays as-is. Usually fine, occasionally confusing when the chart order doesn’t match the sheet.

For a chart that goes in a deck and gets glanced at, none of this matters. For a chart a quality team will study line by line, it might. That’s when you build it manually.

The Manual Method (for Older Excel or Full Control)

The manual approach is a combo chart: sorted columns plus a cumulative percentage line you calculate yourself. Old tutorials make you sort by hand and drag fill formulas around. With dynamic arrays (Excel 2021 and Microsoft 365), the prep is two formulas.

Step 1: Sort the Data with SORT

Suppose your raw data is in A2:B9 (labels and counts). In D2, enter:

=SORT(A2:B9, 2, -1)

This spills a copy of your table sorted by the second column, descending. Change the source data and the sorted copy updates itself — no re-sorting, ever.

Older Excel fallback: no SORT function? Copy the table and sort it manually via Data > Sort, largest to smallest on the count column. It works; you’ll just need to redo it when the numbers change.

Step 2: Add the Cumulative Percentage

In F2, next to the first sorted row, enter:

=SUM($E$2:E2)/SUM($E$2:$E$9)

(assuming the sorted counts spilled into column E). Fill down. Format the column as a percentage. The last row should read 100% — if it doesn’t, check your ranges.

Step 3: Build the Combo Chart

Select the sorted labels, counts, and cumulative percentage columns.
Insert > Insert Combo Chart > Create Custom Combo Chart.
Set the count series to Clustered Column.
Set the cumulative series to Line, and check Secondary Axis.
Click OK, then right-click the secondary axis, choose Format Axis, and set the Maximum to 1.0 (100%) so the line scales correctly.

Now you have a Pareto chart with everything unlocked: line markers, data labels anywhere, full formatting control. It’s ten minutes instead of four clicks, but it’s your chart.

If you build Pareto charts regularly with your company’s formatting, save the finished design as a chart template — a .crtx file — and apply it in two clicks next time. We cover how .crtx chart templates work separately, including free downloads.

Common Problems (and Fast Fixes)

The chart didn’t sort my categories. You probably built a standard column chart, not a Pareto. Native Pareto sorts automatically; a regular chart plots data in sheet order. Rebuild via Insert Statistic Chart, or sort the source data.

My percentages exceed 100%. In the manual method, your SUM denominator is wrong — check that the absolute range covers exactly the data rows, no totals row included.

The chart isn’t picking up new rows. Native statistic charts reference a fixed range. Convert your source data to an Excel Table (Ctrl+T) before creating the chart, or see our full guide to Excel charts not updating — the fixes there apply directly.

The cumulative line looks flat. The secondary axis maximum is probably auto-scaled beyond 100%. Set it to 1.0 manually.

Can Copilot Build This for You?

If you have Copilot in Excel, “create a Pareto chart of complaints by type” will generally get you a usable chart — Copilot handles the sorting and cumulative math the same way the native chart type does, and for a quick look at the data it’s genuinely faster than the ribbon. Where it currently earns less trust is fine formatting control, which is exactly where the native chart is weak too. Our complete guide to Copilot charts in Excel covers what it does well and where you should take over.

The Verdict

Use the native Pareto chart. It’s fast, it’s correct, and its limitations don’t matter for 80% of business uses — fittingly. Build the manual combo version only when you need line labels, markers, or formatting the native chart refuses to give you, and use SORT to do it the modern way rather than hand-sorting like it’s 2014. Either way, the chart’s job is the same: to make your biggest problem impossible to look away from.

FAQ

What Excel versions have the built-in Pareto chart? Excel 2016 and later on Windows and Mac, including Excel 2019, 2021, 2024, and Microsoft 365. Excel 2013 and earlier require the manual combo-chart method.

Does my data need to be sorted before making a Pareto chart? Not for the native chart — Excel sorts it automatically. For the manual method, yes, which is what the SORT formula handles.

What does the line on a Pareto chart show? The cumulative percentage of the total as you move left to right through the sorted categories. Where it crosses 80% marks the boundary of your “vital few.”

Can I make a Pareto chart from a PivotTable? Not directly — the native Pareto chart type isn’t available as a PivotChart. Copy the PivotTable output to a regular range (or reference it with formulas) and build from there.

Pareto chart vs. bar chart — what’s the difference? A Pareto chart is a bar chart with two extra rules: bars sorted descending, plus a cumulative percentage line. Use Pareto when the point is prioritization; use a plain bar chart for simple comparison.