Customer List with Last Purchase (Excel)
Your existing customers are the cheapest sales you will ever make, if you notice when they go quiet. Keep every customer's details on one sheet and record purchases on another; the list then shows when each customer first and last bought, how many orders they placed, their total and average order, and whether they are Active, At risk or Lapsed. A summary lists your top ten customers and sales by segment.
Free · no sign-up · opens in Microsoft Excel, Google Sheets, LibreOffice and WPS Office. Files contain no macros.
Preview
Real view of the file with its sample data (yellow cells are where you type).
| ID | Customer name | Contact person | Phone | City / region | Segment | Account owner | Customer since | Last purchase | Days since last purchase | Orders | Total sales (₹) | Average order (₹) | Status | |
| C001 | Blue Ocean Logistics | Rahul Verma | 98200 33445 | rahul@blueocean.example | Mumbai | Corporate | Karan | Jun-26 | Oct-26 | 25 | 6 | 6,11,400 | 1,01,900 | Active |
| C002 | Greenfield Hospital | Dr. Meera Nair | 98450 11223 | purchase@greenfield.example | Kochi | Corporate | Anita | Feb-26 | Sep-26 | 40 | 9 | 3,11,900 | 34655.56 | Active |
| C003 | Sunrise Public School | Harpreet Kaur | 98140 55667 | office@sunrise.example | Ludhiana | Government | Anita | Apr-26 | Jun-26 | 130 | 3 | 1,51,000 | 50333.33 | At risk |
| C004 | Metro Mart | Imran Shaikh | 99870 77889 | Pune | Retail | Divya | Dec-25 | Oct-26 | 8 | 11 | 1,94,200 | 17654.55 | Active | |
| C005 | Northstar Pharma | Vikas Rao | 98490 34343 | vikas@northstar.example | Hyderabad | Corporate | Karan | Nov-25 | Apr-26 | 210 | 4 | 5,17,700 | 1,29,425 | Lapsed |
| C006 | Kaveri Textiles | S. Raghavan | 94430 56565 | Coimbatore | Wholesale | Divya | Mar-26 | Jun-26 | 150 | 5 | 2,37,200 | 47,440 | At risk |
What's inside
- Customers sheet for 300 customers: ID, name, contact person, phone, email, city or region, segment, account owner and notes
- Calculated columns: customer since, last purchase, days since last purchase, number of orders, total sales, average order value and status
- Purchases sheet with 2,000 rows: date, customer (drop-down), invoice or order number, product and amount
- Setup sheet: currency symbol, the Active and At risk limits in days (90 and 180 to start) and the segment list
- Summary sheet: customers by status, sales by segment and the top ten customers by total sales
How to use it
- On the Setup sheet type your currency symbol and check the day limits: a customer who bought within 90 days is Active, within 180 days At risk, and older than that Lapsed. Change them to suit how often your customers usually buy.
- On the Customers sheet replace the samples with your customers. Give each a short ID and a unique name, because purchases are matched by name.
- Record purchases on the Purchases sheet: date, customer from the drop-down list, bill number, product and amount. You can paste them from your billing software every month.
- Sort or filter the Customers sheet by Status to see who has lapsed, or by Days since last purchase to see who to call this week.
- Open the Summary sheet for your top customers and sales by segment.
Tips
- Call At risk customers before they become Lapsed: a short "anything you need this month?" call wins back more orders than a discount later.
- Use the Segment column for the way you sell (retail, wholesale, corporate, online, distributor) so the Summary shows which kind of customer brings the most money.
- If a customer changes name, change it on both sheets (Home › Find & Select › Replace) so their history stays together.
- The amounts are whatever you choose to record: with or without tax. Use the same rule every time.
Questions and answers
How does it know the last purchase date?
It finds the latest date on the Purchases sheet for that customer's name. If there is no purchase yet, the status says "No purchase yet".
What is a lapsed customer?
One who has not bought for longer than your At risk limit (180 days to start). The right limit depends on your business: a grocery wholesaler may treat 30 days as lapsed, a machinery supplier two years.
Can I keep several years of purchases?
Yes, up to 2,000 purchase rows in this file. If you need more, insert rows above the last row of the Purchases sheet and the formulas include them.
Is my customer data safe?
The file stays on your computer or wherever you save it. Treat it as confidential: it holds names and phone numbers, so do not share it publicly.