Excel guide · 7 min read
How to make a Pareto chart in Excel
Sample data
Every result on this page was calculated from this sheet by the same spreadsheet engine the Cellwise app uses.
| Row | A | B | C | D | E | F | G |
|---|---|---|---|---|---|---|---|
| 1 | Return reason | Returns | Cumulative % | Group under | 15 | ||
| 2 | Wrong size | 142 | 0.37368421053 | Cut-off | 0.8 | ||
| 3 | Damaged in transit | 38 | 0.86315789474 | ||||
| 4 | Not as described | 61 | 0.76315789474 | ||||
| 5 | Changed mind | 87 | 0.60263157895 | ||||
| 6 | Late delivery | 12 | 0.95789473684 | ||||
| 7 | Wrong item sent | 24 | 0.92631578947 | ||||
| 8 | Defective | 9 | 0.98157894737 | ||||
| 9 | Missing parts | 7 | 1 |
Download this sheet as an Excel practice file (.xlsx)
Way 1: the built-in Pareto chart (Excel 2016 and later)
The sheet has one month of returns from an online store, 380 in all, in the order the returns system exported them. You don't need to sort them for the built-in chart: it sorts the bars from largest to smallest by itself and draws the cumulative percentage on a second axis on the right.
It is quick, but you can't change much. The bars always sort, so a catch-all bar can't be pinned at the end, there is no option for an 80% line, and it isn't offered as a PivotChart type: if your counts come from a PivotTable, copy them to a normal range first. Excel 2013 and earlier don't have it at all.
- Select A1:B9 (the reasons and the counts, with headers).
- Go to Insert > Insert Statistic Chart (the icon in the Charts group) and pick Pareto under Histogram. Or Insert > Recommended Charts > All Charts > Histogram > Pareto.
- Click the chart title and type a real title, such as "Returns by reason, November".
- Optional: right-click the bars > Add Data Labels to show the counts.
Way 2, step 1: a sorted table with the cumulative %
A hand-built chart reads the rows in the order they are on the sheet, so the source must be sorted largest first. In Excel 365 or 2021, let a formula do it so the chart never goes stale: SORTBY sorts the reasons and counts, and a running total over the grand total gives the cumulative share. The cumulative percentage guide explains the running SUM in detail.
The formula below spills a three-column table: reason, returns, cumulative %. Put it in an empty spot such as E5 and format the third column as Percent. In older Excel, sort the data with Data > Sort (Returns, Largest to Smallest) and fill =SUM($B$2:B2)/SUM($B$2:$B$9) down a helper column instead.
Column C on the sheet shows another way that needs no sorting: =SUMIF($B$2:$B$9,">="&B2)/SUM($B$2:$B$9) adds everything at least as big as the row. It works for flagging, but two reasons with the same count would share one figure, and the chart still needs sorted rows.
Result: spills 8 rows × 3 columns
| Wrong size | 142 | 0.37368421053 |
| Changed mind | 87 | 0.60263157895 |
| Not as described | 61 | 0.76315789474 |
| Damaged in transit | 38 | 0.86315789474 |
| Wrong item sent | 24 | 0.92631578947 |
| Late delivery | 12 | 0.95789473684 |
| Defective | 9 | 0.98157894737 |
| Missing parts | 7 | 1 |
Wrong size 142 (37.4%), Changed mind 87 (60.3%), Not as described 61 (76.3%), Damaged in transit 38 (86.3%), Wrong item sent 24 (92.6%), Late delivery 12 (95.8%), Defective 9 (98.2%), Missing parts 7 (100%).
Way 2, step 2: the combo chart
Excel has a ready-made combination for this: clustered columns for the counts and a line on a secondary axis for the cumulative %. The one setting people miss is the secondary axis maximum. Left on automatic, Excel may scale it to 120%, and the line no longer ends at the top right corner where a Pareto line belongs.
Set the gap width low (around 10% to 20%) so the bars sit close together; a Pareto chart reads as one block of causes, not separate items.
- Select the sorted reasons, returns and cumulative % columns, with headers.
- Go to Insert > Insert Combo Chart > Clustered Column - Line on Secondary Axis.
- Right-click the right-hand axis > Format Axis. Under Bounds set Minimum to 0 and Maximum to 1 (100%).
- Right-click a bar > Format Data Series and set Gap Width to about 15%.
- Right-click the line > Add Data Labels if you want the cumulative % on each point.
Add an 80% line and mark the vital few
The 80% line shows where "the few causes behind most of the problem" stops. Add a fourth column next to the sorted table that holds the cut-off for every row (=$G$2, which is 0.8), add it to the chart and put it on the secondary axis as a line. It draws a flat line across at 80%.
The reasons to work on are those that start below the cut-off: the cumulative share of the bars before them is under 80%. Count them with the formula below. Here it is 4: Wrong size, Changed mind, Not as described and Damaged in transit, which together cover 86.3% of returns. Wrong size alone is 37.4%, so a better size guide is worth more than everything after the fourth bar combined.
The 80% is a rule of thumb, not a law. Keep the cut-off in G2 so people can try 70% or 90%, and don't move it after seeing the result.
- In the column next to the sorted table type =$G$2 and fill it down to the last reason.
- Right-click the chart > Select Data > Add. Series name: the header; Series values: the new column. Click OK.
- Right-click the chart > Change Chart Type, set the new series to Line and tick Secondary Axis.
- Format the line as a dashed, thin line so it doesn't look like data.
Result: 4
4 reasons start below 80%: the four bars on the left. Together they cover 86.3% of the 380 returns.
Group the small categories into Other
With many small categories the right half of the chart turns into a row of slivers. Group everything below a threshold (G1, 15 returns) into one Other bar. By convention Other goes last even when it is bigger than the bar before it, because it isn't one cause.
The formula keeps the reasons at or above the threshold, sorted, and adds one Other row with the sum of the rest. Late delivery, Defective and Missing parts (12 + 9 + 7) become Other, 28. Point the combo chart at this table instead. Note that the built-in chart can't do this: it would sort Other (28) in front of Wrong item sent (24).
Result: spills 6 rows × 2 columns
| Wrong size | 142 |
| Changed mind | 87 |
| Not as described | 61 |
| Damaged in transit | 38 |
| Wrong item sent | 24 |
| Other | 28 |
Six rows: Wrong size 142, Changed mind 87, Not as described 61, Damaged in transit 38, Wrong item sent 24, Other 28.
Counts from a raw log, and which way to pick
Returns, defects and complaints usually start as a log with one row per event, not counts. Build the counts first: =UNIQUE(log_reasons) for the list and =COUNTIF(log_reasons,E2#) next to it, or a PivotTable with Reason in Rows and Reason again in Values (Count). Clean the reason text first: "Wrong size", "Wrong size " with a trailing space and "Too small" end up as separate bars even though they are one problem. TRIM fixes the spaces; a fixed list of reasons in Data Validation stops the rest at the source.
Use the built-in chart for a quick look or a one-off slide. Build the combo chart when the chart lives in a report that refreshes, needs an 80% line, an Other bar or a fixed axis, or has to open in Excel 2013. Either way, a Pareto chart is only as good as the categories: if 40% of returns say "Other", fix the reason codes before you chart them.
Common mistakes
Building the combo chart from unsorted data
The bars follow the sheet order, so the line no longer rises steeply on the left. Sort largest first, or chart a SORTBY spill so it stays sorted.
Leaving the secondary axis on automatic
Excel may run it to 120% and the line stops short of the top. Set the bounds to 0 and 1 in Format Axis.
Putting the cumulative % on the same axis as the counts
A 0-1 line next to counts in the hundreds lies flat along the bottom. Move the line series to the secondary axis.
Mixing differently spelled categories
"Wrong size" and "Wrong size " (trailing space) become separate bars. Clean the text with TRIM and use a dropdown list for the reason field.
FAQ
Where is the Pareto chart in Excel?
Insert > Insert Statistic Chart > Pareto, in Excel 2016 and later including Microsoft 365. It is listed under Histogram. Excel 2013 and earlier don't have it; build a combo chart instead.
How do I add an 80% line to a Pareto chart?
Add a column that holds 0.8 on every row, add it to the chart as a series, change it to a line and put it on the secondary axis. The built-in Pareto chart has no option for it, so use a combo chart.
Why does my Pareto line go above 100%?
It doesn't really: the secondary axis is set to automatic and runs past 100%, or the cumulative % uses an unlocked total. Fix the axis maximum at 1 and check that the last cumulative value is exactly 100%.
Can I make a Pareto chart from a PivotTable?
Not as a PivotChart. Sort the PivotTable largest to smallest, add % Running Total In as a second value, then copy the result to a normal range and chart that, or chart the PivotTable as a column chart and add the line yourself.
What is the 80/20 rule in a Pareto chart?
It's the rule of thumb that roughly 80% of the effect comes from 20% of the causes. Your data won't split exactly 80/20; the chart shows where your own cut-off falls.
Functions used
SUMIFAdds cells that matchSUMAdds numbersSUMPRODUCTMultiply then addSORTBYSorts by other columns, spillsINDEXValue at a positionHSTACKJoins arrays side by sideVSTACKStacks arrays one under anotherFILTERKeeps matching rows, spillsLETNames values inside a formulaUNIQUERemoves duplicates, spillsCOUNTIFCounts cells that match
Related guides
- How to calculate cumulative percentage in Excel
- ABC inventory analysis in Excel
- How to do a spend analysis in Excel
- How to make a running total (cumulative sum) in Excel
- How to make a control chart in Excel
- How to SUMIF across multiple columns in Excel
Updated