How to Do ABC Analysis in Excel: Functions and Chart-Building Steps

You don't need dedicated tools for ABC analysis; you can fully put it into practice with the Excel you already know.

In this article, we walk through how to perform ABC analysis in Excel, from using functions to building a Pareto chart, step by step. Once you have the flow down (sorting, calculating the cumulative composition ratio, ranking with the IF function, and charting), anyone can build an ABC analysis table from their own data.

What Is ABC Analysis? (Recap)

ABC analysis is a technique that classifies products or customers into three ranks (A, B, and C) according to their contribution to metrics such as sales, helping you identify what to manage with priority. It applies the Pareto principle, "the top 20% of products generate 80% of sales," to everyday operations.

The common criteria for ranking use the cumulative composition ratio, set as follows.

• Rank A: Cumulative composition ratio 0-70%. The mainstays that make up the bulk of sales.
• Rank B: Cumulative composition ratio 70-90%. Mid-tier products.
• Rank C: Cumulative composition ratio 90-100%. Products with small contribution.

These criteria (70% and 90%) are just guidelines; feel free to adjust them to suit your products and policy. If you build it in Excel, you can recalculate instantly even when you change the thresholds.

Steps to Build ABC Analysis in Excel

From here, using per-product sales data as an example, we explain the actual build process in five steps. We proceed assuming you have a table like the following at hand (column A = product name, column B = sales).

Step 1: Sort by Sales in Descending Order

First, sort sales (column B) in descending order. Select your data range and, under the Data tab, choose Sort to specify largest-to-smallest order by sales. Because ABC analysis stacks items from the highest composition ratio down, this sorting is the starting point.

Step 2: Calculate the Composition Ratio

In column C, find each product's composition ratio (its share of the whole). The formula to get each row's share of total sales is as follows.

=B2/SUM($B$2:$B$100)

The key is to make the total range in the denominator an absolute reference with $. This way the total range won't shift even when you copy the formula downward. Setting the cell format to percentage makes it easier to read.

Step 3: Calculate the Cumulative Composition Ratio

In column D, find the cumulative composition ratio that adds up the composition ratios from the top down. The first row is just the composition ratio itself, and from the second row on it is "the cumulative one row above plus your own composition ratio." An example of the formula to enter in the second row (D3) is as follows.