ABC Analysis in Excel: Running Totals, Thresholds, and Update Checks
Published:
Last Updated:
Category: Marketing Glossary
Authors: Shusaku Yosa
To perform ABC analysis in Excel, aggregate a chosen measure by item, sort in descending order, and calculate each item's share, running share, and class. Define the measure, reporting period, and threshold policy before writing formulas. A functioning spreadsheet can still produce inconsistent classifications.
This example classifies an item using the cumulative share before adding that item: below 70% is A; at least 70% but below 90% is B; 90% or above is C. These are illustrative policy choices, not universal business standards. See the ABC analysis guide for classification principles and the Pareto principle guide for the limits of the 80/20 idea.
Prepare one row per item and sort complete rows
- Choose one measure, such as sales revenue, gross profit, or material usage value.
- Aggregate by item ID using one reporting period, currency, tax treatment, and refund policy.
- Resolve duplicate IDs and inconsistent names before classification.
- Sort the entire table by value descending. Use item ID as a stable secondary key for equal values.
Sorting only the value column disconnects values from items. Classifying transaction lines separately can place the same product in several classes. Use the measure that answers your question: a sales ranking does not automatically identify the products with the greatest gross profit.
Set up the columns and threshold cells
The following fictional example uses ten products and sales revenue in Japanese yen excluding tax. Enter Item ID in A1, Sales in B1, Share in C1, Cumulative share in D1, and Class in E1. Data occupies rows 2–11. Set H2 to 0.7 and H3 to 0.9, and check that 0 < H2 < H3 < 1. B1 must be a text heading, not a number.
The “Before item” column below explains the classification and is not required for the formula in E.
Item | Sales (JPY) | Share | Cumulative | Before item | Class |
|---|---|---|---|---|---|
P01 | 3,000,000 | 30% | 30% | 0% | A |
P02 | 2,200,000 | 22% | 52% | 30% | A |
P03 | 1,600,000 | 16% | 68% | 52% | A |
P04 | 1,000,000 | 10% | 78% | 68% | A |
P05 | 700,000 | 7% | 85% | 78% | B |
P06 | 500,000 | 5% | 90% | 85% | B |
P07 | 400,000 | 4% | 94% | 90% | C |
P08 | 300,000 | 3% | 97% | 94% | C |
P09 | 200,000 | 2% | 99% | 97% | C |
P10 | 100,000 | 1% | 100% | 99% | C |
The total is JPY 10,000,000. P04 remains A because its preceding cumulative share is 68%, even though adding it takes the running share to 78%. P06 remains B because its preceding share is 85%; P07 becomes C because its preceding share is exactly 90%. A threshold-crossing item stays in the higher class under this policy.
Calculate shares, running totals, and classes
Enter the following formulas in row 2 and copy them down to row 11. They use the standard English function names and comma separators; adapt separators if your Excel locale requires it.
Cell | Formula |
|---|---|
C2 |
|
D2 |
|
E2 |
|
$B$2:$B$11 fixes the total range. In the cumulative formula, $B$2:B2 keeps the starting cell fixed while the end moves as you copy down. Microsoft documents this running-total approach.
The class formula sums the value before the current item. In E2, $B$1:B1 contains only the text heading, which SUM ignores, so the preceding value is zero. In E3 the endpoint moves to B2. Compare unrounded amounts with the total multiplied by each threshold; do not classify using rounded percentages displayed on screen.
A formula that tests the cumulative share after adding the item, such as IF(D2<=70%,...), implements a different boundary policy. It can be a valid choice, but it will not reproduce this example. Document the choice and apply it consistently.
Reconcile the results against known expectations
Check | Expected result | Investigate if different |
|---|---|---|
Total value | JPY 10,000,000 | Missing rows, duplicate items, wrong range |
Final cumulative share | 100% | Mismatch between numerator and total ranges |
A | 4 items; JPY 7,800,000 | P04 must be A |
B | 2 items; JPY 1,200,000 | P06 must be B |
C | 4 items; JPY 1,000,000 | P07 must be C |
A final share of 100% is necessary but does not prove the source data is correct. Duplicate records can still produce 100% when both the numerator and denominator include them. Reconcile item IDs, item count, reporting dates, and total value with the original source. Check that A + B + C returns the full total.
Use a Pareto chart to show concentration
In supported Excel versions, select a text category column and a numeric value column, then insert a Pareto chart. Follow Microsoft's instructions for your Excel version and platform. Selecting two numeric columns can instead group values into histogram bins, which is a different analysis.
For a chart that follows this exact table, use item labels, sales, and cumulative share in a combination of bars and a line. Set the cumulative percentage axis from 0% to 100% and check that categories have not been combined. A chart illustrates concentration; it does not choose your ABC policy.
Handle exceptions and refresh the whole calculation
- Zero total: shares are undefined. Investigate missing postings or an empty period instead of hiding the problem with IFERROR and zero percentages.
- Negative amounts: identify refunds or adjustments. The usual cumulative method assumes nonnegative values. Decide and document their treatment rather than silently deleting them.
- Missing or text values: distinguish unknown amounts from genuine zero sales. SUM may ignore a number stored as text.
- Ties: preserve a stable secondary sort key. If equal items must share a class at a boundary, adopt and document a different explicit policy.
- New rows: a fixed range does not automatically include them. Use a properly maintained Excel Table with adjusted references, or update every total, cumulative, and class range together.
Extending a range to row 100 is not a refresh strategy: data can exceed it, or unrelated totals can enter it. On each refresh, verify source count, total value, final row, copied formulas, sort order, and chart ranges.
Classification is a starting point for decisions. Before removing C items, consider launches, stockouts, and complementary products using the ABC decision-bias checklist. To organize the resulting initiatives and owners, explore Xtrategy's features.
