How to Calculate ABC Analysis in Excel

Knowing which products or customers deliver the most value to your business is fundamental to smart resource allocation. Whether you're managing thousands of inventory SKUs or a long list of clients, not every item holds the same weight. This is where ABC analysis comes in - a simple but powerful method to prioritize what matters most. This guide will walk you through, step by step, how to perform a complete ABC analysis right inside Microsoft Excel.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

What Exactly is ABC Analysis?

ABC analysis is an inventory and customer categorization technique used to classify items into three distinct groups - A, B, and C - based on their value or significance. It’s built on the Pareto Principle, also known as the 80/20 rule, which suggests that about 80% of outcomes (like revenue or consumption value) typically come from just 20% of the inputs (like products or customers).

By applying this idea, you can focus your time, money, and effort where it will have the biggest impact. Here’s how the categories generally break down:

• Category A: These are your most valuable items. They represent a small percentage of your total items (around 10-20%) but account for a large portion of the value (around 70-80%). These items demand the most attention and tightest control.
• Category B: This group is the "middle ground." They are moderately important, making up about 15-25% of total items and contributing roughly 15-25% of the total value.
• Category C: These are your least valuable items. They make up the bulk of your total items (the remaining 60-65%) but only account for a tiny sliver of the actual value (about 5%). These items require the least amount of oversight.

This method isn't just for physical inventory. You can use it to segment customers by revenue, prioritize marketing tasks by impact, or even manage your personal to-do list by a project's importance.

Preparing Your Data for Analysis

Before you can start calculating in Excel, you need to gather the right data. The foundation of an accurate ABC analysis lies in having a clean, simple dataset. You’ll need at least three key pieces of information for each item you want to analyze:

• Item Identifier: A unique name or code for each product (e.g., SKU, Product Name).
• Unit Cost or Price: How much each individual unit costs you to purchase or manufacture.
• Annual Consumption / Sales Volume: The total number of units sold or used over a specific period, typically a year.

This data usually lives in your inventory management system, accounting software, an ERP, or even sales reports from e-commerce platforms like Shopify. Export this data into a CSV or Excel file.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

How to Calculate ABC Analysis in Excel: A Step-by-Step Guide

Once your data is organized in an Excel sheet, it's time to start the analysis. We will add a few new columns to our worksheet to classify each item. Let's get started.

Step 1: Calculate the Annual Consumption Value

The first step is to figure out the total value each item contributes. This isn't just its unit cost or sales volume, but the combination of both. Create a new column in your worksheet called "Consumption Value".

In this new column, multiply the Unit Cost by the Annual Consumption for each item. Assuming your data starts in row 2, with Annual Consumption in column B and Unit Cost in column C, the formula for cell D2 would be:

=B2*C2

Click on the small square at the bottom-right corner of cell D2 and drag it down to apply this formula to all of your items. You now have the total annual value for every single product.

Step 2: Calculate the Grand Total Value

Next, you need to know the total value of all your items combined. This number will serve as our baseline for calculating what percentage of the total value each individual item represents.

Click on the cell directly below your last "Consumption Value" entry (for example, if your data ends at D20, click D21). Use the SUM function to add up all the values in that column:

=SUM(D2:D20)

Make a note of this cell, as we'll need it for the next step. For clarity, you can label the cell to its left as "Grand Total."

Step 3: Sort Your Items by Consumption Value

To properly apply the Pareto Principle, we must rank our items from most to least valuable. You'll need to sort your entire dataset based on the "Consumption Value" column.

1. Select your entire data table, including headers.
2. Go to the Data tab in Excel's ribbon.
3. Click on the Sort button.
4. In the popup window, set the "Sort by" dropdown to "Consumption Value."
5. Set the "Order" dropdown to "Largest to Smallest."
6. Click OK.

Your spreadsheet will now list the products that generate the most value at the top, down to the ones that contribute the least.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Step 4: Calculate the Percentage of Total Value

Now, let's determine what percentage of the grand total value each individual product is responsible for. Create a new column named "% of Total Value".

Here, you'll divide the "Consumption Value" of each item by the "Grand Total" value we calculated in Step 2. The key here is to use an absolute reference for the grand total so that it doesn't change when you drag the formula down. You can make a reference absolute by adding dollar signs ($) before the column letter and row number.

If your consumption value for the top item is in D2 and your grand total is in cell D21, the formula for cell E2 would be:

=D2/$D$21

Format this column as a percentage by going to the Home tab and clicking the percent sign (%). Then, drag the formula down for all of your items.

Step 5: Compute the Cumulative Percentage

The 'cumulative percentage' will show the running total of a percentage as we go down the list of our sorted items. This is what ultimately allows us to assign items to categories A, B, and C. Create a final column and title it "Cumulative %".

• For the first item (in cell F2): The cumulative percentage is just its own "% of Total Value". So, the formula is simply =E2.
• For the second item (in cell F3): The formula is the cumulative percentage of the row above, plus the individual percentage of the current row: =F2+E3.

Now, drag the formula from cell F3 all the way down. The last cell in this column should equal 100%, confirming that you've accounted for all value.

Step 6: Assign Categories A, B, and C

This is the final step where everything comes together. We will use a nested IF function to automatically classify our items based on the "Cumulative %" column against our established thresholds. Create one last column named "ABC Category".

The standard thresholds are:

• Category A: Cumulative percentage is less than or equal to 80%.
• Category B: Cumulative percentage is between 80% and 95%.
• Category C: Cumulative percentage is greater than 95%.

Assuming your first cumulative percentage is in F2, you can use the following IF formula in cell G2:

=IF(F2<=80%, "A", IF(F2<=95%, "B", "C"))

Let’s break down this formula:

• IF(F2<=80%, "A", ...): If the cumulative percent is 80% or less, label it "A".
• IF(F2<=95%, "B", "C"): If the first condition is false, check if the cumulative percent is 95% or less. If true, label as "B". Otherwise, label as "C".

Drag this formula down, and like magic, every one of your items is now categorized!

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Using Your ABC Analysis Insights

A calculated spreadsheet is only half the battle, the real value comes from what you do with this information. Now that your inventory is segmented, you can create more strategic, targeted policies for each group.

Managing Category A Items

These are your superstars. Treat them as such.

• Inventory Control: Implement stringent inventory control. Conduct regular, frequent stock counts (cycle counting) to prevent stockouts, which can lead to significant revenue loss.
• Supplier Relationship: Foster strong relationships with the suppliers of these items to ensure reliable delivery and favorable terms.
• Demand Forecasting: Invest more time and resources in accurately forecasting demand for these products to optimize stock levels.

Managing Category B Items

These are your solid, reliable performers. They deserve attention but don't require the same level of intensive management as Category A.

• Inventory Control: Implement a more moderate inventory system. You can perform stock counts less frequently and maintain a reasonable safety stock.
• Cost Optimization: Periodically review pricing and explore alternative suppliers to ensure you are getting good value.

Managing Category C Items

These items make up the long tail of your inventory. The primary goal here is to minimize the time and money spent managing them.

• Inventory Control: Adopt a relaxed inventory model. Consider ordering these in large quantities less often to reduce administrative and ordering costs. A stockout is less critical here.
• Procurement: Standardize these products and simplify procurement. You may not need to vet multiple suppliers as rigorously.

Final Thoughts

Performing an ABC analysis in Excel is a straightforward process that transforms a flat list of items into a prioritized strategy for your business. By calculating consumption value, sorting your items, and using a few simple formulas, you can uncover which products or customers are truly driving your success, helping you focus your resources much more effectively.

While Excel is a fantastically accessible tool for this type of focused analysis, the initial friction of exporting, cleaning, and managing data from different platforms remains a challenge for many teams. At our company, we experienced this headache of manual report pulling from sources like Shopify, Google Analytics, and various ad platforms firsthand. We built Graphed to remove that friction completely. It connects to all your data sources automatically and allows you to create real-time dashboards and reports simply by asking for what you need in plain English - no wrestling with formulas required.