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

A Pareto chart is a bar and line graph combined that shows you which problems or categories cause the most impact. The bars represent individual items ranked from largest to smallest, and the line shows the running total as a percentage. The chart is named after the 80/20 principle — the idea that roughly 80 percent of your problems usually come from 20 percent of your causes.

In practice, you might use a Pareto chart to see which customer complaints happen most often, which products return most frequently, which tasks take up most of your time, or which expenses drain your budget. Once you can see the pattern, you know where to focus your effort for the biggest payoff.

Excel does not have a built-in Pareto chart type in older versions, but Excel 2016 and later include one as a standard option. If you have an older version, you can build one manually using a bar chart and a line chart layered together. Both methods take about five to ten minutes once you have your data organized.

Key Takeaways

  • Your data needs two columns: one for the category or problem name, and one for how many times it occurred or how much it cost.
  • Sort your data from highest to lowest count before creating the chart, or Excel will sort it for you during the chart creation process.
  • Excel 2016 and later have a built-in Pareto chart type under the "All Charts" menu that creates the bar and line graph automatically.
  • For older Excel versions, you can layer a bar chart and a line chart together by creating the bar chart first, then adding a secondary axis for the percentage line.
  • The line should reach roughly 80 percent at the point where the bars stop being tall — that is where your vital few causes are.

Organize your data in two columns

Start with a clean spreadsheet. Put your category names in column A and the count or amount in column B. For example, if you are tracking customer complaints, column A might list complaint types (shipping delay, wrong item, damaged package) and column B would show how many times each occurred (47, 23, 15). Do not include a total row — the chart will handle that.

Give each column a header in row 1. Call column A something like "Problem Type" or "Category" and column B something like "Count" or "Frequency". These headers will appear in your chart legend and make the chart readable to someone else later.

If your data is already in a spreadsheet, check that each row is one category and each value is a number, not text that looks like a number. If a count is stored as text ("47" instead of 47), Excel will not sort it correctly and your chart will look wrong.

Sort your data from highest to lowest

Click on any cell in your data range, then select all the data including headers. In Excel, go to the Data tab and click Sort. Choose to sort by column B (your count column) in descending order — highest to lowest. Click OK.

Your data should now show the largest count at the top and the smallest at the bottom. This order is what makes a Pareto chart work visually — you see when ready which causes matter most because they appear on the left side of the chart as the tallest bars.

If you skip this step, Excel 2016+ will still create a Pareto chart, but it will sort the data for you automatically during chart creation. Sorting first yourself gives you control and lets you double-check that the order makes sense.

Create a Pareto chart in Excel 2016 or later

Select your data range including headers. Go to the Insert tab at the top. Click Insert Statistic Chart (it may appear as a small dropdown arrow next to a chart icon). From the menu that opens, look for Pareto or Pareto Chart and click it.

Excel will create the chart when ready. The bars will show your categories in descending order, and a line will overlay them showing the cumulative percentage. The chart appears as an object on your spreadsheet — you can click and drag it to move it, or resize it by dragging the corners.

Right-click the chart to edit it. You can change the title by clicking Chart Title and typing a new name. You can also click on the bars or line to format their colors, or click the axis labels to change what they say. These changes are optional — the chart works fine as-is.

Build a Pareto chart manually in older Excel versions

If you have Excel 2013 or earlier, you will create the chart in two steps: first a bar chart, then add a line on top of it. Start by selecting your data including headers. Go to Insert and choose Column Chart (the basic bar chart type). Excel will create a chart with your categories on the x-axis and counts as vertical bars.

Now you need to add a line showing the cumulative percentage. First, create a helper column in your spreadsheet. In column C, add a header like "Cumulative %". In the first data row under that header, type a formula: =B2/SUM($B$2:$B$100) (adjust the range to match your actual data). In the next row, type =C2+B3/SUM($B$2:$B$100). Copy this second formula down to all remaining rows. This calculates what percentage of the total each row represents, then adds it to the previous row's percentage.

Click on your chart to select it. Go to Insert and choose Line Chart. A dialog will ask what data to use — select your cumulative percentage column (column C). The line will appear on top of the bars, but it will be scaled wrong because the bars go up to your highest count and the line goes up to 100 percent. Right-click the line and choose Format Data Series. Check the box for Secondary Axis. The line will now scale correctly from 0 to 100 percent on the right side of the chart.

Read and use your finished Pareto chart

Look at where the line crosses 80 percent. The bars to the left of that point are your vital few — the causes that drive most of your problem. The bars to the right are the trivial many — they exist but fixing them will not move the needle as much.

For example, if your Pareto chart shows that shipping delays and wrong items account for 78 percent of complaints, those are the two problems worth solving first. Damaged packages might be real, but it is a smaller piece of the pie. You can use this insight to decide where to spend time, money, or effort.

You can update the chart later by changing the numbers in your data columns. The chart will update automatically. If you add new categories, add them to the bottom of your list, re-sort the data, and the chart will refresh.

Frequently Asked Questions

Can I add more than two columns of data to a Pareto chart?

A standard Pareto chart shows one category column and one count column. If you have multiple counts you want to compare (for instance, complaints by month), you would create separate Pareto charts for each month rather than combining them into one chart. This keeps the visual straightforward and the message clear.

What if my categories are dates or time periods instead of problem types?

Pareto charts work with any category — dates, names, locations, product codes. Put the dates in column A and the count in column B, then follow the same steps. The chart will show you which time period had the most activity, which is useful for spotting trends.

Why does my line not reach 100 percent?

It should reach 100 percent at the far right of the chart. If it stops short, check that your helper column formula includes all your data rows in the SUM range. If you are using Excel 2016+ built-in Pareto, this should not happen — the chart calculates it for you.

Can I change the 80 percent threshold to a different number?

The 80/20 rule is a guideline, not a law. If your business finds that 70 percent of problems come from 30 percent of causes, that is still useful information. You can add a reference line to your chart by right-clicking the line and choosing to add a data label or by inserting a shape, but Excel does not have a built-in way to mark a specific threshold.

What if I want to show only the top five categories and group the rest as "Other"?

Create a new row at the bottom of your data called "Other" and sum all the counts from rows six onward into that cell. Then delete rows six through your original last row. Your chart will now show the top five categories plus an "Other" bar, which keeps the chart readable when you have many small categories.