Track Sales Pipeline in Google Sheets
Build a lightweight CRM to manage deals, forecast revenue, and track sales team performance—without expensive software.
Most startups and small businesses cannot justify expensive CRM software like Salesforce—but you still need to track your sales pipeline. Which deals are closing this month? What is your win rate? How much revenue is in the pipeline? Without answers, you are flying blind. Google Sheets provides everything you need for a lightweight sales CRM: deal tracking, revenue forecasting, stage management, and team dashboards. No monthly fees, no complex setup, and full customization to match your sales process. In this comprehensive guide, you will build a complete sales pipeline tracker from scratch. Track leads from first contact to closed deal, forecast monthly revenue with confidence, and identify bottlenecks in your sales process. By the end, you will have a functional CRM that rivals paid software—for free.
What You'll Need
- Basic familiarity with Google Sheets
- Understanding of your sales process stages
- List of current deals or leads to import
Step-by-Step Guide
Define Your Sales Pipeline Stages
Map out the steps from lead to closed deal.
- Common stages: Lead → Qualified → Demo/Proposal → Negotiation → Closed Won/Lost
- Customize for your business: Add stages like "Free Trial" for SaaS, "Site Visit" for services
- Keep it simple: 4-6 stages maximum—too many stages slow sales down
- Define stage criteria: What must happen for a deal to move from Demo to Negotiation?
- Assign probability: Lead (10%), Qualified (25%), Demo (50%), Negotiation (75%), Closed Won (100%)
Pro Tip
Your sales stages should match your actual process, not a template. Shadow your sales calls to see what really happens.
Build Your Pipeline Tracker Sheet
Create a structured table to log all deals.
- Column A: Deal Name (Company Name + Product/Service)
- Column B: Contact Name
- Column C: Email/Phone
- Column D: Deal Value (expected revenue)
- Column E: Current Stage (use data validation dropdown)
- Column F: Probability % (auto-populates based on stage)
- Column G: Expected Close Date
- Column H: Owner (sales rep name)
- Column I: Source (referral, website, cold outreach)
- Column J: Notes/Next Steps
Pro Tip
Use Data → Data validation for Stage and Owner columns. Dropdowns prevent typos and make filtering easier.
Add Stage-Based Automation
Use formulas to auto-populate probability and flags.
- Auto-assign probability based on stage: =IF(E2="Lead",0.1,IF(E2="Qualified",0.25,IF(E2="Demo",0.5,IF(E2="Negotiation",0.75,IF(E2="Closed Won",1,0)))))
- Calculate weighted value: =D2*F2 (Deal Value × Probability)
- Flag overdue deals: =IF(AND(G2<TODAY(), E2<>"Closed Won", E2<>"Closed Lost"), "OVERDUE", "")
- Days in stage: =TODAY()-DateEnteredStage (add hidden column for stage entry date)
- Highlight stale deals: Conditional formatting for deals in same stage >30 days
Pro Tip
Weighted pipeline value is more accurate than total pipeline. A $100k deal at 25% probability = $25k weighted.
Create Pipeline Value Summary
Calculate total pipeline value and forecast monthly revenue.
- Total pipeline value: =SUM(Weighted_Value_Column)
- Total deals in pipeline: =COUNTA(Deal_Name_Column)
- Pipeline by stage: =SUMIF(Stage_Column, "Demo", Deal_Value_Column)
- Expected revenue this month: =SUMIFS(Weighted_Value_Column, Close_Date_Column, ">=2024-01-01", Close_Date_Column, "<2024-02-01")
- Pipeline by owner: =SUMIF(Owner_Column, "Alice", Weighted_Value_Column)
Pro Tip
Create a separate "Dashboard" sheet with all summary metrics at the top for quick visibility.
Track Win Rate and Sales Metrics
Measure team performance and conversion rates.
- Overall win rate: =COUNTIF(Stage_Column, "Closed Won") / COUNTIFS(Stage_Column, "Closed Won") + COUNTIFS(Stage_Column, "Closed Lost")
- Win rate by rep: =COUNTIFS(Owner_Column, "Alice", Stage_Column, "Closed Won") / (Closed Won + Closed Lost for Alice)
- Average deal size: =AVERAGE(Deal_Value_Column)
- Average sales cycle: =AVERAGE(Days_From_Lead_To_Close)
- Conversion rate by stage: % of deals that move from each stage to next
Pro Tip
Track win rate monthly to spot trends. Dropping win rate signals need for better qualification or pricing adjustments.
Build Visual Pipeline Dashboard
Create charts to visualize deal flow and revenue forecast.
- Pipeline Funnel Chart: Horizontal bar chart showing deals by stage
- Revenue Forecast Chart: Line chart showing expected monthly revenue
- Deals by Source Chart: Pie chart showing which channels bring best leads
- Pipeline by Owner: Column chart comparing team member performance
- Use Insert → Chart, then customize colors and labels
- Add trendlines to forecast charts to spot growth or decline
Pro Tip
Use traffic light colors: green for Closed Won, yellow for active stages, red for Closed Lost.
Set Up Activity Tracking
Log sales activities to ensure consistent follow-up.
- Create "Activities" sheet with columns: Date, Deal Name, Activity Type, Notes
- Activity types: Call, Email, Demo, Proposal Sent, Follow-Up
- Count activities per deal: =COUNTIF(Activities!B:B, DealName)
- Last contact date: =MAX(IF(Activities!B:B=DealName, Activities!A:A))
- Flag deals with no activity in 7+ days: =IF(TODAY()-LastContactDate>7, "FOLLOW UP NEEDED", "")
Pro Tip
Set a rule: Every deal must have activity logged within 7 days. Use conditional formatting to highlight neglected deals.
Automate Reporting and Share Access
Set up weekly pipeline reviews and team access.
- Create weekly snapshot: Copy pipeline data to "History" sheet every Monday
- Share with team: Share → "Anyone with link can edit" for sales team collaboration
- Set view-only for leadership: Commenters can see pipeline but not edit
- Export weekly report: File → Download → PDF for exec team meetings
- Set calendar reminder: Review pipeline every Monday morning
Pro Tip
Use version history (File → Version history) to track changes. If someone accidentally deletes data, restore previous version.
Wrapping Up
Frequently Asked Questions
How do I build a sales pipeline in Google Sheets?
Create a table with columns: Deal Name, Contact, Deal Value, Stage, Probability, Close Date, Owner, Source. Use data validation for Stage dropdown. Add formulas for weighted value (Deal Value × Probability) and conditional formatting to flag overdue deals. Create a dashboard with pipeline by stage, forecast revenue, and win rate metrics.
What is weighted pipeline value and how do I calculate it?
Weighted pipeline value multiplies each deal value by its probability of closing. Formula: =Deal_Value × Probability_%. Example: A $100,000 deal at Demo stage (50% probability) = $50,000 weighted value. Sum all weighted values for total weighted pipeline. This gives more accurate revenue forecasts than total pipeline value.
How do I calculate win rate in Google Sheets?
Win Rate = Closed Won Deals / (Closed Won + Closed Lost). Formula: =COUNTIF(Stage,"Closed Won")/(COUNTIF(Stage,"Closed Won")+COUNTIF(Stage,"Closed Lost")). Track by time period: =COUNTIFS(Stage,"Closed Won",CloseDate,">="&DATE(2024,1,1)) / Total Closed Deals. Aim for 20-30% win rate; higher suggests good qualification.
What sales pipeline stages should I use?
Common stages: Lead (10% probability) → Qualified (25%) → Demo/Proposal (50%) → Negotiation (75%) → Closed Won (100%) or Closed Lost (0%). Customize for your business: SaaS may add Free Trial, services may add Site Visit. Keep 4-6 stages maximum. Each stage should have clear entry criteria and next steps.
Can Google Sheets replace Salesforce for small businesses?
For startups and small sales teams (<10 people), yes. Google Sheets provides pipeline tracking, forecasting, activity logging, and dashboards without monthly fees. You lose advanced automation, email integration, and complex workflows. If your team needs those features or manages 100+ deals/month, upgrade to a dedicated CRM. Otherwise, Sheets is sufficient.
