The problem
Your leads live in a Google Sheet. Someone fills out your contact form, you add them to a row. A referral comes in, you add another row. A DM turns into a conversation, another row. After a few weeks you have 40 rows and no system. You can't tell which leads are hot and which went cold. You don't know who was supposed to follow up with whom. Nobody has been contacted in a week because everyone assumed someone else was handling it. A CRM would fix this, but you're not ready for a CRM. You don't want to pay for HubSpot or spend a week learning Salesforce. You just need your spreadsheet to actually work as a lead tracker, with clear statuses, assigned owners, and automatic follow-up reminders so nothing falls through the cracks. That's what this guide builds.
What we're building
A Google Sheet that works as a lightweight lead management system:
- A structured lead log with name, email, source, status, owner, and follow-up dates
- Dropdown statuses so every lead has a clear state
- Conditional formatting that makes overdue follow-ups visually obvious
- Optional: automatic email reminders when a follow-up date is approaching
- Optional: automatic notification when a new lead is added
Step 1: Set up the lead tracker structure
Create a new Google Sheet. Name the first tab "Leads." Set up these columns:
- A: Lead Name
- B: Email
- C: Company
- D: Source (where the lead came from)
- E: Status
- F: Owner (who's responsible for this lead)
- G: Date Added
- H: Last Contact Date
- I: Next Follow-Up
- J: Notes
For the Status column (E), add data validation with a dropdown:
- New
- Contacted
- Qualified
- Proposal Sent
- Won
- Lost
- On Hold
For the Source column (D), add a dropdown:
- Website Form
- Referral
- Cold Outreach
- Event
- Other
For the Owner column (F), add a dropdown with your team members' names. If you're solo, just use your name. Format columns G, H, and I as dates.
Step 2: Add a "Days Since Last Contact" formula
In column K, add the header "Days Since Contact" and enter this in K2:
=IF(H2="", "", TODAY() - H2)Or use ARRAYFORMULA in K2 to fill every row:
=ARRAYFORMULA(IF(H2:H="", "", TODAY() - H2:H))This shows how many days since you last reached out. Anything above 7 is probably going stale.
Step 3: Add a "Follow-Up Overdue" indicator
In column L, add "Overdue?" and enter this in L2:
=IF(AND(I2<>"", I2<TODAY(), E2<>"Won",
E2<>"Lost"), "OVERDUE", "")ARRAYFORMULA version:
=ARRAYFORMULA(IF(AND(I2:I<>"", I2:I<TODAY(),
E2:E<>"Won", E2:E<>"Lost"),
"OVERDUE", ""))This flags any lead where the follow-up date has passed and the lead isn't closed.
Step 4: Add conditional formatting
Select the Overdue column (L). Go to Format, Conditional formatting. Add a rule: if text is "OVERDUE," set the background to light red and text to bold. You can also highlight the entire row: select your data range (A2:L100 or however far your data goes), add a custom formula rule: =($L2="OVERDUE"), and set the background to a light red. For the Status column, add coloring:
- "New" = light blue
- "Qualified" = light green
- "Won" = green
- "Lost" = light gray
- "On Hold" = light yellow
Step 5: Add a simple dashboard
At the top of the sheet (or on a separate "Dashboard" tab), add these summary formulas:
Total Leads: =COUNTA(A2:A)
New: =COUNTIF(E:E, "New")
Contacted: =COUNTIF(E:E, "Contacted")
Qualified: =COUNTIF(E:E, "Qualified")
Won: =COUNTIF(E:E, "Won")
Lost: =COUNTIF(E:E, "Lost")
Overdue Follow-Ups: =COUNTIF(L:L, "OVERDUE")Step 6 (optional): Automatic follow-up reminders
This script checks every lead with an upcoming or overdue follow-up date and sends you a reminder email. Open Extensions, then Apps Script. Paste this:
function sendFollowUpReminders() {
var sheet =
SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Leads');
var lastRow = sheet.getLastRow();
if (lastRow < 2) return;
var data = sheet.getRange(2, 1,
lastRow - 1, 12).getValues();
var today = new Date();
today.setHours(0, 0, 0, 0);
var reminders = [];
for (var i = 0; i < data.length;
i++) {
var name = data[i][0]; // A: Lead Name
var company = data[i][2]; // C: Company
var status = data[i][4]; // E: Status
var owner = data[i][5]; // F: Owner
var followUp = data[i][8]; // I: Next Follow-Up
if (status === 'Won' || status
=== 'Lost') continue;
if (!followUp || !(followUp
instanceof Date)) continue;
var fDate = new Date(followUp);
fDate.setHours(0, 0, 0, 0);
var daysUntil = Math.floor((fDate
- today) / (1000 * 60 * 60 * 24));
if (daysUntil <= 1) {
var label = daysUntil < 0 ?
'OVERDUE by ' + Math.abs(daysUntil) + ' days' : (daysUntil === 0 ? 'TODAY' :
'TOMORROW');
reminders.push(label + ': ' +
name + (company ? ' (' + company + ')' : '') + ' [' + status + '] - Owner: '
+ owner);
}
}
if (reminders.length === 0)
return;
var body = 'You have ' +
reminders.length + ' follow-up(s) due:\n\n' + reminders.join('\n');
MailApp.sendEmail({
to: 'you@company.com',
subject: reminders.length + '
lead follow-up(s) due',
body: body
});
}Set a daily trigger for this function. Every morning, you'll get an email listing which leads need attention today.
How to extend this
Connect to a Google Form. If leads come in through a form, link the form to this sheet. New submissions auto-create lead rows. Add an onFormSubmit trigger to auto-set the Date Added and Status fields. Add lead scoring. Create a Score column with a formula that assigns points based on source, company size, or form answers. Sort by score to prioritize your best leads. Auto-assign to team members. Round-robin new leads across owners using a script that checks who has the fewest active leads and assigns the new one to them. Track emails sent. Add a "Last Email Sent" column. When you send a follow-up, update this column (manually or via script). The Days Since Contact formula then reflects actual communication, not just when you added the lead.
Common issues
ARRAYFORMULA doesn't work with AND. Google Sheets ARRAYFORMULA doesn't expand AND() across rows. Use the multiplication trick instead: =ARRAYFORMULA(IF((I2:I<>"")(I2:I