CRM Template Excel - Contacts, Sales Pipeline and Follow-Ups

About this template

A CRM - customer relationship manager - is at its core a list: who you are talking to, where each deal stands, what it is worth and when you next need to get in touch. Before a team needs integrations and automation, a well-built spreadsheet does that job, costs nothing per seat and opens anywhere. This template is that spreadsheet.

The Pipeline sheet holds one row per contact or deal. You type the contact details, the lead source, the stage, the deal value and the dates; the sheet looks up the stage's win probability, works out the weighted value (deal value times probability) and compares the next follow-up date with today, so overdue follow-ups are flagged in red without anyone checking.

Stages and their probabilities live on the Stages sheet - Lead 10%, Contacted 20%, Qualified 40%, Proposal 60%, Negotiation 80%, Won 100%, Lost 0% by default - and you can rename them or change the percentages on this page or in the sheet. The Summary sheet counts deals and adds up value and weighted value by stage, and gives the win rate and the open pipeline's weighted forecast.

Columns on the Pipeline sheet: Contact, Company, Email, Phone, Source, Stage, Deal value, Probability, Weighted value, Last contact, Next follow-up, Follow-up status, Owner and Notes, with the header in row 4.

How to use it

1. Set your stages - On this page, list your pipeline stages with a probability each ("Proposal | 60"). They become the Stage drop-down and the lookup table on the Stages sheet.
2. Add contacts and deals - One row each: name, company, email, phone and where the lead came from. Pick the stage from the drop-down and enter the expected deal value.
3. Always set a next follow-up date - The follow-up column is what keeps deals from going quiet. Past dates show Overdue in red; dates in the next seven days show Due this week.
4. Move deals through the stages - Change the Stage as a deal progresses. Probability and weighted value update, and the Summary sheet re-totals the pipeline.
5. Review the summary weekly - The weighted forecast is a realistic estimate of what the open pipeline is worth; the win rate is won deals as a share of closed ones.

Formulas in this workbook

Every formula below is written into the workbook itself, exactly as shown. The cell references are the ones the default options produce; change the row or column counts and they shift to match.

• =IF(F5="","",IFERROR(VLOOKUP(F5,Stages!$A$4:$B$13,2,FALSE),0)) - Probability: looks the deal's stage up on the Stages sheet and returns its win probability.
• =IF(OR(G5="",H5=""),"",G5*H5) - Weighted value: deal value times win probability - what the deal is worth to the forecast today.
• =IF(OR(K5="",F5="Won",F5="Lost"),"",IF(K5<TODAY(),"Overdue",IF(K5-TODAY()<=7,"Due this week","Scheduled"))) - Follow-up status against today's date. Won and lost deals need no follow-up, so they stay blank.
• =COUNTIF(Pipeline!$F$5:$F$54,A5) - On the Summary sheet: the number of deals in each stage.
• =SUMIF(Pipeline!$F$5:$F$54,A5,Pipeline!$G$5:$G$54) - Total deal value in each stage; the weighted column uses the same SUMIF over weighted values.
• =SUM(B5:B11) - The Total row on the Summary sheet: all deals, and the same SUM for value and weighted value.
• =IFERROR(B10/(B10+B11),"") - Win rate: won deals divided by won plus lost deals.
• =SUMIFS(Pipeline!$I$5:$I$54,Pipeline!$F$5:$F$54,"<>Won",Pipeline!$F$5:$F$54,"<>Lost") - Weighted forecast of the open pipeline: weighted values of every deal not yet won or lost.
• =COUNTIF(Pipeline!$L$5:$L$54,"Overdue") - How many follow-ups are overdue.

Customising it

• Add an Expected close date column and a SUMIFS by month on the Summary sheet for a monthly forecast.
• Turn the table into an Excel Table (Ctrl+T) and add slicers for Owner and Stage for a quick filtered view.
• Log activity history on a separate sheet with the contact name, date and note, and use COUNTIF to show how many touches each deal has had.
• Share it through OneDrive or Google Drive so the whole team edits one copy rather than emailing versions.