Cellwise Start free

Excel guide · 5 min read

ABC inventory analysis in Excel

Quick answerMultiply each SKU's annual units by its unit cost to get usage value, work out each SKU's cumulative share of the total from largest to smallest, then classify: A up to 80% of value, B up to 95%, C for the rest. 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.

RowABCDEFGHIJKL
1SKUItemUnits/yrUnit costUsage valueShareCum. beforeCum. shareClassA up to0.8
2HT-101Cordless drill 18V1150891023500.2934514593700.29345145937AB up to0.95
3HT-102Drill bit set24006.5156000.0447273352830.788319284360.83304661965B
4HT-103Safety gloves42001.875600.0216755547910.93187682780.95355238259C
5HT-104Circular saw620145899000.257755605250.293451459370.55120706463A
6HT-105Wood screws 500pk31004.2130200.037330122140.833046619650.87037674179B
7HT-106Tape measure15007.9118500.0339755719940.870376741790.90435231378B
8HT-107Laser level410118483800.138712082110.551207064630.68991914674A
9HT-108Sandpaper pack21003.165100.018665061070.953552382590.97221744366C
10HT-109LED work light4002496000.027524514020.904352313780.9318768278B
11HT-110Utility knife16003.454400.0155972246120.972217443660.98781466827C
12HT-111Shop vacuum260132343200.0984001376230.689919146740.78831928436A
13HT-112Wall anchors34001.2542500.0121853317280.987814668271C

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.

=C2*D2

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.

=SUMIFS($E$2:$E$13,$E$2:$E$13,">"&E12)/SUM($E$2:$E$13)+F12

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.

=IFS(H12<=$L$1,"A",H12<=$L$2,"B",TRUE,"C")

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.

=IF(G3<$L$1,"A",IF(G3<$L$2,"B","C"))

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.

=SORTBY(HSTACK(A2:A13,B2:B13,E2:E13,I2:I13),E2:E13,-1)

Result: spills 12 rows × 4 columns

HT-101Cordless drill 18V102350A
HT-104Circular saw89900A
HT-107Laser level48380A
HT-111Shop vacuum34320A
HT-102Drill bit set15600B
HT-105Wood screws 500pk13020B
HT-106Tape measure11850B
HT-109LED work light9600B
HT-103Safety gloves7560C
HT-108Sandpaper pack6510C
HT-110Utility knife5440C
HT-112Wall anchors4250C

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

Related guides

Updated