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

A Pareto chart combines a bar chart and a line graph to help you identify 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 — Pareto charts are widely used in quality control, business analysis, and project management. Excel makes building one surprisingly straightforward, though the exact steps vary depending on your version.

What a Pareto Chart Actually Shows

Before building one, it helps to understand what you're looking at. A Pareto chart displays:

• Bars arranged in descending order, each representing a category (defect type, complaint reason, cost driver, etc.)
• A cumulative percentage line that rises from left to right, reaching 100% at the far right

The point where the line crosses roughly 80% marks the categories responsible for the majority of your issue. That visual cutoff is what makes the chart actionable.

Method 1: 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 — no manual setup required.

Step 1: Prepare your data Enter your data in two columns:

• Column A: Category labels (e.g., defect types, error codes, complaint categories)
• Column B: Numeric values (e.g., frequency counts, costs, occurrences)

You don't need to sort the data first — Excel handles that automatically.

Step 2: Select your data Highlight both columns, including headers.

Step 3: Insert the chart Go to Insert → Charts → Insert Statistic Chart → Pareto. The chart icon looks like a bar chart with a diagonal line overlay.

Step 4: Review and customize Excel automatically sorts bars in descending order and draws the cumulative percentage line. From here you can:

• Edit the chart title by clicking on it
• Adjust axis labels via Format Axis
• Change bar colors using Format Data Series

That's it. For most users on modern Excel versions, this method takes under two minutes.

Method 2: Manual Pareto Chart (Excel 2013 and Earlier)

Older versions don't include a built-in Pareto option, so you'll need to construct it manually using a combo chart.

Step 1: Organize and sort your data Set up three columns:

• Column A: Category labels
• Column B: Frequency/values — sorted from highest to lowest
• Column C: Cumulative percentage

Step 2: Calculate cumulative percentages In C2, enter: =B2/SUM($B$2:$B$10) (adjust the range to match your data) In C3, enter: =C2+B3/SUM($B$2:$B$10) Drag C3 down to the last row. Format Column C as a percentage.

Step 3: Create the initial chart Select Columns A and B → Insert a Clustered Column chart.

Step 4: Add the cumulative percentage as a second series

• Right-click the chart → Select Data → Add
• Set the series values to your Column C range
• Click OK

Step 5: Change the new series to a line on a secondary axis

• Click the cumulative percentage bars in the chart
• Right-click → Change Series Chart Type
• Select Line for that series
• Check Secondary Axis

Step 6: Format and label Adjust the secondary Y-axis maximum to 1.0 (representing 100%) and format as a percentage. Add axis titles and a chart title for clarity.

Key Variables That Affect Your Results

How your Pareto chart looks — and how useful it is — depends on several factors worth thinking through before you build:

Common Mistakes to Avoid

Using percentages as your raw input values — the chart expects raw counts or totals, not pre-calculated percentages, unless you're building the cumulative line manually.

Too many categories — if you have 30+ categories, consider grouping the smallest ones into an "Other" bucket. A Pareto chart loses clarity when the bars become too narrow to read.

Not locking the SUM range — in the manual method, forgetting to use absolute references ($B$2:$B$10) in your cumulative percentage formula is a common formula error that breaks the calculation as you drag down.

Skipping the secondary axis — without a secondary Y-axis, the cumulative percentage line will appear nearly flat at the bottom of the chart because its scale (0–100%) conflicts with the raw value scale of the bars.

When a Pareto Chart Is and Isn't the Right Tool 📈

Pareto charts work best when:

• You have categorical data with countable occurrences
• You're trying to prioritize where to focus limited resources
• Your goal is to find the vital few causes rather than document everything equally

They're less useful when categories are roughly equal in frequency — the 80/20 split simply won't emerge, and a standard bar chart may communicate the data more honestly.

They also don't work well with continuous data (like temperature readings or timestamps) without first grouping that data into discrete categories.

What Varies by User and Setup

The right approach — built-in chart vs. manual combo method — depends on your Excel version, which isn't always obvious if you're working across multiple machines or shared environments. Beyond that, the structure of your dataset (how many categories, what the values represent, whether you've already cleaned the data) shapes how much preparation work the chart actually requires before it tells a meaningful story.

Whether the 80/20 split holds cleanly in your data, or whether you find a 70/30 or 90/10 pattern instead, is something only your specific dataset will reveal.