How to Create a Pareto Graph in Excel: A Step-by-Step Guide

A Pareto graph combines a bar chart and a line chart to show which factors contribute most to a problem or outcome. Based on the 80/20 principle — the idea that roughly 80% of effects come from 20% of causes — it's a staple tool in quality control, business analysis, and data management. Excel makes it possible to build one, though the exact path depends on which version you're running.

What a Pareto Graph Actually Shows

A Pareto chart displays categories ranked from highest to lowest frequency or impact, represented as bars. Overlaid on those bars is a cumulative percentage line that rises from left to right, reaching 100% at the far right. The point where the line crosses roughly 80% tells you which small group of categories drives most of your results.

This makes it useful for:

• Identifying the top causes of customer complaints
• Spotting which file types or data sources consume the most storage
• Prioritizing bugs, errors, or inefficiencies in a workflow

📊 The visual format is designed to make prioritization obvious at a glance — something raw tables can't do as efficiently.

Two Ways to Build a Pareto Chart in Excel

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

If you're running Excel 2016, 2019, 2021, or Microsoft 365, the Pareto chart is a native chart type. Here's how it works:

1. Enter your data — two columns: one for categories (e.g., error types, file categories), one for values (e.g., frequency or count). No need to sort in advance.
2. Select your data range — highlight both columns including headers.
3. Insert the chart — go to the Insert tab → click Recommended Charts or the Statistical Chart dropdown → select Pareto.
4. Excel handles the rest — it automatically sorts bars from largest to smallest and overlays the cumulative percentage line.

From there, you can format the chart title, axis labels, colors, and data labels through the Chart Design and Format tabs. Right-clicking any element opens contextual formatting options.

This method requires minimal manual setup. The trade-off is less control over exactly how the cumulative line is calculated or displayed.

Method 2: Manual Build (Excel 2013 and Earlier, or for Custom Control)

Older versions of Excel — 2013, 2010, or 2007 — don't include the native Pareto chart type. You build it manually using a combination chart. This method also gives advanced users more flexibility regardless of version.

Step 1: Prepare your data

• Sort categories descending by frequency (largest to smallest)
• Add a Cumulative % column: each row adds its frequency to all previous rows, then divides by the total

Step 2: Create a clustered column chart

Select your Category and Frequency columns → Insert → Column/Bar Chart → Clustered Column.

Step 3: Add the cumulative percentage as a second data series

• Right-click the chart → Select Data → Add a new series using your Cumulative % values
• This will appear as another set of bars initially

Step 4: Change the cumulative series to a line

• Click on the cumulative bars → right-click → Change Series Chart Type
• Set the Frequency series to Clustered Column, the Cumulative % series to Line
• Check the Secondary Axis box for the Cumulative % series

Step 5: Format the secondary axis

Right-click the right-side (secondary) axis → Format Axis → set the maximum to 1.0 (representing 100%).

This gives you a fully functional Pareto chart with independent control over every element.

Key Variables That Affect Your Process

The right approach depends on several factors:

• Excel version — the native chart type is only available from 2016 onward; older versions require the manual method
• Data size and complexity — larger datasets may need additional cleanup before the chart renders usefully
• Purpose of the analysis — a rough visual for internal use needs less polish than a chart going into a formal report or presentation
• Technical comfort level — the built-in method is faster but less flexible; the manual method rewards users familiar with combination charts and secondary axes
• Output format — if the chart will be embedded in a shared Excel file, exported to PDF, or dropped into PowerPoint, formatting decisions (font sizes, colors, axis labels) matter more

Common Mistakes to Avoid

Not sorting data before using the manual method — unsorted data produces a meaningless chart. The bars must run highest to lowest for the Pareto principle to be readable.

Forgetting the secondary axis — without setting the cumulative percentage line to a secondary axis, it will be compressed against the bottom of the chart and appear nearly flat.

Mixing up raw counts and percentages — your frequency column should contain actual values (not percentages). The cumulative percentage column is calculated separately.

Leaving axis maximums unchecked — the secondary axis should max at exactly 1.0 (100%), or the line won't reach the top-right corner of the chart as expected.

What Changes Based on Your Data

The same chart structure behaves differently depending on what you're analyzing. 🔍 A dataset with one dominant category will show a steep early rise in the cumulative line before it flattens — confirming a strong 80/20 effect. A more evenly distributed dataset produces a gentler curve, suggesting the causes are roughly equal in impact and harder to prioritize.

That shape — the steepness of the cumulative line — is often more informative than the bars alone. It tells you whether focusing on the top one or two categories will meaningfully move the needle, or whether you're facing a more distributed problem that requires a broader response.

How useful that insight turns out to be depends entirely on the nature of your data, what question you're trying to answer, and how much precision your analysis actually requires.