A step-by-step guide to computing customer concentration in Excel from an invoice export: revenue per customer with SUMIFS, the top-ten share with LARGE, the largest single customer share, the same figures for the prior year, the pivot-table route for older versions, and the one check that proves the customer totals still add up to the ledger. Includes the exact formulas for Microsoft 365 and for older Excel, and the point at which the spreadsheet stops being enough.
Customer concentration is the share of revenue that comes from the largest customers. In Excel it is four formulas on top of an invoice export. This guide gives them, the pivot alternative, and the check.
One sheet, named Invoices, one row per invoice line:
| Column | Content |
|---|---|
| A | Customer |
| B | Invoice date, as a real date |
| C | Amount, net of credits |
Credits must be in the data as negative rows. Put the period on a sheet named Calc: start date in B1, end date in B2.
Microsoft 365, in Calc!A5:
=UNIQUE(FILTER(Invoices!A2:A50000,Invoices!A2:A50000<>""))
Older Excel: copy column A to Calc!A5, then Data, Remove Duplicates.
In Calc!B5, filled down, or spilled with A5# in 365:
=SUMIFS(Invoices!C:C,Invoices!A:A,A5,Invoices!B:B,">="&$B$1,Invoices!B:B,"<="&$B$2)
Before any ratio, prove nothing was lost:
=SUM(B5:B5000)-SUMIFS(Invoices!C:C,Invoices!B:B,">="&$B$1,Invoices!B:B,"<="&$B$2)
This must be zero. If it is not, a customer is missing from the list, usually a blank or a trailing space.
Microsoft 365:
=SUM(LARGE(B5:B5000,SEQUENCE(10)))/SUM(B5:B5000)
Older Excel:
=SUMPRODUCT(LARGE(B5:B5000,ROW(1:10)))/SUM(B5:B5000)
=MAX(B5:B5000)/SUM(B5:B5000)
And their name:
=INDEX(A5:A5000,MATCH(MAX(B5:B5000),B5:B5000,0))
Add prior-year dates in C1 and C2, repeat Step 2 in column C with those cells, and repeat Steps 4 and 5 on column C. Two numbers side by side: top-ten share now, top-ten share a year ago.
For each top account, the change against its own prior year:
=IF(C5=0,"new",B5/C5-1)
Insert a pivot table on the invoice data. Customer to Rows, Amount to Values, Date to Filters. Add Amount to Values a second time, right-click, Show Values As, % of Grand Total. Sort descending. The first ten percentages summed is the top-ten share. The pivot is quicker and has no check; add the control total beside it by hand.
Microsoft 365, customers sorted by revenue with share and cumulative share:
=SORTBY(A5:B5000,B5:B5000,-1)
Then share in the next column as revenue over SUM, and cumulative share as a running sum. The row where cumulative share crosses 50 percent is the number of customers that make half the revenue, which a board usually finds more vivid than the ratio.
Duplicate customers. One customer under two spellings halves its share. Map names to a parent first.
Dates stored as text. SUMIFS silently returns zero for them. Test with =ISNUMBER(Invoices!B2).
Credits missing. Gross invoices overstate everyone, and the largest customers most.
Hard-coded ranges. The export grows past row 5,000 and the totals quietly stop matching. The check in Step 3 catches it.
This works for one period, one entity and a clean customer list. It gets fragile when concentration is wanted by margin as well as revenue, per rep and per region, every month, with parent mapping maintained by several people. The four signs a spreadsheet is no longer enough covers that point, and the five checks before trusting a spreadsheet total are worth running on this workbook. For what the number should be once you have it, see what is a good customer concentration, and for the same arithmetic by hand, the worked example on ten customers. Covirage runs the same calculation from the same export, with the check built in.
SUMIFS for revenue per customer in a date range, LARGE for the top ten, MAX for the largest, and SUM for the total. In Microsoft 365, UNIQUE builds the customer list and SEQUENCE feeds LARGE. In older Excel, use Remove Duplicates for the list and ROW(1:10) inside SUMPRODUCT instead of SEQUENCE.
Before anything else. Concentration is understated whenever one customer is split across two spellings or two account numbers. Add a mapping sheet with every raw name and the parent it belongs to, look the parent up with XLOOKUP or VLOOKUP into a new column, and total by parent. The check total will not catch this; only the mapping does.
Yes: =SUMPRODUCT((totals/SUM(totals))^2). It runs from near zero for a very spread base to one for a single customer. It is useful for tracking one book over time and less intuitive than the top-ten share for a board, so show both.