Use Google Sheets AI to Build a Prospect Pipeline Tracker

What This Does

Google Sheets' built-in AI can write formulas, add conditional formatting, and help you build a simple but powerful prospect tracking spreadsheet, even if you've never written a formula in your life. This gives you CRM-like visibility without any new software.

Before You Start

• You have a Google account (free Gmail account works)
• You can open Google Sheets at sheets.google.com
• Optional: Google Workspace account gives access to Gemini in Sheets for more advanced AI

Steps

1. Set up your spreadsheet structure

1. Go to sheets.google.com and click Blank spreadsheet
2. Name it "Prospect Pipeline: [Community Name] [Month/Year]"
3. In row 1, create these column headers in cells A1–J1:

   • A: Prospect Name
   • B: Phone
   • C: Email
   • D: Unit Interest
   • E: Tour Date
   • F: Last Contact Date
   • G: Days Since Contact
   • H: Pipeline Stage
   • I: Notes
   • J: Next Action

2. Use AI to write the "Days Since Contact" formula

1. Click on cell G2 (the first data row under "Days Since Contact")
2. In the formula bar at the top, type: =
3. Look for the Explore button (a small star icon) in the bottom-right corner of your screen and click it
4. In the Explore panel that opens, type: "How do I calculate the number of days between today and a date in column F?"
5. The AI will show you the formula: =IF(F2="","",TODAY()-F2). Click to insert it into your cell
6. Drag the formula down column G to apply it to all rows

3. Add conditional formatting to highlight hot prospects

1. Select all rows in column G (click the G header to select the whole column)
2. Click Format in the top menu → Conditional formatting
3. Under "Format rules," click the dropdown and select Greater than
4. Type 7 in the value field (to flag prospects you haven't contacted in 7+ days)
5. Click the fill color box and choose red or orange
6. Click Done. Any prospect you haven't contacted in a week will now appear highlighted

4. Add a Pipeline Stage dropdown

1. Click on cell H2
2. Click Data in the top menu → Data validation
3. Under "Criteria," select Dropdown (from a list)
4. Type your pipeline stages separated by commas: New Lead, Toured, Applied, Approved, Denied, Lease Signed, Cold
5. Click Save. Now every cell in column H has a consistent dropdown

Real Example

Scenario: You have 30 active prospects in various stages and you keep losing track of who you haven't followed up with.

What you set up: The spreadsheet with all 30 prospects entered; column G calculates days since last contact automatically; red highlighting instantly shows you who's been waiting 7+ days.

What you get: Every morning, open the spreadsheet, look for red cells in column G, and start your follow-up list. No mental math, no missed prospects.

Tips

• If you have Google Workspace with Gemini in Sheets, click the Gemini button in the top toolbar and ask: "Which prospects in this sheet have been in 'Toured' stage for more than 7 days and haven't been contacted?" It will filter and highlight for you
• Color-code your pipeline stages by adding conditional formatting to column H as well (green for Lease Signed, orange for Applied, grey for Cold)
• Keep this tab open on a second monitor or browser tab during your shift so you can update it in real time between tasks

Tool interfaces change. If a button has moved, look for similar AI/magic/smart options in the same menu area.