A step-by-step guide to building a dormant customer list in Excel from an order export: last order date per customer with MAXIFS, days since last order, each customer's typical gap between orders, a per-customer threshold, the dormant flag, prior-year revenue beside each name, and the check that every customer is in exactly one state. Includes the exact formulas for Microsoft 365 and older Excel, and the point at which the spreadsheet stops being enough.
A dormant customer is one who has stopped ordering for longer than their own pattern says they should. In Excel it is six columns on an order export. This guide gives the formulas, the pivot route and the check.
One sheet, Orders, one row per order line:
| Column | Content |
|---|---|
| A | Customer |
| B | Order date, as a real date |
| C | Amount |
On a sheet named Calc, put the as-of date in B1. Use a fixed date rather than TODAY(), so the list is reproducible. Put the multiple, 2.5, in B2.
Microsoft 365, in Calc!A5:
=UNIQUE(FILTER(Orders!A2:A50000,Orders!A2:A50000<>""))
Older Excel: copy the column and use Data, Remove Duplicates.
Last order, B5:
=MAXIFS(Orders!B:B,Orders!A:A,A5)
First order, C5:
=MINIFS(Orders!B:B,Orders!A:A,A5)
Several lines on one day are one order for this purpose. Microsoft 365, D5:
=ROWS(UNIQUE(FILTER(Orders!B$2:B$50000,Orders!A$2:A$50000=A5)))
Older Excel: build a pivot with Customer and Date in Rows, then count rows per customer, or accept COUNTIFS(Orders!A:A,A5) if the export is one row per order.
E5:
=IF(D5>=3,(B5-C5)/(D5-1),NA())
This is the average gap in days. It is slightly pulled by one long break; a median of the gaps is better and needs a helper column on the order sheet, sorted by customer then date: in Orders!D2, =IF(A2=A1,B2-B1,""), then in 365 =MEDIAN(FILTER(Orders!D:D,(Orders!A:A=A5)*(Orders!D:D<>"")*(Orders!D:D>0))). Start with the average; move to the median if one-off breaks are common.
Days since, F5:
=$B$1-B5
Threshold, G5:
=IFERROR($B$2*E5,"n/a")
State, H5:
=IF(D5<3,"Too few orders",IF(F5>G5,"Dormant","Active"))
Prior-year revenue, I5, with the prior year's start and end in D1 and D2:
=SUMIFS(Orders!C:C,Orders!A:A,A5,Orders!B:B,">="&$D$1,Orders!B:B,"<="&$D$2)
Sort the dormant rows by this column. In 365:
=SORT(FILTER(A5:I5000,H5:H5000="Dormant"),9,-1)
That is the list: dormant customers, largest prior revenue first, with days since last order and their own threshold beside each.
By count:
=COUNTIF(H5:H5000,"Dormant")/COUNTIFS(I5:I5000,">0")
By value:
=SUMIFS(I5:I5000,H5:H5000,"Dormant")/SUM(I5:I5000)
Show both. Fifteen percent by count and two percent by value is a tail resting; fifteen and twenty is a problem.
Every customer in exactly one state:
=COUNTIF(H5:H5000,"Dormant")+COUNTIF(H5:H5000,"Active")+COUNTIF(H5:H5000,"Too few orders")-COUNTA(A5:A5000)
This must be zero.
TODAY() as the as-of date. The list changes every time the file opens, and nobody can reproduce last week's.
Order lines counted as orders. A customer with forty lines on two dates looks like a frequent buyer.
Seasonal customers. A customer who orders every spring has a twelve-month gap. The average gap handles it; a fixed rule lists them every summer.
Credits as orders. A credit note dated last week resets the last order date. Filter to positive amounts for the date columns.
One as-of date on one export is fine in Excel. Every week, with owner names from the CRM, touch dates beside each account, and last week's list compared to this week's, is where it strains. See the four signs a spreadsheet is no longer enough. For the arithmetic by hand, see dormancy on ten accounts; for what the rate should be, see what is a good dormancy rate. Covirage builds the same list from the same export every week, with the threshold per account and the check built in.
Because 90 days is normal for a customer who orders twice a year and far too late for one who orders weekly. The fixed rule fills the list with customers who are fine and leaves off the ones who have missed ten orders. Two extra columns, the customer's own gap and a multiple of it, fix both.
MAXIFS arrived in Excel 2019. In older versions use an array formula, =MAX(IF(customers=customer,dates)), entered with Ctrl+Shift+Enter, or build a pivot table with Customer in Rows and Date in Values summarised by Max.
At least three order dates, giving two gaps. With fewer, show the customer with a fallback threshold, such as the median threshold of its segment, and label it. One-order customers are a different list: they never came back, which is an onboarding question rather than a dormancy one.