What a Pareto chart does and why you'd use one

A Pareto chart combines a bar graph and a line graph to show which problems or categories matter most. The bars show how often something happens, ranked from highest to lowest. The line shows the running total as a percentage, so you can see how many of your problems come from just a few causes.

The most common use is the 80/20 rule: roughly 80 percent of your problems usually come from 20 percent of the causes. A Pareto chart makes that visible. If you're tracking customer complaints, equipment failures, or defects in a manufacturing process, a Pareto chart tells you where to focus first.

Excel doesn't have a built-in Pareto chart type in older versions, but newer versions (Excel 2016 and later) include one. If you have an older version, you can build one manually using a bar chart and a line chart layered together.

Key Takeaways

  • Excel 2016 and later have a Pareto chart option under Insert > Charts > All Charts, but older versions require manual construction.
  • Your data needs two columns: one for categories (like complaint types) and one for the count or frequency of each.
  • The chart automatically sorts categories from highest to lowest frequency and adds a cumulative percentage line.
  • You can adjust colors, axis labels, and the percentage threshold line to match your needs.
  • If you're using an older Excel version, combine a sorted bar chart with a secondary axis line chart to create the same effect.

Prepare your data in the right format

Start with two columns of data. The first column lists your categories—these could be types of complaints, machine names, product defects, or any other grouping. The second column shows the count or frequency for each category. Do not include totals or subtotals in your data range; Excel will calculate those automatically.

Your data should look like this: one row of headers (Category, Count) and then one row per item. You don't need to sort it yourself—the Pareto chart will do that. However, make sure your frequency numbers are actual numbers, not text that looks like numbers.

Select the entire data range including headers before you insert the chart. Click the first cell of your category column and drag to the last cell of your count column.

Insert a Pareto chart in Excel 2016 or later

With your data selected, go to the Insert tab at the top of the ribbon. Click Charts (or Chart depending on your Excel version). A dropdown menu appears showing chart types.

Look for All Charts or More Charts at the bottom of the list. Click it to open the full chart gallery. On the left side, find Histogram (in some versions it's labeled under a different category). Scroll down in the chart subtypes until you see Pareto. Click it, then click OK.

Excel inserts the chart into your worksheet. The bars are sorted from highest to lowest, and a blue line shows the cumulative percentage. The chart is now functional, but you'll probably want to adjust the title and labels.

Customize the chart title and axis labels

Right-click the chart title (usually "Chart Title" by default) and select Edit Text. Type a new title that describes what you're measuring—for example, "Customer Complaints by Type" or "Equipment Failures This Quarter".

Right-click the vertical axis on the left (the one showing counts) and select Format Axis. You can adjust the maximum value, add gridlines, or change the number format. Right-click the horizontal axis at the bottom to rename the category labels if needed.

The line on the right side shows cumulative percentage. You can right-click it to change its color or thickness. Most people leave it blue or change it to red so it stands out from the bars.

Build a Pareto chart manually in older Excel versions

If you have Excel 2013 or earlier, you'll create a Pareto chart by combining two separate charts. First, sort your data from highest count to lowest. Select your data, go to Data > Sort, and sort by the count column in descending order.

Next, create a helper column to calculate cumulative percentages. In a third column, add a formula that divides the running total by the grand total. For example, if your counts are in column B and you're in row 2, the formula might be =SUM($B$2:B2)/SUM($B$2:$B$10)*100. Copy this formula down for every row.

Select your category column and count column, then insert a Column Chart. Once the chart appears, right-click the data series (the bars) and select Change Chart Type. Change it to a Bar Chart if you prefer horizontal bars, or keep it as columns.

Now right-click the chart again and select Select Data. Click Add to add a new data series. Point it to your cumulative percentage column. Once added, right-click that new series and select Change Series Chart Type. Change it to a Line Chart. Click the dropdown for Secondary Axis and select Yes so the percentage line uses the right-side axis instead of the left.

Adjust the secondary axis for the percentage line

After you've added the line series on a secondary axis, the chart may look crowded or the scales may not match. Right-click the secondary axis (the one on the right side) and select Format Axis. Set the maximum value to 100 since percentages go from 0 to 100.

Set the primary axis (left side) maximum to a value that makes sense for your data. If your highest count is 47, you might set the maximum to 50 so there's a little breathing room. This makes both axes readable and the chart easier to interpret.

You can also add a horizontal line at 80 percent to show the "80/20 rule" threshold. Right-click the secondary axis and add a gridline at 80 if your charting software supports it, or manually draw a line by inserting a shape.

Common mistakes and how to fix them

The most common mistake is including a total row in your data. If you do, it shows up as a category and throws off the entire chart. Delete any total or summary rows before you create the chart.

Another mistake is forgetting to sort your data before building a manual Pareto chart in older Excel versions. The bars must go from highest to lowest for the chart to make sense. If you see them in random order, go back to your data and sort by count in descending order.

If the percentage line is hard to see, it may be hidden behind the bars. Right-click the line and select Format Data Series. Increase the line width to 2 or 3 points and change the color to something that contrasts with the bars.

If your cumulative percentage line doesn't reach 100 percent, check your helper column formula. The denominator should be the sum of all counts, not a moving target. Use absolute references (dollar signs) for the total sum so it doesn't change as you copy the formula down.

Frequently Asked Questions

Can I change the order of the bars after the chart is created?

In Excel 2016 and later, the Pareto chart automatically sorts from highest to lowest and updates if you change the data. In a manual chart, you need to re-sort your source data. The chart will update automatically once the data is sorted correctly.

What does the 80/20 rule mean on a Pareto chart?

It's a rough guideline that 80 percent of your problems usually come from 20 percent of your causes. A Pareto chart shows this visually: the line climbs steeply at first (the vital few causes) and then flattens out (the trivial many). You can use this to decide where to focus your effort.

How do I add a threshold line at 80 percent?

In Excel 2016 and later, right-click the chart and select Add Chart Element > Lines > High-Low Lines or use a shape to draw a horizontal line. In older versions, you can insert a line shape manually or add a reference line through the axis formatting options.

What if my data changes—does the chart update automatically?

Yes. If you change the counts in your source data, the chart updates immediately. In Excel 2016 and later, the Pareto chart re-sorts automatically. In a manual chart, make sure your formulas are set up correctly so the cumulative percentages recalculate when counts change.

Can I export a Pareto chart to PowerPoint or Word?

Yes. Right-click the chart in Excel and select Copy. Then paste it into PowerPoint or Word. The chart will paste as an image by default, but you can right-click it in Word or PowerPoint and choose to keep it linked to the Excel file if you want updates to show in the presentation.