CRM Spreadsheet Template: How to Build a Simple CRM in Excel or Google Sheets

A CRM spreadsheet template is a workbook that keeps every client and prospect in one place: who they are, where each deal stands, what you agreed to do next and when. For a freelancer or a small team with fewer than a few hundred active contacts, a well-structured spreadsheet does the core job of a CRM — no missed follow-ups, a clear pipeline and a realistic estimate of upcoming revenue — without a monthly subscription. The key is to separate contacts, deals and activities into different tabs and to use dropdowns so the data stays clean.

This guide is part of our Client & Time Tracking section. Below you will find the tab structure, the columns to create, the formulas for follow-ups and pipeline value, and a worked example you can copy.

The four tabs of a spreadsheet CRM

Keeping deals separate from contacts matters: one client can generate several projects over time, and you want each one tracked with its own value and stage.

Columns to create

Contacts tab

• Contact ID (C-001, C-002…) — a unique key used by the other tabs
• Company, contact name, role
• Email, phone, website
• Source (referral, website, social, event) — a dropdown
• Type (prospect, active client, past client, partner)
• First contact date, notes, consent/opt-in status if you send marketing emails

Deals tab

• Deal ID, Contact ID, deal name (e.g. “Website redesign”)
• Stage — dropdown: Lead, Qualified, Proposal, Negotiation, Won, Lost
• Value — expected contract value
• Probability — looked up from the stage
• Weighted value — value × probability
• Expected close date, owner, next action and next action date
• Lost reason (filled only when the stage is Lost)

Activities tab

• Date, Contact ID, Deal ID, type (call, email, meeting), summary, outcome

Formulas that make the sheet work

Pull the company name into the Deals tab

Instead of retyping names, look them up from the Contact ID: =XLOOKUP(B2,Contacts!A:A,Contacts!B:B,"Not found"). In older Excel versions use =IFERROR(VLOOKUP(B2,Contacts!A:B,2,FALSE),"Not found").

Probability by stage

Create a small table on the Lists tab with each stage and its probability, then in the Deals tab use =XLOOKUP(E2,Lists!$A$2:$A$7,Lists!$B$2:$B$7). Typical starting values are 10% for Lead, 25% for Qualified, 50% for Proposal, 75% for Negotiation, 100% for Won and 0% for Lost. Replace them with your own historical rates once you have enough closed deals.

Weighted value

=F2*G2 (value × probability). The sum of this column is your weighted pipeline — a more honest revenue estimate than adding up every open deal at full value.

Overdue follow-up flag

=IF(AND(K2<>"",K2<TODAY(),NOT(OR(E2="Won",E2="Lost"))),"OVERDUE",""), where K is the next action date and E the stage. Add conditional formatting to colour these rows, and sort or filter by next action date every morning.

Duplicate check

In the Contacts tab, flag repeated emails with =IF(COUNTIF(E:E,E2)>1,"Duplicate",""). Duplicates are the most common reason spreadsheet CRMs become unreliable.

Worked example: an eight-deal pipeline

A freelance web studio tracks eight opportunities using the stage probabilities above:

The six open deals add up to $20,700 at full value, but their weighted value is $10,470. That second number is the one to use when planning workload or cash. Of the two closed deals, one was won and one lost, so the current win rate is 50% — a figure that becomes meaningful once you have 20 or more closed deals.

Dashboard formulas for this example:

• Open pipeline: =SUMIFS(Deals!F:F,Deals!E:E,"<>Won",Deals!E:E,"<>Lost") → 20,700
• Weighted open pipeline: =SUMIFS(Deals!H:H,Deals!E:E,"<>Won",Deals!E:E,"<>Lost") → 10,470
• Deals per stage: =COUNTIFS(Deals!E:E,"Proposal") → 2
• Win rate: =COUNTIFS(Deals!E:E,"Won")/(COUNTIFS(Deals!E:E,"Won")+COUNTIFS(Deals!E:E,"Lost")) → 50%
• Overdue follow-ups: =COUNTIF(Deals!L:L,"OVERDUE")

Defining your pipeline stages

Stages only help if everyone agrees on what moves a deal forward. Write one exit criterion for each stage on the Lists tab:

Recording a lost reason (price, timing, chose a competitor, no response) turns lost deals into useful data. After a few months, a simple COUNTIFS by lost reason shows whether pricing, speed or targeting is the problem.

A weekly CRM routine (15 minutes)

1. Filter the Deals tab for overdue follow-ups and handle them first.
2. Update the stage and next action date of every deal you touched this week.
3. Log calls and meetings in the Activities tab with a one-line summary.
4. Close deals that have been silent past your agreed follow-up limit and record the reason.
5. Check the dashboard: weighted pipeline, deals per stage and upcoming close dates.

Connect the CRM with the rest of your admin: when a deal is won, create the invoice from your invoice template, log hours in your timesheet, and add the expected payment to your 13-week cash flow forecast.

Keeping contact data safe

• Store only the information you actually need to serve the client.
• Restrict sharing: give edit access only to people who work the pipeline, and use view-only links for everyone else.
• Protect formula columns so they are not overwritten — see how to protect formulas in Excel.
• Track marketing consent separately from client status, and check which privacy rules apply to the people in your list (for example GDPR if you hold data on people in the EU).
• Keep a dated backup copy each month.

Spreadsheet CRM or CRM software?

Starting with a spreadsheet is not wasted effort: the stage definitions, fields and habits you build carry over directly if you later move to software, and a clean spreadsheet imports easily.

FAQ

Can I use this CRM spreadsheet template in Google Sheets?

Yes. XLOOKUP, SUMIFS, COUNTIFS, data validation and conditional formatting all work in Google Sheets, which also makes it easy to share with a partner or assistant.

How many contacts can a spreadsheet CRM handle?

The file itself can hold far more rows than a small business needs. The real limit is discipline: once several people edit the same rows daily or you need automatic email logging, software becomes easier to keep accurate.

What is the difference between pipeline value and weighted pipeline?

Pipeline value adds every open deal at full value. Weighted pipeline multiplies each deal by the probability of its stage, which gives a more realistic estimate of the revenue you can expect.

Should I track lost deals?

Yes. Keeping them with a lost reason shows patterns in pricing, timing or lead quality, and gives you a list of past prospects to contact again later.

Next step: create the Lists tab with your stages and probabilities first, then add Contacts and Deals with dropdowns. Enter your current open opportunities and set a next action date for each one. More tools for client work are in the Freelancers & Creators hub.