How to Create a Pareto Chart in Excel (Step-by-Step Guide)

A Pareto chart combines a bar chart and a line graph to show which factors contribute most to a problem or outcome. It's built on the 80/20 principle — the idea that roughly 80% of effects come from 20% of causes. In Excel, you can build one natively or manually, and knowing the difference matters depending on your version and what level of control you need.

What a Pareto Chart Actually Shows

The bars are arranged in descending order from left to right — largest contributor first, smallest last. The line overlaid on top tracks the cumulative percentage, climbing from left to right until it reaches 100% at the far right.

This makes it easy to spot visually: wherever the cumulative line crosses the 80% mark, everything to the left of that point represents your most significant causes. That's the insight the chart is designed to surface.

Method 1: Using Excel's Built-In Pareto Chart (Excel 2016 and Later)

If you're running Excel 2016, 2019, 2021, or Microsoft 365, Excel includes a native Pareto chart type. This is the fastest route.

Steps:

1. Enter your data in two columns — Category in column A, Frequency or Value in column B. Don't sort it yet; Excel handles that automatically.
2. Select both columns, including headers.
3. Go to the Insert tab on the ribbon.
4. Click Insert Statistic Chart (the icon with a histogram).
5. Select Pareto from the dropdown.

Excel will automatically sort the bars in descending order and overlay the cumulative percentage line. You can then format the chart — axis labels, colors, titles — using the standard chart formatting tools.

What you can customize:

• Chart title and axis labels via Chart Design > Add Chart Element
• Bar colors by right-clicking individual bars
• The secondary axis (cumulative % line) scale, if needed, by double-clicking the axis

Method 2: Building a Pareto Chart Manually (Any Excel Version) 📊

If you're on an older version of Excel or need more control over the output, you can construct the chart from scratch using a sorted data table.

Steps:

1. Sort your data in descending order by value (largest to smallest).
2. Add a cumulative percentage column. In the first row, divide the first value by the total sum. In the second row, add the first cumulative value to the second row's percentage, and so on. Formula example for cell C2: =B2/SUM($B$2:$B$10) and for C3 onward: =C2+(B3/SUM($B$2:$B$10))
3. Select the Category, Value, and Cumulative % columns.
4. Insert a Clustered Column chart from the Insert tab.
5. Click on the cumulative % data series in the chart, right-click, and choose Change Series Chart Type.
6. Set the cumulative % series to Line and check the box to plot it on a Secondary Axis.
7. Format the secondary axis to run from 0 to 1 (or 0% to 100% if formatted as percentage).

This manual approach gives you full control over bin groupings, color schemes, and axis scaling — useful when presenting to stakeholders with specific formatting requirements.

Key Variables That Affect Your Results

Not every Pareto chart setup works equally well. Several factors shape what you get:

Common Issues and How to Avoid Them

The cumulative line doesn't reach 100% This usually means your cumulative percentage formula has an error in the range reference. Check that your SUM range is anchored with absolute references ($).

Bars aren't sorting automatically The native chart type sorts on insert, but if you edit the source data afterward, it may not re-sort. You may need to re-insert or manually reorder your source data.

Secondary axis is missing or misaligned When building manually, the secondary axis scale must be set to match your percentage range. If cumulative values are decimals (0 to 1), format the axis as a percentage. If they're already multiplied by 100, set the axis max to 100.

Too many small categories cluttering the chart ⚠️ Consider grouping low-frequency items into an "Other" category. This keeps the visual clean and makes the 80/20 split easier to identify.

How Skill Level and Use Case Change the Approach

A data analyst running recurring reports will likely want the manual method — it's reproducible, customizable, and easier to embed in a template. Someone doing a one-off quality control review might find the built-in chart type faster and sufficient.

If you're working in a shared environment where colleagues use older Excel versions or Google Sheets, the manual method also travels better — the built-in Pareto type doesn't translate cleanly across platforms.

The right approach also depends on how much visual customization your output requires. Default Excel Pareto charts are functional but minimal. Whether that's acceptable depends entirely on where the chart is going and who's reading it. 📈