This article is about ABC analysis in Excel. Don’t confuse it with the ABS function. The free !SEMTools procedure described at the end does it in two clicks.
ABC analysis is one of the most effective methods of classifying and allocating resources. It’s useful in many areas: marketing (traffic sources, campaigns), sales (customers, product range), logistics (routes, warehouse turnover) and so on.
As a marketer, I use it in my work a lot. Maybe you’ve heard of it (that’s why you’re here) but aren’t quite sure how to do ABC analysis in Excel. It’s actually very simple! I’ll try to explain it as simply and as thoroughly as possible.
The principle of ABC analysis
ABC analysis is based on the Pareto principle, also known as the 80/20 rule: in almost every area of life, about 80% of the result comes from about 20% of the effort.
In statistical terms, a small part of the elements (group A) makes up most of the total by some metric (revenue, volume, visits), while most of the elements (groups B and C) matter less. The Pareto principle divides everything into two categories, important and unimportant; ABC analysis adds an intermediate one, so there are three.
The method is named after the letters of these categories. Group A contains the most important elements that bring most of the result, group B the medium ones, and group C the least significant ones.
How to do ABC analysis in Excel
Say you have a small table of traffic sources with the number of visits from each over a period. The task is to find the priority sources (those that bring about 80% of the traffic), the secondary ones and the least significant ones, to focus your attention and effort. The table is for demonstration only: in reality, ABC analysis makes sense on much longer lists.
| Source | Visits |
|---|---|
| Organic | 5,200 |
| Direct | 1,800 |
| 900 | |
| Paid | 3,100 |
| Social | 400 |
| Referral | 300 |
| Affiliates | 150 |
| Other | 50 |
Sort the data
First, sort the data by visits in descending order (Data → Sort), so that the most significant sources are at the top.
Calculate the running total
Add a column with the running total: it starts with the first source and adds each next one to the sum. With the visits in B2:B9:
=SUM($B$2:B2)
Calculate the cumulative share
Divide the running total by the grand total and format it as a percentage. This is the cumulative share:
=SUM($B$2:B2)/SUM($B$2:$B$9)
Assign the categories A, B and C
- Category A: sources within the first 80% of the cumulative share.
- Category B: the next 15% (more than 80%, up to 95%).
- Category C: the rest.
=IF(D2<=0.8,"A",IF(D2<=0.95,"B","C"))
| Source | Visits | Cumulative share | Class |
|---|---|---|---|
| Organic | 5,200 | 44% | A |
| Paid | 3,100 | 70% | A |
| Direct | 1,800 | 85% | B |
| 900 | 92% | B | |
| Social | 400 | 96% | C |
| Referral | 300 | 98% | C |
| Affiliates | 150 | 99.6% | C |
| Other | 50 | 100% | C |
The 80/95 thresholds are a convention: some use 70/90 or 75/95. Choose them for your task and keep them consistent. For a clearer distinction, fill the groups with different colors.
ABC analysis without sorting
If you don’t want to sort the source table, SUMIF calculates for each row the cumulative share it would have in a sorted list. The formula above then works as is:
=SUMIF($B$2:$B$9,">="&B2)/SUM($B$2:$B$9)
Visualizing the results
Results are usually visualized with cell fill colors, as above. Charts comparing the share of elements by the metric with their share by count are also popular: a Pareto chart (Insert → Statistic Chart → Pareto in Excel 2016 and later) shows exactly that.
On my own site, the Pareto principle holds more than well: not 20%, but 7% of the pages generate 80% of all page views. Those are the best articles, though, so in terms of effort it may well be the same 20% :) At the same time, 83% of the pages generate only 5% of the views. Clearly, not all content is equally useful and in demand.
ABC analysis in 2 clicks with !SEMTools
As you can see, ABC analysis in Excel isn’t that simple: several intermediate steps, sorting, running totals, cumulative shares, labeling and formatting the groups.
What if you could skip all that and do ABC analysis without even sorting? I thought so too and created a free procedure in my !SEMTools add-in. The data doesn’t need to be sorted: the add-in orders it in memory, calculates all the shares and labels the groups A, B and C with colors. Again: even in the free starter version.
Do ABC analysis of any list in 2 clicks: no sorting, no running totals, groups labeled with colors, for free.
!SEMTools solves this and hundreds of other tasks right in Excel!
Summary
I hope you found what you were looking for on this page and were able to do the ABC analysis of your data. Sort, calculate the cumulative share, assign the classes by thresholds, or use SUMIF to skip sorting. And for regular analysis, the free !SEMTools procedure does it all in two clicks. More metrics are in the statistical metrics section.