How to Classify Inventory with ABC Analysis in Excel

Sort SKUs into A, B and C classes by cumulative share of annual value, count the A items, and total the value tied up in C items.

You will learn to

• Classify an item from its cumulative %
• Count the items in class A
• Total the annual value of class C items

How it works

ABC analysis ranks stock by annual usage value (units used × unit cost) so you can focus control where the money is. Sort items from highest to lowest value and add a cumulative % column. Items up to about 80% of total value are class A, the next 15% (up to 95%) are B, and the long tail is C.

With the cutoffs in cells, a nested IF classifies each item: =IF(D6<=$H$1,"A",IF(D6<=$H$2,"B","C")). A items get tight control and frequent counts; C items — often most of the SKUs but a small share of the value — can be counted rarely and reordered in bulk.

=IF(D6<=$H$1,"A",IF(D6<=$H$2,"B","C"))