Pareto analysis ranks items from largest to smallest and adds a running share of the total, to show how few items make up most of it. This page works it on ten customers in Excel, with the running-share and cut-off formulas, the built-in Pareto chart, the checks that prove the table, and how it differs from ABC analysis.
Pareto analysis ranks items from largest to smallest and adds a running share of the total, so you can see how few items make up most of it. In the ten-customer example below, 4 customers hold 78.6% of $1,400,000 in revenue and the fifth takes the running share to 85.4%. That is the 80/20 rule measured, not assumed.
It splits a list into the vital few, the items that together make up most of the total, and the useful many, the long list that makes up the rest. The few deserve named attention: the largest customers, the suppliers behind most of the spend, the causes behind most of the defects.
Share = item value / total
Running share = sum of values from the top down to this row / total
The single figure that sums it up is the top-N share: what share of the total the largest N items hold.
One row per item, with one value: revenue per customer, spend per supplier, defects per cause. If the export has one row per invoice or per ticket, aggregate first with a PivotTable or SUMIFS, so that each customer appears once. Leave credit memos netted into the customer's total, not as separate rows.
| Column | Content |
|---|---|
| A | Item: customer, product, supplier or cause |
| B | Value for the period: net revenue, spend or count |
One fiscal year, net revenue per customer, sorted from largest to smallest.
| Customer | Revenue (USD) | Share | Running share | Share of customers |
|---|---|---|---|---|
| Northgate Foods | 412,000 | 29.4% | 29.4% | 10% |
| Hallam Retail | 298,000 | 21.3% | 50.7% | 20% |
| Brightwater Hotels | 236,000 | 16.9% | 67.6% | 30% |
| Cole & Sons Co. | 154,000 | 11.0% | 78.6% | 40% |
| Delta Catering | 96,000 | 6.9% | 85.4% | 50% |
| Eastside Cafes | 62,000 | 4.4% | 89.9% | 60% |
| Fenwick Schools | 48,000 | 3.4% | 93.3% | 70% |
| Garnet Fitness | 38,000 | 2.7% | 96.0% | 80% |
| Harbor Clubs | 31,000 | 2.2% | 98.2% | 90% |
| Ivy Senior Living | 25,000 | 1.8% | 100.0% | 100% |
| Total | 1,400,000 | 100.0% |
Read it from the two right-hand columns. The top 20% of customers hold 50.7% of revenue, not 80%. The top 40% hold 78.6%, and the fifth customer takes the running share past 80%, to 85.4%. The data rarely splits at exactly 80/20, and this base is less concentrated than the rule suggests.
Customers in A2:A11, revenue in B2:B11.
1. Sort descending. Use Data > Sort, largest to smallest on column B, or leave the source alone and spill a sorted copy with SORTBY in Excel for Microsoft 365, 2024 or 2021, where -1 means descending:
=SORTBY(A2:B11,B2:B11,-1)
2. Share in C2, filled down:
=B2/SUM($B$2:$B$11)
3. Running share in D2, filled down. The first reference is anchored and the second moves, so each row sums from the top to itself:
=SUM($B$2:B2)/SUM($B$2:$B$11)
4. Share of customers in E2, filled down, the rank as a share of the count:
=(ROW()-1)/COUNT($B$2:$B$11)
5. The 80% flag in F2, filled down. It marks every row up to and including the one that crosses 80%, by testing the running share before this row:
=IF(D2-C2<0.8,"Vital few","Useful many")
That flags five customers: the four that reach 78.6% and Delta Catering, which crosses the line. If you want only the rows wholly inside 80%, use =IF(D2<=0.8,...) and you get four. Pick one rule, write it next to the table, and keep it from period to period. For one figure per customer from a raw invoice list, SUMIFS in Excel builds column B.
In Excel for Microsoft 365, Excel 2024 and Excel 2021, select A1:B11 and choose Insert > Insert Statistic Chart, then Pareto under Histogram. Microsoft describes the chart as columns sorted in descending order plus a line for the cumulative total percentage. On a Mac the same chart is under the Insert tab's Statistical chart icon. It sorts for you, so the source does not need to be sorted first.
In older versions, build it as a combo chart: revenue (column B) as clustered columns, running share (column D) as a line on the secondary axis, with that axis fixed from 0% to 100%. Add a horizontal line at 80% if you want the cut-off visible.
Three checks, each one cell:
=SUM(C2:C11) returns 1.=COUNT(B2:B11) equals the number of distinct customers in the export, and =SUM(B2:B11) equals the revenue total in the ledger, here 1,400,000.If the running share passes 100% before the last row and then falls back, there is a negative value in the list.
Both start from the same sorted table and running share. Pareto analysis draws one line and gives two groups. ABC analysis draws two lines, typically at 80% and 95%, and gives three classes: A, B and C. ABC is the usual choice for inventory and for customer coverage tiers, where the middle class needs its own treatment. The worked method, with cut points held in cells, is in ABC analysis of customers in Excel.
Revenue tells you who buys the most, not who earns the most. Covirage's tools rank customers and products from the ledger export, compute running shares and concentration, and check that the list sums to the revenue total; the external AI model explains who is in the vital few and what changed, and never does the arithmetic. See customer profitability, and how to calculate customer concentration in Excel for measures beyond one cut-off. To put the chart beside other measures on one page, see how to build an Excel dashboard, and to group customers by what they do rather than what they spend, see behavioral segmentation.
The observation that a small share of causes often accounts for a large share of effects, for example 20% of customers producing 80% of revenue. It is a pattern, not a law: real data splits at 70/30, 90/10 or anywhere else, which is why the analysis measures it.
Select the categories and values, then Insert > Insert Statistic Chart > Pareto, in Excel for Microsoft 365, Excel 2024 and Excel 2021 on Windows and Mac. Excel sorts the bars descending and adds the cumulative line. In older versions, build a combo chart with the running share on a secondary axis.
Pareto analysis finds the vital few that make up most of the total. ABC analysis uses the same ranking and running share but splits items into three classes, typically A up to 80%, B to 95% and C the rest, and is common for inventory and customer tiers.
Quality control, to find the few causes behind most defects; sales, to find the customers or products behind most revenue; procurement, to find the suppliers behind most spend; and service, to find the issues behind most tickets.