How to Build a Simple CRM in Google Sheets

A CRM you will actually keep using is one sheet, one row per lead, and six formulas. The row carries the stage, the quote value and — the column that does the real work — the next action and the date it is due. A small dashboard totals your open pipeline, weights it, and counts how many follow-ups are overdue. It takes about twenty minutes to build and it beats a free CRM trial you abandon in week three. Below: the exact columns, the formulas with ranges that will not break, a worked ten-lead example with every figure recomputed, and an honest account of where this layout stops being the right tool.

This is the sales-side companion to two guides that pick up either end of it: how to price a job decides what goes in the quote-value column, and how to invoice as a freelancer takes over the moment a lead turns into Won.

The two tabs

Two tabs, and resist adding a third. Every abandoned spreadsheet CRM I have seen died of extra tabs.

• Leads — one row per opportunity. Not per person, not per company: per opportunity. The same customer coming back next year is a new row.
• Dashboard — six cells that read the Leads tab. Nothing is typed here.

The Leads tab: ten columns, no more

Column J earns that billing because deals do not usually die of a bad quote — they die of silence. A CRM whose only job is to make sure nothing sits untouched for three weeks has already paid for the twenty minutes it took to build.

Make the stage a dropdown, not free text

Select E2:E500, then Data → Data validation → Add rule → Dropdown, and enter the five stages. This is not cosmetic: every formula below matches on the exact text in column E, and one row typed as "quoted" or "Quote sent" silently drops out of your pipeline total. A dropdown makes that class of bug impossible.

The six formulas

Put these on the Dashboard tab. The ranges stop at row 500 deliberately — whole-column ranges make SUMPRODUCT slow and, worse, let a stray note typed in row 900 join your pipeline.

Column H on the Leads tab is =F2*G2, filled down. And one piece of conditional formatting is worth more than the rest of the sheet: select A2:J500, choose Format → Conditional formatting → Custom formula is, and enter

=AND($J2<TODAY(),$J2<>"",$E2<>"Won",$E2<>"Lost")

with a red fill. Now every lead you have let go quiet turns red on its own, and the sheet tells you what to do the moment you open it.

A worked ten-lead pipeline

Ten leads, mid-quarter. Probability follows the stage: New 0.1, Contacted 0.25, Quoted 0.5, Won 1, Lost 0.

What the six formulas return, each one arithmetic you can redo:

• Open pipeline: the seven rows that are neither Won nor Lost — 2,400 + 1,800 + 6,500 + 3,200 + 1,150 + 5,000 + 900 = $20,950.00
• Weighted pipeline: 1,200 + 450 + 3,250 + 320 + 575 + 1,250 + 90 = $7,135.00
• By stage: New $4,100.00 (2) · Contacted $6,800.00 (2) · Quoted $10,050.00 (3) · Won $5,700.00 (2) · Lost $2,900.00 (1)
• Win rate: 2 ÷ (2 + 1) = 66.7%
• Average won deal: 5,700 ÷ 2 = $2,850.00

The warning about that $7,135

Weighted pipeline is the number every CRM puts in the biggest font, and at this scale it is the one most likely to mislead you. Two reasons, and both matter more for a one-person business than for a sales team.

First, an average of seven coin flips is not a forecast. $7,135 is what you would average if this exact pipeline ran hundreds of times. It will run once. The realistic outcomes are lumpy: lose the $6,500 Carter job and win nothing else and the quarter closes near zero; win it alone and you beat the weighted figure by a mile. Treat weighted pipeline as a way to compare this month against last month, never as money you can spend.

Second, the probabilities are invented until you have data. 0.5 for "Quoted" is a convention, not a measurement. Once you have roughly thirty closed rows, replace it with your own history:

=COUNTIFS(Leads!$E$2:$E$500,"Won",Leads!$D$2:$D$500,"Referral")/COUNTIFS(Leads!$D$2:$D$500,"Referral")

Run that per source and the sheet stops guessing. Ours would say referrals close at one rate and website enquiries at another — and that single comparison is usually worth more than the whole pipeline total, because it tells you where the next hour of marketing should go. Until then, read the stage counts rather than the weighted dollars: three live quotes is a fact, $7,135 is a model.

The weekly routine that makes it work

A CRM is a habit with a spreadsheet attached. Fifteen minutes, once a week:

1. Open the Leads tab and sort by column J, oldest first. Everything red is the whole agenda.
2. Deal with each red row: send the message, make the call, or move it to Lost. Moving a dead lead to Lost is a win — it stops inflating a pipeline you are making decisions on.
3. Every row you touch gets a new next action and a new date. Never leave column J blank on a live lead; a blank there is how a deal goes quiet for six weeks.
4. Glance at the Dashboard. If open pipeline is falling week over week, the problem is upstream of selling — you need more leads, not better follow-up.

That last check is the one that connects this sheet to the rest of the business. A shrinking pipeline today is a cash gap in sixty days, which is why the pipeline total belongs next to your cash flow tracker rather than on its own.

When a spreadsheet CRM stops being the right answer

Being honest about the ceiling is part of recommending the tool. Move off a sheet when any of these is true:

• More than one person edits it daily. Sheets handles concurrent editing, but not two people disagreeing about who owns a lead.
• You need email in the record. Nothing in a spreadsheet logs conversations. If "what did we say in March" matters, that is a real CRM's job.
• Past a few hundred live rows. The formulas here stay fast, but a human stops scanning a list that long, and the sheet quietly becomes an archive.
• You want automatic reminders. Conditional formatting only shouts when you open the file.

None of those apply to most solo businesses, which is the actual point: a free CRM you log into twice is worth less than a sheet you open every Monday.

Quick FAQ

Can you use Google Sheets as a CRM?

Yes, and for a solo or two-person business it is often better than dedicated software, because the thing that makes a CRM work is being opened weekly rather than being feature-complete. A sheet with one row per opportunity, a five-stage dropdown, a next-action date and a small dashboard covers what most small businesses actually use. It stops being the right tool when several people edit it daily, when you need email conversations stored in the record, or when you want reminders that reach you without opening the file.

What should a simple CRM track?

Ten columns is enough: date added, contact name, company, source, stage, quote value, probability, weighted value, next action, and next action date. Source and next-action date are the two people leave out and the two that pay — source eventually tells you where your customers come from, and the date is what stops deals dying of silence.

How do I calculate total pipeline value in Google Sheets?

Sum the quote-value column for every row that is not closed: =SUMIFS(Leads!$F$2:$F$500,Leads!$E$2:$E$500,"<>Won",Leads!$E$2:$E$500,"<>Lost"). For a weighted figure, multiply each open row's value by its probability with SUMPRODUCT. In the ten-lead example above that gives $20,950.00 open and $7,135.00 weighted — and the open figure is the more honest of the two at small volumes.

What is a good win rate for a small business?

There is no reliable benchmark for one-person businesses, and anyone quoting you a single percentage is guessing — win rates swing enormously by trade, price point and where leads come from. The number worth tracking is your own, over time, split by source: a referral that closes at twice the rate of a website enquiry tells you exactly where to spend your next hour, and that comparison works from about thirty closed leads.

General guidance, not sales or financial advice. The ten-lead pipeline uses illustrative figures chosen so every total can be reproduced by hand; the stage probabilities are a common convention, not a measured rate — replace them with your own history as soon as you have enough closed leads.

Related: how to price a job · how to invoice as a freelancer · small business cash flow tracking · bookkeeping in Google Sheets.