What a Pareto chart does and when to use one
A Pareto chart is a bar and line graph combined that shows you which problems or categories matter most. The bars display how often something happens, ranked from highest to lowest. The line shows what percentage of the total each bar represents, climbing toward 100 percent as you move right. The point is to spot the few things causing most of your trouble — so you stop wasting effort on what barely matters.
You build one in Excel using a regular column chart, then add a second data series as a line. It takes about ten minutes once you understand the steps. The chart works best when you have a list of categories with counts — complaints by type, defects by cause, time spent on tasks, files organized by size. If you are trying to show trends over time or compare two unrelated groups, a Pareto chart is the wrong tool.
The name comes from the 80/20 rule: typically 80 percent of problems come from 20 percent of causes. A Pareto chart makes that visible so you know where to focus.
Key Takeaways
- Sort your data from highest count to lowest before you start, because Excel will not do this automatically when you insert a chart.
- Create two helper columns: one for the running total and one for the percentage of the grand total, then hide them before you finish.
- Insert a column chart first, then add a line series on a secondary axis so the percentages do not flatten the bar heights.
- Format the line to show data labels so viewers can read the exact percentages at each step.
Prepare your data and sort it correctly
Start with a straightforward two-column list: one column for the category name, one for the count. For example, you might have complaint types in column A and the number of complaints in column B. Do not worry about order yet — just get the raw data into the sheet.
Now sort the data by count, highest to lowest. Click any cell in your data range, then go to the Data tab and click Sort. Choose to sort by the count column in descending order. Excel will keep the category names paired with their counts as it rearranges. This step is critical because a Pareto chart only makes sense when the bars descend from left to right.
Once sorted, add a header row if you do not have one. Call column A "Category" and column B "Count" so the chart will label itself correctly.
Build the running total and percentage columns
You need two helper columns that the chart will read but the viewer will not see. In column C, you will calculate the running total — the sum of all counts from the top down to each row. In column D, you will calculate what percentage of the grand total each running total represents.
Click cell C1 and type a header: "Running Total". In C2, type =B2 (just the first count). In C3, type =C2+B3 (the previous running total plus the current count). Copy this formula down to the last row of data. Each cell will now show the cumulative sum up to that row.
In column D, add the header "Percentage". In D2, type =C2/C$ (the last row number) — for example, if your data ends at row 10, type =C2/C$10. The dollar sign locks that bottom cell so it does not change when you copy the formula down. Copy this formula to all rows. Format column D as percentage so it shows 45% instead of 0.45.
Insert the column chart
Select the range that includes your Category column (A) and Count column (B), but not the helper columns yet. Include the headers. Go to the Insert tab and click Column Chart, then choose the basic clustered column type. Excel will create a chart with categories along the bottom and bars showing the counts.
The chart will appear on your sheet. Right-click it and choose Edit Data or double-click to enter edit mode. You should see the chart selected with a blue border. Now you need to add the percentage line on top of these bars.
Add the percentage line as a second series
While the chart is in edit mode, right-click on one of the bars and select Select Data. The Select Data Source dialog will open. Click Add to add a new data series. In the dialog that appears, give it a name — type "Percentage" — then set the data range to your D column (the percentages you calculated). Click OK twice to close the dialogs.
Your chart now has two series: the bars and the percentages. But the percentages are probably invisible or crushed because they are on the same scale as the counts. You need to move the percentage series to a secondary axis. Right-click the line (or the data points if you see them) and choose Format Data Series. In the dialog, find the option for Secondary Axis and check it. The line will now climb the right side of the chart on its own 0-100 scale while the bars stay on the left.
Format the line and clean up the chart
The line should now be visible, climbing from lower left to upper right. Right-click the line and choose Format Data Series. Set the line color to something visible — dark gray or black works well. Then find the Data Labels option and turn it on so the percentage appears at each point on the line. This lets viewers read the exact percentage without guessing.
Now hide the helper columns. Right-click on column C header, select Hide, then do the same for column D. The chart will still use the data, but the columns will not appear on the sheet. Add a title to the chart by clicking it, then typing in the title box that appears. Something like "Complaint Types by Frequency" or "Defects by Root Cause" tells the viewer what they are looking at.
Right-click the chart and choose Exit Edit Mode or click outside it to finish. Your Pareto chart is complete.
Frequently Asked Questions
What if my percentages do not reach 100 percent on the right side?
Check that your running total formula goes all the way to the last row of data. If the final running total does not equal the sum of all counts, you have a gap. Also make sure the percentage formula divides by the last cell in the running total column, not a fixed number.
Can I add a target line to show the 80 percent threshold?
Yes. Add another data series with a single value of 0.8 (or 80 if your axis goes to 100), then format it as a line instead of bars. This makes the 80/20 rule visible on the chart itself.
Why does my chart show the categories in the wrong order?
You did not sort the original data by count before inserting the chart. Delete the chart, sort your data from highest to lowest count, then insert a new chart. Excel will not reorder categories automatically.
How do I update the chart if my counts change?
Edit the numbers in column B. The running totals and percentages will recalculate automatically, and the chart will update. If you add new rows, extend the formulas in columns C and D to include them, then update the percentage formula to divide by the new final total.