Excel guide · 4 min read
How to calculate customer concentration in Excel
=MAX(C2:C11)/SUM(C2:C11) and the top 5 share is =SUM(LARGE(C2:C11,{1,2,3,4,5}))/SUM(C2:C11). In the sample, one customer brings in 30.5% of revenue and the top 5 bring in 82.6%.Sample data
Every result on this page was calculated from this sheet by the same spreadsheet engine the Cellwise app uses.
| Row | A | B | C | D | E | F | G | H |
|---|---|---|---|---|---|---|---|---|
| 1 | Customer | FY2024 | FY2025 | Share 2025 | Over threshold | Threshold | 0.1 | |
| 2 | Northwind Traders | 290000 | 412000 | 0.30518518519 | TRUE | |||
| 3 | Harbor Foods | 240000 | 268000 | 0.19851851852 | TRUE | |||
| 4 | Atlas Retail | 180000 | 195000 | 0.14444444444 | TRUE | |||
| 5 | Brightline Group | 150000 | 142000 | 0.10518518519 | TRUE | |||
| 6 | Cedar & Co | 110000 | 98000 | 0.072592592593 | FALSE | |||
| 7 | Delta Supply | 85000 | 76000 | 0.056296296296 | FALSE | |||
| 8 | Evergreen Stores | 70000 | 61000 | 0.045185185185 | FALSE | |||
| 9 | Fjord Ltd | 52000 | 44000 | 0.032592592593 | FALSE | |||
| 10 | Granite Works | 40000 | 31000 | 0.022962962963 | FALSE | |||
| 11 | Halo Studio | 33000 | 23000 | 0.017037037037 | FALSE |
Download this sheet as an Excel practice file (.xlsx)
Start from revenue by customer
You need one row per customer with their revenue for the period. If you have an invoice list instead, build the table first: in Excel 365, =UNIQUE(invoices[Customer]) lists the customers and =SUMIFS(invoices[Amount],invoices[Customer],A2) totals each one. Here A2:A11 hold ten customers with FY2024 revenue in B and FY2025 in C.
Clean the customer names before you total them. "Northwind Traders" and "Northwind Traders Ltd" count as two customers, which makes you look less concentrated than you are. The same goes for several sites of one group billed separately: if they'd all leave together, combine them.
The largest customer's share
In D2, =C2/SUM($C$2:$C$11) gives each customer's share of the total; format it as a percentage and fill down. The $ signs keep the total fixed as you fill. Percentage of total in Excel covers this pattern in more detail.
For the headline number you don't need the column at all. MAX finds the biggest revenue wherever it sits, so the formula still works next year when a different customer is the largest. To name that customer, =XLOOKUP(MAX(C2:C11),C2:C11,A2:A11) returns Northwind Traders.
Result: 0.30518518519 shows as 30.5% with Percent format
0.305, 30.5%: Northwind Traders' 412,000 out of 1,350,000 of FY2025 revenue.
Top 5 and top 10 share
LARGE with an array of positions returns the 5 biggest values, and SUM adds them. Because the positions are a constant, this works without Ctrl+Shift+Enter in any version. For the top 10, use {1,2,3,4,5,6,7,8,9,10}, or SEQUENCE(10) in Excel 365.
In Excel 365 you can also sort and take: =SUM(TAKE(SORT(C2:C11,,-1),5))/SUM(C2:C11) gives the same 82.6%. Both handle ties: if two customers have the same revenue, LARGE returns that value twice. Top 5 values in Excel shows how to list the names next to them.
Result: 0.82592592593 shows as 82.6% with Percent format
0.826, 82.6%: the five largest customers brought in 1,115,000 of the 1,350,000.
Should I group the long tail as "All other customers"?
Not in the table you measure. A single "Other" row of many small customers can show up as your biggest "customer" and inflate both the top 5 share and the HHI. Keep every customer on its own row, or leave the "Other" row out of the LARGE and HHI ranges while keeping it in the total.
Flag customers above a threshold
Lenders, buyers and auditors often ask which customers are above some share of revenue, and 10% is a common cut-off. Put the threshold in H1 rather than in the formula so you can change it. In E2, =D2>=$H$1 returns TRUE for customers at or above it.
To list them, =FILTER(A2:A11,E2:E11) spills Northwind Traders, Harbor Foods, Atlas Retail and Brightline Group in Excel 365. Brightline is only just over the line at 10.5%, which is worth knowing before someone asks.
Result: 4
4: four customers each bring in 10% or more of FY2025 revenue.
The Herfindahl-Hirschman index (HHI) and the trend over years
The top 5 share ignores how revenue is split inside the top 5. The HHI looks at every customer: square each share and add them up. SUMPRODUCT does it in one cell. Multiplying by 10,000 gives the usual scale, where 10,000 means a single customer and smaller numbers mean revenue is spread wider.
A handy way to read it: 1 divided by the HHI (before the x 10,000) is the number of equal-sized customers that would give the same concentration. FY2025's 1,768 works out to about 5.7 equal customers, down from about 6.9 in FY2024. US merger guidelines treat markets above 1,800 as highly concentrated, but there's no official cut-off for a customer base, so watch your own trend.
Run the same formulas on column B for the year before. Revenue grew by 100,000, but Northwind alone grew by 122,000 while most others shrank: the largest share went from 23.2% to 30.5%, the top 5 from 77.6% to 82.6%, and the HHI from 1,448 to 1,768. Growth that comes from one customer makes the business more dependent on it. Pair this with net revenue retention to see whether the rest of the base is shrinking.
Result: 1768.3621399
1,768 (rounded): the HHI for FY2025, up from 1,448 for FY2024 with the same formula on B2:B11.
How do I get the HHI for the earlier year?
Point the same formula at the FY2024 column.
Result: 1447.7952
1,448 (rounded): revenue was spread more evenly in FY2024.
Common mistakes
Pointing the largest-share formula at one fixed row
Use MAX over the whole column so it still works when another customer becomes the largest.
Duplicate customer names
Clean names and combine related accounts before totalling, or concentration looks lower than it is.
Grouping small customers into one "Other" row
Keep each customer separate, or exclude the Other row from LARGE and the HHI while keeping it in the total.
Reading one year in isolation
Calculate the same measures for each year side by side. A rising share matters more than the level.
FAQ
What is a customer concentration ratio?
The share of total revenue that comes from your largest customer or largest few customers, for example the top 5. In Excel it's the sum of the largest values divided by the total.
How do I calculate the top 10 customers' share in Excel?
Use =SUM(LARGE(range,{1,2,3,4,5,6,7,8,9,10}))/SUM(range), or =SUM(LARGE(range,SEQUENCE(10)))/SUM(range) in Excel 365.
How do I calculate the HHI in Excel?
Use =SUMPRODUCT((range/SUM(range))^2)*10000. It squares each customer's share of the total and adds them up; a higher number means more concentrated.
What counts as high customer concentration?
There's no single rule. A common flag is any one customer at 10% or more of revenue, and many people also watch the top 5 share and whether it's rising year after year.
Functions used
MAXLargest numberSUMAdds numbersLARGEk-th largestXLOOKUPModern lookup in any directionTAKEFirst or last rows, spillsSORTSorts a range, spillsSUMPRODUCTMultiply then addCOUNTIFCounts cells that matchFILTERKeeps matching rows, spillsUNIQUERemoves duplicates, spillsSUMIFSAdds with several conditions
Related guides
- How to calculate percentage of total in Excel
- How to average the top N values in Excel
- How to calculate net revenue retention (NRR) in Excel
- How to find the top 5 values in Excel
- How to do a spend analysis in Excel
- How to sum by day of the week in Excel
- How to calculate takt time in Excel
Updated