Blog · How-to guides · Sales teams
Rank customers into ten groups by revenue in Excel, with a reproducible rule for ties. Build a decile contribution table and check its customer counts and revenue against the source export.
Decile analysis in Excel divides a ranked customer list into ten groups, then compares how much revenue each group contributes. Start with one row per customer and a stated tie rule; dividing invoice rows would group transactions rather than customers.
Use a closed reporting period and net revenue in USD after credits and returns. Decide whether the population means customers with positive sales, all active customers or every account on a roster. Those choices change the denominator and must stay consistent when comparing periods.
For this worked example, a US distributor has twenty customers with positive net revenue in the selected period. On Invoices, customer ID is in A, invoice date in B and net revenue in C. On the customer sheet, put the period start in F1 and the next period's start in F2, both as real dates.
List distinct customer IDs in A2:A21. Calculate B2, then fill down:
=SUMIFS(Invoices!$C$2:$C$10000,Invoices!$A$2:$A$10000,A2,Invoices!$B$2:$B$10000,">="&$F$1,Invoices!$B$2:$B$10000,"<"&$F$2)
Extend the matching ranges to include the complete export. The exclusive next-period boundary includes transactions throughout the final day, including timestamps. Microsoft documents SUMIFS as adding cells meeting multiple criteria.
Check that customer IDs are unique and revenue cells are numeric. Reconcile their sum to the included invoice and credit rows before ranking. Do not silently discard an account whose net revenue is negative: either retain it in the defined population or disclose its treatment separately.
Sort the entire A:B table by revenue descending, then by customer ID ascending. Keep IDs in a consistent text format. This makes tied customers appear in the same order on every refresh.
In C2, enter the position formula and fill through C21:
=RANK.EQ(B2,$B$2:$B$21,0)+COUNTIF($B$2:B2,B2)-1
Microsoft's ranking reference explains that descending rank puts larger values first and assigns equal values the same rank. The expanding COUNTIF adds successive offsets within each tie, producing unique positions. Covirage is not affiliated with Microsoft.
Put the decile formula in D2 and fill through D21:
=ROUNDUP(C2*10/COUNT($B$2:$B$21),0)
This convention makes decile 1 the highest-revenue group. If the customer count is divisible by ten, group sizes are identical. Otherwise they differ by at most one; report the actual count in every group. The procedure requires at least ten customers and no missing revenue values.
The sorted customer sheet contains these rows. Revenue is shown in whole dollars; the calculations use the full stored values.
| Customer ID | Revenue (USD) | Unique position | Decile |
|---|---|---|---|
| C01 | 200,000 | 1 | 1 |
| C02 | 150,000 | 2 | 1 |
| C03 | 150,000 | 3 | 2 |
| C04 | 100,000 | 4 | 2 |
| C05 | 80,000 | 5 | 3 |
| C06 | 70,000 | 6 | 3 |
| C07 | 60,000 | 7 | 4 |
| C08 | 50,000 | 8 | 4 |
| C09 | 40,000 | 9 | 5 |
| C10 | 35,000 | 10 | 5 |
| C11 | 30,000 | 11 | 6 |
| C12 | 25,000 | 12 | 6 |
| C13 | 20,000 | 13 | 7 |
| C14 | 18,000 | 14 | 7 |
| C15 | 16,000 | 15 | 8 |
| C16 | 14,000 | 16 | 8 |
| C17 | 12,000 | 17 | 9 |
| C18 | 10,000 | 18 | 9 |
| C19 | 8,000 | 19 | 10 |
| C20 | 6,000 | 20 | 10 |
| Total | 1,094,000 |
C02 and C03 have identical revenue but lie on opposite sides of a boundary. Customer ID determines their order; the split carries no commercial distinction between them. Keeping all equal values together would answer a different grouping question and sacrifice equal counts.
List deciles 1 through 10 in H2:H11. Enter customer count in I2, revenue in J2 and revenue share in K2, then fill down:
=COUNTIF($D$2:$D$21,H2)
=SUMIFS($B$2:$B$21,$D$2:$D$21,H2)
=J2/SUM($B$2:$B$21)
| Decile | Customers | Revenue (USD) | Revenue share |
|---|---|---|---|
| 1 | 2 | 350,000 | 31.99% |
| 2 | 2 | 250,000 | 22.85% |
| 3 | 2 | 150,000 | 13.71% |
| 4 | 2 | 110,000 | 10.05% |
| 5 | 2 | 75,000 | 6.86% |
| 6 | 2 | 55,000 | 5.03% |
| 7 | 2 | 38,000 | 3.47% |
| 8 | 2 | 30,000 | 2.74% |
| 9 | 2 | 22,000 | 2.01% |
| 10 | 2 | 14,000 | 1.28% |
| Total | 20 | 1,094,000 | 100.00% |
Shares are rounded to two decimal places after calculation; displayed detail adds to 99.99% because of rounding. Verify =SUM(I2:I11)-COUNTA(A2:A21) and =SUM(J2:J11)-SUM(B2:B21) both return zero. Unrounded shares must sum to one.
The highest two deciles contribute 600,000 / 1,094,000 = 54.84% of revenue. For contribution cutoffs rather than equal customer counts, use ABC customer analysis and Pareto analysis. For recency and frequency alongside spending, use B2B RFM analysis.
Bring your customer revenue workbook to Covirage, including the period and any decile labels you have assigned. Covirage maps the columns, its tools compute and check the group comparisons, and the external AI model explains the results with every figure traced to its rows. Create a free Covirage account and bring a sample of your own export.
See Covirage for sales teams for the supporting customer-analysis workflow.
Either convention is possible, so label it explicitly. This workflow assigns decile 1 to the highest-revenue customers and decile 10 to the lowest. Revenue rank alone does not establish profitability, retention risk or the appropriate level of sales coverage.
For equal-count groups, use a stable secondary ordering, such as customer ID ascending. Equal-revenue customers can then fall into adjacent deciles. If keeping ties together matters more, allow unequal group sizes and disclose that different convention rather than presenting the groups as equal-count.
You can calculate positions, but ten populated customer groups are impossible with fewer than ten customers. Use fewer named groups or show the customer rows directly. Decile summaries also become fragile when each group contains very few accounts.
Deciles divide customers by count after ranking them. ABC analysis generally assigns tiers using cumulative contribution thresholds. A decile can contribute a large or small share of revenue; it does not have to contain a tenth of total sales.