Excel guide · 5 min read
ABC inventory analysis in Excel
=SUMIFS($E$2:$E$13,$E$2:$E$13,">"&E2)/SUM($E$2:$E$13)+F2 gives the cumulative share without sorting, and =IFS(H2<=$L$1,"A",H2<=$L$2,"B",TRUE,"C") assigns the class.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 | H | I | J | K | L |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | SKU | Item | Units/yr | Unit cost | Usage value | Share | Cum. before | Cum. share | Class | A up to | 0.8 | |
| 2 | HT-101 | Cordless drill 18V | 1150 | 89 | 102350 | 0.29345145937 | 0 | 0.29345145937 | A | B up to | 0.95 | |
| 3 | HT-102 | Drill bit set | 2400 | 6.5 | 15600 | 0.044727335283 | 0.78831928436 | 0.83304661965 | B | |||
| 4 | HT-103 | Safety gloves | 4200 | 1.8 | 7560 | 0.021675554791 | 0.9318768278 | 0.95355238259 | C | |||
| 5 | HT-104 | Circular saw | 620 | 145 | 89900 | 0.25775560525 | 0.29345145937 | 0.55120706463 | A | |||
| 6 | HT-105 | Wood screws 500pk | 3100 | 4.2 | 13020 | 0.03733012214 | 0.83304661965 | 0.87037674179 | B | |||
| 7 | HT-106 | Tape measure | 1500 | 7.9 | 11850 | 0.033975571994 | 0.87037674179 | 0.90435231378 | B | |||
| 8 | HT-107 | Laser level | 410 | 118 | 48380 | 0.13871208211 | 0.55120706463 | 0.68991914674 | A | |||
| 9 | HT-108 | Sandpaper pack | 2100 | 3.1 | 6510 | 0.01866506107 | 0.95355238259 | 0.97221744366 | C | |||
| 10 | HT-109 | LED work light | 400 | 24 | 9600 | 0.02752451402 | 0.90435231378 | 0.9318768278 | B | |||
| 11 | HT-110 | Utility knife | 1600 | 3.4 | 5440 | 0.015597224612 | 0.97221744366 | 0.98781466827 | C | |||
| 12 | HT-111 | Shop vacuum | 260 | 132 | 34320 | 0.098400137623 | 0.68991914674 | 0.78831928436 | A | |||
| 13 | HT-112 | Wall anchors | 3400 | 1.25 | 4250 | 0.012185331728 | 0.98781466827 | 1 | C |
Download this sheet as an Excel practice file (.xlsx)
Why ABC, and the data you need
In most stock lists, a few items carry most of the money. ABC analysis finds them so you can count them more often, forecast them more carefully and negotiate harder on them, while the long tail of cheap items gets simpler rules. A items are the few that make up most of the value, C items are the many that make up little, and B items sit between.
You need one row per SKU with annual units used or sold and the unit cost. The sample is a tool distributor with 12 SKUs in A:D, listed in SKU order, not sorted. Use a full year of usage so seasonal items aren't misjudged.
Step 1: annual usage value and share
Usage value = annual units x unit cost. It's the money that flows through each item in a year, and it's what ABC ranks by. The cordless drill moves 1,150 units at 89.00 for 102,350, the most of any SKU even though gloves sell more than three times as many units. That's the point of ranking by value rather than units.
Next to it, each SKU's share is its usage value divided by the total (348,780 here). Lock the total with dollar signs so the formula fills down.
Result: 102350
102350: the cordless drill's annual usage value, 29.3% of the total.
Step 2: cumulative share, without sorting the list
The usual method sorts the list by value and adds a running total. That breaks as soon as someone re-sorts the data or adds a SKU. A sort-proof version adds up the value of every item worth more than this one with SUMIFS, divides by the total, and adds the item's own share. The result is the cumulative share this item reaches when the list is ranked largest first.
The shop vacuum is the fourth-largest item: the three items worth more (drill, saw, laser level) make up 69.0%, and the vacuum adds 9.8%, so its cumulative share is 78.8%. Items with exactly the same usage value get the same "items worth more" total, so they always land in the same class.
Result: 0.78831928436 shows as 78.8% with Percent format
0.788, which is 78.8%: the four largest items, down to and including the shop vacuum, carry 78.8% of annual usage value.
Step 3: classify with cut-offs in input cells
Put the cut-offs in cells (L1 = 80% for A, L2 = 95% for B) so you can change them without touching formulas. IFS checks them in order: up to 80% is A, up to 95% is B, the rest is C. In Excel 2016 or earlier without IFS, nest two IFs instead.
The result: 4 A items (a third of the SKUs) carry 78.8% of value, 4 B items carry 14.4%, and 4 C items carry 6.8%. Real catalogues with hundreds of SKUs are usually more lopsided than this one, with A items a smaller share of the count.
Result: A
A: the shop vacuum's cumulative share (78.8%) is within the 80% cut-off.
What about the item that crosses 80%?
The drill bit set takes the cumulative share from 78.8% to 83.3%. With the rule above it's B. Some teams class an item by where the list stood before it, so the item that crosses the line joins A. Both are valid; pick one and write it down. Column G holds the share before each item.
Result: A
A: under the "before" rule, the drill bit set joins class A because the list stood at 78.8% before it.
Step 4: a sorted view and a class summary
Leave the source table in SKU order and build a ranked view next to it in Excel 365. SORTBY with -1 sorts by usage value, largest first, and HSTACK picks the columns to show. It spills, so it updates when the data changes.
For the summary, COUNTIF counts SKUs per class and SUMIFS adds their value: =SUMIFS($E$2:$E$13,$I$2:$I$13,"A")/SUM($E$2:$E$13) gives class A's share of value. Showing "% of SKUs" next to "% of value" is the slide that makes the case for treating classes differently.
Result: spills 12 rows × 4 columns
| HT-101 | Cordless drill 18V | 102350 | A |
| HT-104 | Circular saw | 89900 | A |
| HT-107 | Laser level | 48380 | A |
| HT-111 | Shop vacuum | 34320 | A |
| HT-102 | Drill bit set | 15600 | B |
| HT-105 | Wood screws 500pk | 13020 | B |
| HT-106 | Tape measure | 11850 | B |
| HT-109 | LED work light | 9600 | B |
| HT-103 | Safety gloves | 7560 | C |
| HT-108 | Sandpaper pack | 6510 | C |
| HT-110 | Utility knife | 5440 | C |
| HT-112 | Wall anchors | 4250 | C |
A ranked list starting HT-101 Cordless drill 18V, 102,350, A, then the circular saw, laser level and shop vacuum, down to the wall anchors at 4,250 in class C.
Choosing cut-offs and using the classes
80% and 95% are a convention, not a rule. Some businesses use 70/90 or classify by SKU count instead (the top 20% of SKUs are A). What matters is that the classes lead to different actions: for example, counting A items monthly and C items once or twice a year, reviewing A items' safety stock and order quantities more often, and keeping C items on simple rules.
Usage value isn't the only lens. A cheap part that stops a production line may deserve A treatment, and a slow, expensive item may need watching for weeks of supply. Rerun the analysis every quarter or year, since items move between classes. For the general running-share technique, see cumulative percentage in Excel.
Common mistakes
Ranking by units sold instead of value
Multiply units by unit cost first. High-volume cheap items rarely belong in A.
A running total that breaks when the list is re-sorted
Use SUMIFS on "greater than this item's value" so the cumulative share doesn't depend on row order.
Hard-coding 0.8 and 0.95 inside formulas
Put the cut-offs in input cells and reference them with dollar signs.
Using a few months of data
Use a full year of usage so seasonal items aren't pushed into the wrong class.
FAQ
How do you do ABC analysis in Excel?
Calculate usage value (units x unit cost), each item's cumulative share of total value from largest to smallest, then classify with IFS: A up to 80%, B up to 95%, C for the rest.
What is the 80/15/5 rule in ABC analysis?
It's the common split where A items make up about 80% of value, B about 15% and C about 5%. The exact cut-offs are a choice; keep them in input cells.
Can I do ABC analysis without sorting the data?
Yes. =SUMIFS(value,value,">"&this value)/total + this item's share gives the cumulative share whatever the row order.
How is ABC analysis different from a Pareto chart?
They use the same ranked cumulative share. A Pareto chart draws it; ABC analysis turns it into three classes you can act on.
Functions used
SUMIFSAdds with several conditionsSUMAdds numbersIFSFirst true test winsIFPicks a result from a testCOUNTIFCounts cells that matchSORTBYSorts by other columns, spillsHSTACKJoins arrays side by side
Related guides
- How to make a Pareto chart in Excel
- How to do a spend analysis in Excel
- How to calculate cumulative percentage in Excel
- How to calculate economic order quantity (EOQ) in Excel
- Excel formulas for inventory: stock balance, reorder and value
- Excel formulas cheat sheet: the 25 everyone should know
- How to calculate the gross rent multiplier (GRM) in Excel
Updated