Blog · How-to guides · Sales teams
Build an opening-to-closing customer roster from account snapshots and sales exports. Separate losses, new customers and reactivations, then reconcile counts and compare the revenue associated with lost accounts.
Customer churn analysis in Excel works best as a customer roster reconciliation before it becomes a percentage. Match opening and closing relationships, separate losses from additions, and attach sales only after every account has one state. A stable customer count can conceal substantial turnover underneath it.
Use an account master, opening and closing status exports, historical relationship records, and invoice lines with customer ID, posting date and signed net revenue. This example is a US service supplier's September 2026 review: opening status is August 31 and closing status is September 30. Active means an effective service agreement at the snapshot date.
Use a stable ID rather than customer names. Build the roster from the union of all IDs in both snapshots and history; otherwise a disappeared account may disappear from the analysis too. Resolve duplicate IDs, mergers and account-number changes before matching. Freeze the opening segment so an account transfer cannot move a loss between segments retrospectively.
For businesses that sell on orders, use the definition in B2B churn without a subscription. That page owns the buying-cadence decision; this workflow starts after the active-status rule is agreed.
Create an Excel table named Roster. Its first columns are CustomerID, Segment, OpeningActive, ClosingActive and PriorHistory. The flags contain numeric 1 or 0; PriorHistory means an active relationship existed before the reporting period. Attach August and September revenue as PriorRevenue and CurrentRevenue, then add State.
| CustomerID | Segment | OpeningActive | ClosingActive | PriorHistory | PriorRevenue (USD) | CurrentRevenue (USD) | State |
|---|---|---|---|---|---|---|---|
| A | Wholesale | 1 | 1 | 1 | 20,000 | 22,000 | Retained |
| B | Wholesale | 1 | 0 | 1 | 12,000 | 0 | Lost |
| C | Service | 1 | 1 | 1 | 8,000 | 9,000 | Retained |
| D | Service | 1 | 0 | 1 | 10,000 | 0 | Lost |
| E | Wholesale | 0 | 1 | 0 | 0 | 6,000 | New |
| F | Service | 0 | 1 | 1 | 0 | 4,000 | Reactivated |
| G | Service | 0 | 0 | 1 | 0 | 0 | Inactive |
| Total | 4 | 4 | 50,000 | 41,000 |
In the State column enter:
=IF([@OpeningActive]=1,IF([@ClosingActive]=1,"Retained","Lost"),IF([@ClosingActive]=1,IF([@PriorHistory]=1,"Reactivated","New"),"Inactive"))
The formula assumes the flags have passed validation. Reject blanks and values outside 0 and 1 rather than allowing them to become inactive by default. An opening-active account with no prior history is also an exception to investigate.
Keep the historical inactive account G. It proves that the workbook distinguishes old relationships from first-time customers. The separate cohort retention table tutorial follows customers from their acquisition periods; do not reset those cohorts when accounts reactivate.
Name the invoice-line table Sales, with CustomerID, PostingDate and NetRevenue. NetRevenue includes signed credits and excludes sales tax. For PriorRevenue and CurrentRevenue use:
=SUMIFS(Sales[NetRevenue],Sales[CustomerID],[@CustomerID],Sales[PostingDate],">="&DATE(2026,8,1),Sales[PostingDate],"<"&DATE(2026,9,1))
=SUMIFS(Sales[NetRevenue],Sales[CustomerID],[@CustomerID],Sales[PostingDate],">="&DATE(2026,9,1),Sales[PostingDate],"<"&DATE(2026,10,1))
The exclusive upper date includes timestamps throughout the final day. Microsoft documents SUMIFS as adding values that meet multiple criteria. Reconcile each revenue column to its corresponding sales export, and investigate invoice IDs whose customers do not appear in the roster.
The two lost accounts had $22,000 of August revenue: $12,000 in Wholesale and $10,000 in Service. This is prior-period revenue associated with lost accounts. It is neither a recurring-revenue forecast nor necessarily September's lost sales. A contract can end after earning revenue during the month.
Put Wholesale and Service in a small summary's Segment column. Its lost-customer count and associated prior revenue are:
=COUNTIFS(Roster[Segment],[@Segment],Roster[State],"Lost")
=SUMIFS(Roster[PriorRevenue],Roster[Segment],[@Segment],Roster[State],"Lost")
Microsoft documents COUNTIFS as counting records meeting multiple criteria. Covirage is not affiliated with Microsoft.
The count bridge is opening 4 minus lost 2 plus new 1 plus reactivated 1 equals closing 4. Retained 2 plus lost 2 must equal opening 4; retained 2 plus new 1 plus reactivated 1 must equal closing 4. This Excel check must return zero:
=SUM(Roster[OpeningActive])-COUNTIFS(Roster[State],"Lost")+COUNTIFS(Roster[State],"New")+COUNTIFS(Roster[State],"Reactivated")-SUM(Roster[ClosingActive])
The revenue bridge also closes: $50,000 less $22,000 at lost accounts, plus $3,000 retained-account growth, plus $6,000 new and $4,000 reactivated revenue equals $41,000. Retained revenue moves from $28,000 to $31,000. No rounding is needed for these whole-dollar figures.
Account B has a documented customer closure; D's reason is unknown. Keep the evidence reference and follow-up owner beside each record. Neither a blank reason nor an account's segment proves why it left. Use customer value analysis for prioritization after reconciling the roster.
Bring your account snapshots, customer history and sales export; Covirage maps the columns, and its tools compute and check customer counts and revenue comparisons. The external AI model explains the result with every figure traced to its rows. Create a free Covirage account and bring a sample of your own data.
See Covirage for sales teams for the business context.
Invoices show purchasing activity, not necessarily a terminated relationship. For subscriptions, use effective contract status. For order-based businesses, agree a lapse rule based on buying cadence and preserve the date that rule was applied. Keep late invoices and credit memos separate from evidence of an active customer.
A customer with an earlier active relationship is a reactivation when it returns after being inactive at the opening date. Keep historical IDs so the workbook can make that distinction. A changed account number or merger requires an approved mapping before classifying the customer.
A recorded reason describes what an account owner or customer reported. It does not establish causation. Preserve the source and recording date, leave missing reasons unknown, and investigate competing explanations before attributing losses to pricing, service or an individual sales representative.