A step-by-step guide to building a cross-sell or whitespace matrix in Excel from an invoice export: a customers-by-categories grid with SUMIFS or a pivot table, a bought or not-bought flag, category penetration within each customer segment, the expected categories for each segment, the gap cells where a customer does not buy what most similar customers do, a value for each gap from the segment median, and the ranked opportunity list. Includes the exact formulas, the check, and the point at which the spreadsheet stops being enough.
A cross-sell matrix shows who buys what. With a segment column and a penetration table it also shows who does not buy what they would be expected to. This guide builds both in Excel.
Sheet Invoices: customer in A, date in B, category in C, amount in D. Sheet Customers: customer in A, segment in B. On Calc: period start B1, end B2, expected floor 0.5 in B3.
The quickest route is a pivot table: Customer in Rows, Category in Columns, Amount in Values, Date filtered to the period. For a formula version, on sheet Matrix, customers in A3 down, categories in C2 across, segment in B3:
=XLOOKUP(A3,Customers!A:A,Customers!B:B,"unassigned")
Cell C3, filled across and down:
=SUMIFS(Invoices!$D:$D,Invoices!$A:$A,$A3,Invoices!$C:$C,C$2,Invoices!$B:$B,">="&Calc!$B$1,Invoices!$B:$B,"<="&Calc!$B$2)
Customer total at the end of the row, N3 if there are eleven categories:
=SUM(C3:M3)
=SUM(Matrix!N3:N5000)-SUMIFS(Invoices!D:D,Invoices!B:B,">="&Calc!B1,Invoices!B:B,"<="&Calc!B2)
Zero. If not, a category on the invoices is missing from the header row, or a customer is missing from the list.
On sheet Norm, segments in A3 down, the same categories in C2 across. Customers in the segment, B3:
=COUNTIFS(Matrix!$B:$B,$A3)
Penetration, C3, filled across and down:
=COUNTIFS(Matrix!$B:$B,$A3,Matrix!C:C,">0")/$B3
| Segment | Customers | Pipe and fittings | Cable | Timber | Aggregates |
|---|---|---|---|---|---|
| Builder | 120 | 70% | 65% | 97% | 92% |
| Plumber | 85 | 98% | 30% | 40% | 20% |
| Electrician | 60 | 20% | 99% | 30% | 10% |
Anything at or above the floor is expected for that segment.
For valuing gaps, the typical share of spend a buying customer puts in the category. Add a sheet Share with the same layout as Matrix, each cell the revenue cell over the customer total. Then on a sheet NormShare, laid out like Norm, Microsoft 365:
=MEDIAN(FILTER(Share!C$3:C$5000,(Matrix!$B$3:$B$5000=$A3)*(Matrix!C$3:C$5000>0)))
In older Excel this is an array formula with nested IF, entered with Ctrl+Shift+Enter.
On sheet Gaps, same layout. A gap exists where the customer buys nothing and the category is expected for its segment. Its value is the median share times the customer total:
=IF(AND(Matrix!C3=0,INDEX(Norm!C$3:C$50,MATCH(Matrix!$B3,Norm!$A$3:$A$50,0))>=Calc!$B$3),INDEX(NormShare!C$3:C$50,MATCH(Matrix!$B3,Norm!$A$3:$A$50,0))*Matrix!$N3,0)
Conditional formatting on the revenue matrix, highlighting cells where the matching gap cell is above zero, turns the grid into the picture people expect: coloured holes where a customer is missing a category its peers buy.
A grid is for looking at. A rep needs rows. Unpivot the gap matrix into customer, category and value, keep the rows above zero, and sort descending. The reliable route in any version is Power Query: select the gap table, Data, From Table, then Transform, Unpivot Other Columns on the customer column, filter the value column to greater than zero, and sort.
| Customer | Segment | Category | Segment penetration | Gap value |
|---|---|---|---|---|
| Account 1 | Builder | Aggregates | 92% | £15,100 |
| Account 1 | Builder | Timber, underbought | 97% | £16,800 |
| Account 14 | Plumber | Pipe and fittings | 98% | £9,300 |
Add the account owner and the list is ready to hand out.
A zero cell is the obvious gap. A customer buying a tenth of the segment median in a category is nearly as interesting. Extend the rule: gap equals the expected amount, median share times customer total, less what the customer spends, where the customer is below some fraction of expected, such as half. The category share by trade worked example shows both kinds on five accounts.
No segments. Every customer compared with everyone; plumbers told to buy timber.
Unassigned customers defaulted. A fifth of the base has no segment and gets a builder norm it may not fit. Show them as unassigned and count them.
Gaps summed into a pipeline. The values are norms, not forecasts. Rank with them.
Part-number level. Ten thousand columns, nearly all empty, and no conversation a rep recognises.
One-off buyers counted as buying. A single small purchase two years ago marks the category as held. Use a period and a minimum amount.
A matrix for one branch and one period is a good spreadsheet job. Norms by segment recomputed monthly, underbought as well as unbought, parents rolled up, owners attached and last month compared with this month is where it strains; see the four signs a spreadsheet is no longer enough. For the idea behind it, see whitespace and share of wallet and products per customer. Covirage builds the matrix, the norms and the ranked gap list from the invoice export and the customer master, with the check built in.
Because what a customer should buy depends on what kind of customer it is. An electrician does not buy aggregates. Penetration across all customers would mark that as a gap for every electrician. Within the segment, aggregates has low penetration, is not expected, and no gap is raised.
Conservatively. Take the median spend in the category among customers of the segment who do buy it, as a share of their total spend, and apply that share to this customer's total. It is an estimate of what a typical similar customer would spend, not a forecast. Rank by it; do not add the gaps up and call the sum a pipeline.
Between six and twenty. Fewer hides the gaps; more makes cells too sparse for penetration to mean anything, and the matrix unreadable. Use the product hierarchy level a rep would recognise as a conversation: fixings, cable, pipe, not individual part numbers.