Use Google Sheets AI to Build a Pipeline Value Tracker
What This Does
Google Sheets' built-in AI (powered by Gemini) can write formulas for you — so you can build a simple pipeline tracker that automatically calculates your estimated monthly commission based on your current leads, without knowing anything about spreadsheet formulas.
Before You Start
- You have a Google account (free — the same one you use for Gmail)
- You know your commission structure (e.g., 7% of gross sale, or 40% of net margin)
- You have a list of your active leads (name, job estimate size, stage)
- Time needed: 15 minutes to set up; 5 minutes/week to maintain
- Cost: Free (Google Sheets is free with a Google account)
Steps
1. Open a New Google Sheet
Go to sheets.google.com and click the "+" button to create a blank new spreadsheet. Name it "My Roofing Pipeline" by clicking "Untitled spreadsheet" at the top.
What you should see: A blank grid with columns A, B, C...
2. Set Up Your Column Headers
In Row 1, type these headers (one per cell, starting at A1):
- A1: Homeowner Name
- B1: Address
- C1: Estimated Job Size ($)
- D1: Pipeline Stage
- E1: Close Probability (%)
- F1: Weighted Value
- G1: Est. Commission
What you should see: Seven bold header labels across the top row.
3. Fill in a Few Rows with Your Leads
Enter 3–5 of your current leads. For Pipeline Stage, use: "Inspection Scheduled," "Proposal Sent," "Claim Filed," or "Contract Signed." For Close Probability, use your gut estimate: 20% for early leads, 60% for proposal sent, 90% for claim approved.
4. Use AI to Write the Weighted Value Formula
Click on cell F2. Then look for the "Help me write a formula" option — it appears as a small AI icon or under the Insert menu as "Help me write." (If you don't see it, type directly: this is also available via Gemini side panel — click the star/sparkle icon on the right sidebar.)
Type: "Multiply column C by column E to get the weighted pipeline value"
What you should see: A formula like =C2*E2 appears in the cell. Click Accept.
5. Use AI to Write the Commission Formula
Click on cell G2. Use the same AI formula assistant and type: "Calculate commission as [YOUR COMMISSION %]% of cell C2"
For example: "Calculate commission as 7% of cell C2"
What you should see: A formula like =C2*0.07 appears. Click Accept.
6. Apply Formulas to All Rows
Select cells F2 and G2. Click and drag the small square in the bottom-right corner of the selection down through all your lead rows. The formulas copy automatically.
7. Add a Total Row
In the row below your last lead, type "TOTAL" in column A. In column G, use the AI assistant: "Add up all values in column G from row 2 to row [last row number]" — it will write a SUM formula for you.
What you should see: A grand total of your estimated monthly commission at the bottom of the sheet.
Real Example
Scenario: You have 6 active leads. You want to know if you're on track for your income goal this month.
| Homeowner | Address | Job Size | Stage | Probability | Weighted | Commission (7%) |
|---|---|---|---|---|---|---|
| Garcia | 412 Oak | $14,000 | Claim Filed | 80% | $11,200 | $784 |
| Patel | 88 Elm | $9,500 | Proposal Sent | 60% | $5,700 | $342 |
| Johnson | 301 Pine | $18,000 | Contract Signed | 95% | $17,100 | $1,064 |
What you get: A live view showing ~$2,190 in estimated commissions in your current pipeline — giving you a clear picture of where you stand and which leads to prioritize.
Tips
- Update the Close Probability column weekly as leads progress — this keeps your forecast accurate.
- Color-code pipeline stages with conditional formatting: ask the AI "highlight cells in column D green when they say Contract Signed" to make it visual.
- Share the sheet with your manager to replace your weekly pipeline call — they'll have everything they need at a glance.
Tool interfaces change — if a button has moved, look for similar AI/magic/smart options in the same menu area.