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.

=D2+C3

In the top cell (D2), enter "=C2," then copy the formula above into D3 and below. If the final row comes to 100%, the calculation is correct.

Step 4: Determine A, B, and C Ranks with the IF Function

In column E, automatically assign ranks based on the cumulative composition ratio. Nest the IF function and judge by the thresholds (70% and 90%). An example of the formula to enter in E2 is as follows.

=IF(D2<=70%,"A",IF(D2<=90%,"B","C"))

This formula returns A if the cumulative composition ratio is 70% or less, B if 90% or less, and C otherwise. When you want to change the thresholds, just edit the 70% and 90% values in the formula. In an environment where the IFS function is available, you can also write it more readably.

Step 5: Make It Easy to Read

• Apply conditional formatting to the rank column and color-code A, B, and C so you can grasp them at a glance.
• Align the composition ratio and cumulative composition ratio to percentage display.
• Since you'll often want to change the criteria later, it's handy to place the 70% and 90% thresholds in separate cells and reference them from the formula.

How to Build an ABC Analysis Chart (Pareto Chart)

Beyond the numbers, turning it into a Pareto chart (a chart overlaying a bar graph with a cumulative-composition-ratio line) makes it visually clear where Rank A ends. There are two ways to make one depending on your Excel version.

Method 1: Pareto Chart (Excel 2016 and Later)

1. Select the two columns of product name and sales.
1. Under the Insert tab, choose Insert Statistic Chart, then Pareto.
1. The bar graph and cumulative line are drawn automatically.

This method is the easiest, but it isn't suited to fine adjustments such as displaying thresholds on the cumulative line.

Method 2: Build It Yourself with a Combo Chart

1. Select the columns for product name, sales, and cumulative composition ratio.
1. Choose Insert, then Combo Chart, and set sales to "clustered column" and the cumulative composition ratio to "line (secondary axis)."
1. Fixing the maximum of the secondary axis (cumulative composition ratio) at 100% makes it easier to read.
1. If needed, add 70% and 90% reference lines to mark the A, B, and C boundaries.

A self-built combo chart takes more effort, but in return you can freely adjust how it looks. It suits cases where you want to clearly indicate the boundaries in a report.

Tips for Templatizing and Reusing It

Saving the table you build once as a template makes things much easier next time. Here are the points for making it easy to operate.

• Keep the formulas and swap only the data: Leave the composition ratio, cumulative composition ratio, and IF-judgment columns as formulas, and replace only the product names and sales.
• Parameterize the thresholds: Gather 70% and 90% into input cells and reference them from the formula so criteria changes take only a moment.
• Leave room in the total range: Setting the SUM range a bit wide means no need to fix the formula even as rows increase.

Things to Watch Out for When Doing It in Excel

• Don't forget to sort: If you haven't sorted in descending order, the cumulative composition ratio becomes meaningless and ranks won't come out correctly.
• Use absolute references: If you don't lock the total range and threshold cells with $, they shift when copied and cause miscalculation.
• Recalculate and re-sort after updating data: Once you update sales, get into the habit of redoing it from the sort.

Conclusion

ABC analysis in Excel can be done by anyone with this flow: (1) sort sales in descending order, (2) calculate the composition ratio, (3) calculate the cumulative composition ratio, (4) judge ranks with the IF function, and (5) visualize with a Pareto chart.

Once you've built a template, you can keep your monthly analysis running just by swapping in the data. Start by adding the two columns of composition ratio and cumulative composition ratio to your own sales data.