A lead tracking spreadsheet needs nine columns you type in, two formulas you never touch, and one rule: every open lead has a next step. Most trackers fail because they have thirty columns and no rule, so rows go stale and the sheet turns into a list nobody trusts.
Here is the layout for Google Sheets (Excel works the same way), the exact formulas, a five-minute daily routine, and an honest note on where a sheet stops working and a board takes over.
The nine columns
| Col | Header | What goes in it |
|---|---|---|
| A | Name | The person, first and last |
| B | Company | Where they work |
| C | Source | Dropdown: referral, website form, outbound email, event, other |
| D | First contact | The date they arrived or you first wrote |
| E | Last touch | Date of the last real exchange, in either direction |
| F | Next step | A verb and a day, like "Call Tue Oct 14" |
| G | Stage | Dropdown: New, Contacted, Replied, Meeting booked, Won, Lost |
| H | Value | What the job is worth if it closes |
| I | Notes | What they said, in a line |
Make Source and Stage dropdowns (Data, then Data validation), so "referral" never gets spelled four ways. The stages are the five from sales pipeline stages for a small business, with won and lost counted as the end. Leave out anything that tempts you to type for its own sake: address, company size, LinkedIn URL. If you need them, you'll look them up when you write.
The two formulas
These go in columns J and K. Fill them down the whole column.
J, days since last touch.
=IF(E2="","",TODAY()-E2)
K, the flag.
=IF(OR(G2="Won",G2="Lost"),"",IF(F2="","NO NEXT STEP",IF(E2="","NO TOUCH YET",IF(J2>7,"STALE",""))))
In words: ignore closed leads. If the Next step cell is empty, say so. If nothing has been sent yet, say that. If more than 7 days have passed since the last touch, call it stale. Then add conditional formatting (Format, then Conditional formatting) so any non-empty flag turns the row red. Seven days is a starting point. Change it to fit your sales cycle.
A few rows, filled in
Say today is October 13. Four rows look like this:
| Name | Source | Last touch | Next step | Stage | Days | Flag |
|---|---|---|---|---|---|---|
| Hector Ruiz | Referral | Oct 9 | Send quote Oct 14 | Replied | 4 | |
| Wendell Verma | Outbound email | Oct 1 | Contacted | 12 | NO NEXT STEP | |
| Lars Beck | Website form | Oct 11 | Call Oct 14 | New | 2 | |
| Joy Amadi | Outbound email | Oct 2 | Follow-up #2 Oct 8 | Contacted | 11 | STALE |
Two of four rows are red. Wendell has no plan, and Joy's planned follow-up came and went five days ago. Those are the two leads that would have drifted off, and the sheet put them in front of you.
The next-step rule
The rule that makes the sheet work is that a row can only leave red two ways: do the step and write the next one, or mark the lead Lost. "Follow up" and "check in later" don't count as next steps, because they have no day. "Call Tuesday Oct 14" does. If you can't think of a next step, that is the answer: the lead is lost, or you owe someone a question. The longer version of this argument is in forty names and six actual deals.
Five minutes a day
- Open the sheet and sort or filter on column K so flagged rows come first (30 seconds).
- For each flagged row, do the step now or reschedule it honestly (about 2 minutes for three or four rows).
- Update Last touch and write the new Next step (1 minute).
- Add any new leads from today (1 minute).
Three counts for Monday morning
Put these in a small box above the table, or on a second tab, so you read the sheet in ten seconds.
Open leads: =COUNTA(A2:A)-COUNTIF(G2:G,"Won")-COUNTIF(G2:G,"Lost"). Rows flagged red: =COUNTIF(K2:K,"?*"), which counts any cell with text in it. Value in play: =SUMIFS(H2:H,G2:G,"<>Won",G2:G,"<>Lost"), which adds up the Value of everything still open.
If 40 are open and 15 are red, you have a follow-up problem, not a lead problem. Once a month, cut the Lost rows older than 30 days and paste them into an Archive tab. A sheet with 400 dead rows in it is slow to sort and easy to ignore.
Where a spreadsheet stops working
A sheet is only as current as your typing, and replies live in Gmail. Last touch is the date you remembered to write down, not the date they replied. Updating a row takes about 30 seconds, so 60 open leads is half an hour a day, which is roughly where a sheet starts costing more than a board. It also breaks when two people edit it, when leads need more than a line of notes, and when you want to send from it, which a sheet can't do. What a customer database should do lists the signs, the list is not the customer explains why a name isn't a relationship, and do I need a CRM as a solo founder covers the question from the other direction.
Moving to a board, and keeping a backup
When you move, export the sheet as CSV (File, Download), map Stage to the board's columns, and keep the sheet read-only for 30 days. Rocketship's pipeline starts with the same stages, and its AI worker is the one sending and reading replies, so the record of what happened doesn't depend on your typing. Until then, keep a backup: Google Sheets keeps version history, but download a CSV copy to a folder once a week, so a deleted tab isn't a catastrophe. Once you have 60 days of data, lead conversion rate shows how to read it by source with the same columns, and the AI workers page shows what runs without the typing.
A tracker only helps once there are leads in it. Rocketship finds the buyers, emails them from your own Gmail, follows up, and books the meeting on its own. Plans that get you customers start at $24.99 a month (Launch), and every monthly plan starts with a 7-day free trial, card required.