Sales CRM System in Excel: Leads, Stages, Weighted Pipeline, Follow-up Reminders, Win Rate by Source and Dashboard

By Ravindra Bagale · Last updated: 17 October 2026

← All Excel dashboards

Download the template: sales-crm-lead-pipeline-system-template.xlsx: a 7-sheet system with example data and real formulas (Read me, Setup, Leads, Activities, Pipeline, Lead card, Dashboard). Yellow cells are inputs you can type over; green cells hold formulas; blue rows are headers and totals. Replace the example data with your own and every report and the dashboard recalculate.

Made-up data: every name, amount, business and date on this page is made up for practice. Cities are used only to make the examples feel real.

1. The business story

Shahrukh Solar Solutions, Pune, as of 17 October 2026 (made-up data). Target ₹15,00,000 a month.

The morning starts with a filter on Follow-up status, not with a guess.

2. Build it sheet by sheet

Step 1. Setup: stages

1. Stages with chance of winning in A7:B12: New 10%, Contacted 25%, Site visit 50%, Quotation sent 70%, Won 100%, Lost 0%.
2. Sources in D7:D11, owners in F7:F9, B4 monthly target.

Step 2. Leads

1. A5 ="LD-"&TEXT(ROW()-4,"000"). Type company, source, owner, value and stage from drop-downs.
2. H5 chance with XLOOKUP and I5 weighted =F5*H5.
3. L5: =IF(OR(G5="Won",G5="Lost"),"Closed",IF(J5="","No date",IF(J5<Setup!$B$3,"Overdue",IF(J5=Setup!$B$3,"Today","Upcoming")))) (IF with OR).
4. M5 days open =IF(K5="",Setup!$B$3,K5)-B5.

Step 3. Activities

1. One row per call, visit, meeting or quotation, with the lead ID. C5 company with XLOOKUP.

Step 4. Pipeline report

1. By stage: leads, value and weighted value with COUNTIFS and SUMIFS.
2. By owner: open leads, open weighted, won and overdue follow-ups.
3. By source: E20 win rate =IFERROR(C20/(C20+D20),0).

Step 5. Lead card and Dashboard

1. Lead card: type a lead ID in B4. Set the print area.
2. KPI cards: open leads, open pipeline, weighted pipeline, won in October, target achieved and overdue follow-ups. Charts: value by stage and open weighted by owner.

3. Formulas used

Every formula above is written in the template, so you can click a cell and see it in the formula bar. All values on this page were recalculated in LibreOffice with zero errors.

4. Common mistakes

1. Open leads with no follow-up date. No date means no call. The status shows No date so you can fix it.
2. Counting the full pipeline as sales. Use the weighted value to plan; a new lead is not money yet.
3. Stages not updated. Change the stage the day it changes, or weighted value lies.
4. Deleting lost leads. Keep them as Lost; win rate by source needs them.
5. Chance set per lead by feel. Keep chance per stage on Setup so everyone uses the same scale.

5. More real examples

Example 1: Real estate. Stages become enquiry, site visit, token, agreement; value is the flat price.

Example 2: B2B supplies. Add a product column and expected order date per lead.

Example 3: Interior studio. Leads turn into estimates like the interior design costing system.

Quick revision cards

Englishमराठीहिंदी

Quick revision

Each card shows formulas from this page, each with an example and its answer from the template. Scan the QR code on a card to open this lesson. All names and numbers are made up for practice.

More cards: Ravi Sir's Cards.

6. Frequently asked questions

How do I make a simple CRM in Excel?

One row per lead with stage, value and next follow-up; a Setup sheet for stages and a report with COUNTIFS and SUMIFS.

What is a weighted pipeline?

Deal value × chance of the stage, added over open leads.

How do I show overdue follow-ups?

Compare the next follow-up date with today: before today is Overdue, equal is Today.

How do I calculate win rate?

Won ÷ (won + lost). Leads still open are left out.

How do I track the monthly target?

SUMIFS deal value where stage is Won and closed date is in the month, ÷ target.

7. Quick quiz

Ten questions on this page. Sign in to save your score.