How to Do ABC Analysis in Excel: 5 Easy Steps
ABC analysis sorts inventory items into three classes by the value of their annual use. Class A items are the few that account for most of the money. Class C items are the many that account for little, and class B sits in between. In Excel, you can do it with one table: annual value, share of total, a running total, and a formula that assigns each class.
This guide explains what the classes mean, the five Excel steps, a worked example, and how to chart the result. It also covers how to pick your cutoffs and what to do with each class once the analysis is done.
What ABC analysis is
ABC analysis is a way to decide which items deserve the most attention. It rests on a simple pattern: a small share of items carries a large share of the spending.
A 2012 chapter on analyzing and controlling pharmaceutical expenditures, from Management Sciences for Health (MSH), describes it plainly. It notes that "a relatively small number of items account for most of the value of annual consumption." It adds: "The analysis of this phenomenon is known as Pareto analysis or, more commonly, ABC analysis."
The same chapter explains that items "can be classified into three categories (A, B, and C) based on the value of their annual usage." The method is the same whether you stock medicines, spare parts, or retail products.
One point is easy to miss. The classes are not permanent labels. MSH notes that "If use patterns change, the item may fall into a different category the next time ABC analysis is performed." So ABC analysis works best as a routine check, not a one-off project.
What the A, B, and C classes mean
The MSH chapter gives typical ranges for each class:
| Class | Share of items | Share of annual value | What it usually means |
|---|---|---|---|
| A | 10 to 20 percent | 75 to 80 percent | Few items, most of the money |
| B | 10 to 20 percent | 15 to 20 percent | A middle group |
| C | 60 to 80 percent | 5 to 10 percent | Many items, little of the money |
These are typical ranges, not rules. MSH says "These boundaries are somewhat flexible." Its example sets class A at the items that add up to 70 percent of funds instead.
The value that drives the classes is annual consumption value: units used in a year times the unit cost. A cheap item used in huge volumes can land in class A. An expensive item used once a year can land in class C.
A 2014 article in the American Journal of Business Education questions using value alone. It argues that textbooks "focus on dollar volume as the sole criterion" and recommends adding other criteria. For a first pass, value is the method the MSH chapter uses.
What you need before you start
ABC analysis in Excel needs only a few columns per item:
- Item name or SKU. One row per item.
- Annual units used or purchased. Use the same 12-month period for every item.
- Unit cost. The cost of one unit, in the same unit you count in.
MSH stresses the matching period: "Make sure that the same review period is used for all items to avoid invalid comparisons." It also advises using the same basic unit for cost and quantity, such as a tablet or a single box, rather than mixing pack sizes.
If your data comes from an inventory or purchasing system, export it as a CSV or Excel file. Remove items with no activity in the period, or keep them and expect them to fall into class C.
How to do ABC analysis in Excel
The five steps below follow the method in the MSH chapter, adapted to Excel formulas. The example puts a title in row 1, headers in row 2, and 10 items in rows 3 to 12. Columns A, B, and C hold the item name, annual units, and unit cost.
Step 1: List the items, units, and unit cost
Enter or paste one row per item with its name, annual units, and unit cost. Add headers in row 2 so the table is easy to sort later.
Check the data before going further. Look for blank costs, negative quantities, and duplicate SKUs, because each one will distort the totals. A quick filter on each column usually finds them.
If several purchases of the same item were made at different prices, use one consistent cost. MSH notes that "a weighted average or a FIFO average" are the most accurate alternatives when actual unit cost is hard to track.
Step 2: Calculate the annual value and its share of the total
In column D, multiply units by cost to get each item's annual value. In D3, enter =B3*C3 and fill the formula down.
In column E, divide each value by the total of all values to get its share. In E3, enter =D3/SUM($D$3:$D$12) and fill down. The dollar signs keep the total range fixed as the formula copies. Format column E as a percentage with two decimal places.
MSH recommends that precision for a reason. In its words, "several items may be close together in value and many may represent less than 1 percent of total value."
Step 3: Sort the items by value, largest first
Select the whole table, including headers, and sort by column D from largest to smallest. In Excel, that is Data, then Sort, with column D and the order set to Largest to Smallest.
If you prefer a formula, the SORT function returns a sorted copy. Microsoft's syntax is =SORT(array,[sort_index],[sort_order],[by_col]), where a sort order of -1 means descending. For this table, =SORT(A3:E12,4,-1) sorts by the fourth column, highest value first.
After this step, the item with the highest annual value sits at the top. That order is what makes the running total in the next step meaningful.
Step 4: Add the cumulative percentage
In column F, add a running total of the shares. In F3, enter =SUM($E$3:E3) and fill down. The first part of the range stays fixed, and the second part grows by one row each time.
The last row should show 100 percent. If it does not, check for blank cells or text values in columns D and E.
This column is the heart of ABC analysis. It shows how much of the total value the items above each row account for together.
Step 5: Assign the A, B, and C classes
In column G, use a formula to label each item. With cutoffs of 80 and 95 percent, enter this in G3 and fill down:
=IFS(F3<=0.8,"A",F3<=0.95,"B",TRUE,"C")
The IFS function checks each condition in order and returns the first match. Microsoft's own example uses the same pattern, with TRUE as the final catch-all. Items up to 80 percent cumulative become A, items up to 95 percent become B, and the rest become C.
Finally, count each class with =COUNTIF(G3:G12,"A") and the same for B and C. Compare the counts with the typical ranges above. Adjust the cutoffs if class A is far too large or small for your team to manage.
A worked example
Here is an illustrative table for 10 items, already sorted by annual value. The numbers are examples, not data from a real company.
| Item | Annual units | Unit cost | Annual value | Share | Cumulative | Class |
|---|---|---|---|---|---|---|
| SKU-01 | 1,200 | $45.00 | $54,000 | 36.00% | 36.00% | A |
| SKU-02 | 3,000 | $12.00 | $36,000 | 24.00% | 60.00% | A |
| SKU-03 | 500 | $40.00 | $20,000 | 13.33% | 73.33% | A |
| SKU-04 | 8,000 | $1.50 | $12,000 | 8.00% | 81.33% | B |
| SKU-05 | 2,000 | $4.00 | $8,000 | 5.33% | 86.67% | B |
| SKU-06 | 600 | $10.00 | $6,000 | 4.00% | 90.67% | B |
| SKU-07 | 1,000 | $5.00 | $5,000 | 3.33% | 94.00% | B |
| SKU-08 | 1,500 | $3.00 | $4,500 | 3.00% | 97.00% | C |
| SKU-09 | 700 | $5.00 | $3,500 | 2.33% | 99.33% | C |
| SKU-10 | 400 | $2.50 | $1,000 | 0.67% | 100.00% | C |
The total annual value is $150,000. Three items, 30 percent of the list, make up 73.33 percent of the value and land in class A. Four items fall into class B, and the last three, worth 6 percent of the value, fall into class C.
Two details stand out. SKU-04 has the most units by far, but its low cost puts it in class B. And with only 10 items, the class shares will not match the typical ranges, which is normal for a short list.
How to chart the result
A chart makes the pattern easy to show in a meeting. MSH suggests plotting the cumulative percentage against the item number, which gives the familiar ABC curve.
Excel has a built-in chart for this. Microsoft describes a Pareto chart as one that "contains both columns sorted in descending order and a line representing the cumulative total percentage." To create one, select the item names and annual values, then choose Insert, Insert Statistic Chart, and Pareto.
Add two horizontal lines or labels at your cutoffs, such as 80 and 95 percent, so viewers can see where each class begins. Our guide to making a Pareto chart with AI covers the chart itself in more depth.
Choosing your cutoffs
There is no single correct cutoff. MSH explains that the choice "depends on how volume and value are dispersed among items on the list." It also depends on "how the results of the ABC analysis are going to be used."
Management capacity is the practical limit. MSH puts it directly: "allocation of items to class A must be based on management capacity." If your team can review 50 items closely each month, a class A of 300 items defeats the purpose.
A few common approaches:
- Value cutoffs. A up to 80 percent of value, B up to 95 percent, C for the rest. This is the method used above.
- Item-count cutoffs. The top 20 percent of items by value become A, the next 30 percent B, and the rest C.
- Fixed lists. Some teams set class A as the top 25 or 50 items, whatever their share of value.
Whichever you pick, write it down and use it every time. Comparing this quarter's classes with last quarter's only works if the cutoffs stay the same.
What to do with each class
The point of ABC analysis is to spend effort where the money is. The MSH chapter lists several ways to use the results:
- Order class A items more often. MSH says ordering class A items "more often and in smaller quantities should lead to a reduction in inventory-holding costs."
- Negotiate class A prices first. "Price reductions for items classified as A products in the analysis can lead to significant savings," according to the chapter.
- Count class A stock more often. MSH notes that "cyclic stock counts should be guided by ABC analysis, with more frequent counts for class A items."
- Watch class A order status. An unexpected shortage of a class A item can lead to costly emergency purchases.
Class C items can get simpler rules, such as larger, less frequent orders and fewer counts. Class B sits in between. If slow movers are a concern, our guide to how to spot slow-moving inventory pairs well with this analysis.
Doing it faster with AI
The Excel steps take a few minutes once the data is clean. Cleaning the export and repeating the work every quarter takes longer.
An AI workspace can do the arithmetic and the sorting in one request. Upload the inventory or purchasing export to Powerdrill Bloom and ask in natural language for an ABC analysis with your cutoffs. Ask for annual value, share, cumulative percentage, and class for each item, plus a Pareto chart.
Then check it like any spreadsheet. Confirm the total annual value against your own sum, and spot-check two items in each class. Our Excel AI assistant page covers that kind of spreadsheet work in more detail. For a broader look at forecasting tools, see this roundup of AI tools for inventory and demand forecasting.
Common mistakes to avoid
- Mixing time periods. Twelve months for one item and six for another makes the shares meaningless.
- Using units instead of value. The classes depend on units times cost, not on units alone.
- Forgetting to sort before the running total. A cumulative percentage on an unsorted list puts items in the wrong class.
- Treating classes as permanent. Rerun the analysis each quarter or year, because items move between classes.
- Cutoffs that ignore capacity. A class A list too long to manage closely gets no closer attention than class B.
- Ignoring critical cheap items. A low-value item can still stop work if it runs out. The MSH chapter pairs ABC analysis with a separate rating of vital, essential, and nonessential items.
When your item list comes from a messy export, you can try Powerdrill Bloom to build the first ABC table and chart.
Frequently asked questions
What is ABC analysis in inventory management?
ABC analysis sorts items into three classes by annual consumption value. Class A items are the few that account for most of the value. Class C items are the many that account for little, and class B is in between. It helps teams focus control effort where the money is.
How do you calculate ABC analysis in Excel?
Multiply annual units by unit cost for each item, then divide by the total to get each item's share. Sort by value from largest to smallest, add a running total of the shares, and assign classes with a formula such as IFS. Cutoffs of 80 and 95 percent sit within the typical ranges in the MSH chapter.
What are the percentages for ABC analysis?
A common guideline is that class A holds 10 to 20 percent of items and 75 to 80 percent of value. Class B holds another 10 to 20 percent of items and 15 to 20 percent of value. Class C holds 60 to 80 percent of items and 5 to 10 percent of value.
What is the formula for ABC classification in Excel?
With the cumulative percentage in column F and data starting in row 3, use =IFS(F3<=0.8,"A",F3<=0.95,"B",TRUE,"C"). Change 0.8 and 0.95 to match your own cutoffs. Nested IF formulas can do the same job.
Why is ABC analysis important?
It shows where most of the inventory money goes, so teams can manage those items more closely. Typical uses include ordering class A items more often, negotiating their prices first, and counting them more frequently. It also flags spending that does not match plans.
Sources: Management Sciences for Health, MDS-3 Chapter 40: Analyzing and controlling pharmaceutical expenditures · Ravinder and Misra, ABC Analysis for Inventory Management (2014) · Microsoft Support, SORT function · Microsoft Support, IFS function · Microsoft Support, Create a Pareto chart.