How Do You Do ABC Analysis of Inventory in Excel?

Most inventory lists are sorted by the wrong column. People sort by unit price, see the expensive items float to the top, and conclude those are the ones worth managing closely.

ABC analysis says something different and more useful: the items that deserve your attention are the ones that consume the most money over a year, which is a completely different list. A $4 fastener you ship twenty thousand times a year matters more to your working capital than a $900 spare part you touch once.

The whole method is six columns and five formulas in Excel. The difficult part is not the spreadsheet — it is understanding which number you are ranking, and what to do with the classes once you have them.

How Do You Do ABC Analysis of Inventory in Excel?

Multiply each item's unit cost by the quantity used over a year to get its annual consumption value, sort the list by that figure from largest to smallest, build a running total of its share of the grand total, and then cut the list into three classes at roughly 80% and 95% of cumulative value.

The items inside the first cut are your A class, the next band is B, and the long tail that makes up the last few percent is C.

In Excel that is six columns:

1. Item or SKU code
2. Unit cost
3. Annual quantity used
4. Annual consumption value — `=B2*C2`
5. Cumulative value, running down the sorted list — `=SUM($D$2:D2)`
6. Cumulative share of the total, and the class assigned from it

Everything after this is detail: which formulas handle the sort, where to put the cut points, and how to avoid the two mistakes that make the output meaningless.

A number that nobody has pressure-tested is a decision waiting to go wrong. Get a free audit and find out which of your reporting numbers actually holds up.

What ABC Analysis Actually Measures

ABC analysis is a categorisation technique borrowed from inventory optimisation, and it exists to solve a resourcing problem: you cannot count, forecast and negotiate every SKU with equal care, so you need a defensible way to decide which ones get the attention.

Annual Consumption Value, Not Unit Price

The ranking metric is annual consumption value: the item's cost per unit multiplied by the quantity used across a set period, preferably a full year.

That "preferably a year" matters more than it looks. Pick a quarter and seasonal items will be misclassified in both directions — a line that sells hard in Q4 looks like a C item if you measure it in spring.

Unit price on its own tells you nothing about consumption. Volume on its own tells you nothing about cost. ABC analysis is specifically the product of the two, and substituting either one for the product is the single most common way this exercise gets run badly.

Why the Classes Are Deliberately Uneven

The conventional split is that A items account for roughly 80% of annual consumption value, B items about 15%, and C items the remaining 5%.

The counts run in the opposite direction. A items are typically around 20% of your SKUs, and C items often make up about half the catalogue while barely moving the money.

That asymmetry is the entire point. It is the Pareto pattern applied to stock, and it is why the method is worth running at all: if value were spread evenly across items, there would be no shortlist to make.

The Cut Points Are a Choice, Not a Law

Nothing forces 80/15/5. Plenty of operations run 70/20/10, and some add a fourth class for the very long tail.

Treat the thresholds as a parameter you can defend rather than a rule you inherited. If your A class comes out at three SKUs, the cut is too tight to be actionable. If it comes out at four hundred, it is too loose to be a shortlist.

How to Build It in Excel, Step by Step

Step 1: Lay Out the Raw Columns

Put your SKU codes in column A, unit cost in column B, and annual quantity used in column C. Keep it as a flat list with one row per item and a single header row.

Turn the range into a proper Excel table with Ctrl+T. It costs five seconds and means every formula you write below expands automatically when you add rows next quarter.

Step 2: Calculate Annual Consumption Value

In D2, enter `=B2*C2` and fill it down the column.

Then put the grand total somewhere fixed and out of the way — say `=SUM(D2:D500)` in a cell above the table or on a summary sheet. You will reference it repeatedly, so give it an absolute address or a defined name.

Step 3: Sort by Value, Descending

The ranking has to happen before the running total means anything, because a cumulative sum down an unsorted list is just noise.

If you are on Microsoft 365, Excel 2024 or Excel 2021, `SORT` will do it as a formula. Microsoft's syntax is `=SORT(array,[sort_index],[sort_order],[by_col])`, so sorting a four-column block by its fourth column, largest first, is `=SORT(A2:D500,4,-1)`.

That returns a spilled array in a fresh block of cells, which keeps your raw data untouched — a real advantage when someone questions the numbers later.

On older versions, or if you would rather work in place, use Data > Sort, choose the annual consumption value column, and set the order to Largest to Smallest. The result is identical; it just does not refresh on its own.

Step 4: Build the Running Total and Cumulative Share

With the list sorted, the running total in E2 is `=SUM($D$2:D2)`.

The locked first reference and the relative second one are what make it accumulate: filled down, row 3 sums D2:D3, row 4 sums D2:D4, and so on.

Then convert that to a share of the whole in F2 with `=E2/$D$1`, pointing the denominator at wherever you parked the grand total. Format the column as a percentage.

Sanity check before you go further: the last row of column F must read exactly 100%. If it does not, you have either missed rows in the total or left an unsorted block in the middle of the list.

Half of all broken dashboards trace back to one locked reference in the wrong place. Get a free audit and have someone check the formulas behind your reporting.

Step 5: Assign the Class

With cumulative share in column F, the class in G2 is one `IFS`:

`=IFS(F2<=0.8,"A",F2<=0.95,"B",TRUE,"C")`

`IFS` needs Excel 2019 or later. On anything older, the nested version does the same job: `=IF(F2<=0.8,"A",IF(F2<=0.95,"B","C"))`.

Note that the test is on cumulative share, not on the item's own share. Each row asks "how much of the total value have we accounted for by the time we reach this item?" — that is what puts the boundary in the right place.

Step 6: Summarise the Classes

Three counts and three sums tell you whether the split is sensible:

• Items per class — `=COUNTIF($G$2:$G$500,"A")`
• Value per class — `=SUMIF($G$2:$G$500,"A",$D$2:$D$500)`
• Share of total value — the class value divided by the grand total

Read those six numbers together. If A is 20% of your items and about 80% of your money, the analysis has done its job and you can act on it.

When Is ABC Analysis the Right Tool?

It earns its keep whenever a finite amount of attention has to be allocated across a catalogue that is too big to manage uniformly.

Cycle counting is the clearest case: count A items often, B items periodically, C items rarely. The same logic applies to how tightly you forecast, how hard you negotiate, how much safety stock you carry, and which suppliers get a quarterly review rather than an annual one.

It is also a good first pass before any more sophisticated inventory work, because it tells you where the money actually sits. Running a demand-forecasting project across five thousand SKUs is a different proposition once you know that four hundred of them carry 80% of the value.

Re-run it at least once a year. Consumption patterns drift, and a classification built on two-year-old quantities will quietly send your attention to the wrong shelf.

Most teams have never checked whether last year's assumptions still hold. Get a free audit and find out what has drifted.

Where ABC Analysis Falls Down

It is a static snapshot. The method ranks what happened over the period you measured and says nothing about what is about to happen, so a line in steep decline and a line growing fast can land in the same class.

It is blind to criticality. An inexpensive component used twice a year will classify as C, even if production stops without it. Plenty of operations keep a manual override list for exactly this reason, and that is the correct response rather than a flaw in your spreadsheet.

It cannot classify new items at all, because there is no consumption history to multiply. New SKUs need a placeholder class and a review date.

And done in a spreadsheet, it is exposed to ordinary human error — a mistyped quantity, a sort applied to one column instead of the whole block, a total that stops one row short. The last of those is worth guarding against explicitly, which is why the 100% check in Step 4 is not optional.

ABC vs the Alternatives

Plain Pareto analysis is the same arithmetic stopped early. It identifies the vital few and leaves the rest as one undifferentiated mass. ABC is more useful operationally because the B class gives you somewhere to put items that are neither critical nor negligible.

XYZ analysis ranks by demand variability instead of value: X for steady, Z for erratic. It answers a different question — how predictable is this item? — and on its own it tells you nothing about financial exposure.

ABC-XYZ combined is the version worth graduating to. Crossing the two produces a nine-box grid, and the interesting cell is AZ: high value, highly unpredictable. Those are the items that cause both stockouts and write-offs, and neither method finds them alone. It is more work to maintain, so build plain ABC first and extend it only once people are actually using the output.

Inventory management software does all of this continuously and without the sort-order hazards. The spreadsheet version remains worth knowing because it is transparent — you can see every step of the calculation, which matters when someone disputes why their favourite product landed in C.

Picking the wrong metric is a quieter failure than a broken formula, and a more expensive one. Get a free audit and find out whether you are measuring the thing you think you are.

Final Thoughts

ABC analysis is one multiplication, one sort, one running total and one `IFS`. The arithmetic takes ten minutes.

What makes it useful is picking the right metric to rank, and the most common failure is not a formula error at all — it is sorting by unit cost and producing a list of expensive things rather than a list of expensive-to-stock things. Those two lists overlap far less than people expect.

The classes are only worth generating if something changes as a result: counting frequency, forecast effort, supplier attention, reorder policy. A classification nobody acts on is just a tidier spreadsheet.

If grouping records into meaningful bands is the part of this you want to get better at generally, our guide to how to stratify data in Excel covers the broader technique that ABC analysis is one specific application of.