Google Sheets CRM: Free Template, Formulas and Where It Stops
Tabs, columns, dropdowns and formulas for a spreadsheet CRM that one to three people can run in Google Sheets, a short Apps Script that emails you each morning, and a clean path into a CRM.

by Benjamin WagnerYes, Google Sheets works as a CRM for one to three people. Give each kind of record its own tab (contacts, deals, activities), make every fixed value a dropdown, and let a Dashboard tab count and sum the rest with QUERY and COUNTIFS. Google itself does not sell a CRM: its Workspace Marketplace lists third-party ones such as Copper, Pipedrive, Streak and Zoho CRM, and Gmail, Forms and Apps Script are the parts you wire together yourself.
Customermates publishes this guide and is the CRM in the migration example at the end. Everything before that section needs only a Google account.
- Five tabs: Lists, Contacts, Deals, Activities and a Dashboard with live follow-up and pipeline tables.
- Form responses that land in the sheet by themselves, change alerts by email, and Apps Script for a morning email of past next steps.
- One to three people, one sales process, conversations mostly by email.
- Access is per file, not per row. Links between tabs are plain text, and LinkedIn or WhatsApp threads never reach the sheet unless someone types them in.
- Download as .xlsx and import one tab at a time: companies, then contacts, then deals.
Use the contact list template for a small, shared directory with a named owner and next step, before you decide whether to move it into a CRM.
Four setup rules for the sheet
- One header row, one row per record. No merged cells, no blank rows, no subtotals between records.
- Turn each data tab into a table. Select the range and choose Format › Convert to table. Google's tables guide lets you set a type per column: Drop-down, Date, Currency, People chip and others. A Date column then flags "next Tuesday" with a warning, which matters once formulas compare dates.
- Make every fixed value a dropdown. Insert › Drop-down, or Data › Data validation with "Drop-down (from a range)" pointing at the Lists tab. According to Google's dropdown help, invalid entries are rejected by default. Keep it that way: "Proposal" and "proposal sent" are two stages to every formula.
- Link by name. A contact's company is the company's exact name, a deal's contact the person's full name. Names are readable, and CRM imports, Customermates included, match links by name.
The template, tab by tab
Lists
| Range | Values |
|---|---|
| A: Stage, B: Probability | Prospecting 10%, Qualification 20%, Demo 40%, Proposal 60%, Negotiation 80%, Won 100%, Lost 0% |
| D: Contact type | Lead, Customer, Partner, Former customer |
| E: Source | Referral, Website, LinkedIn, Event, Web form |
| F: Activity type | Call, Email, Meeting, LinkedIn, WhatsApp |
| G: Team | Full name of everyone who owns records |
These stages and probabilities are an example. Customermates has no preset stages: create the same options on the Stage field of your deals list in Data model (or let AI setup build them in the cloud), and the Stage column moves over later without renaming. Replace the percentages once you have a few months of won and lost deals to count.
Contacts
Columns A to J: First name, Last name, Company, Email, Phone, LinkedIn URL, Contact type, Source, Owner, Notes. Keep first and last name apart; most CRMs need both to create a person. If you sell to companies, add a Companies tab with Company, Website, Industry and City, and point the Company column's dropdown at it.
Deals
| Column | Content | Example |
|---|---|---|
| A Deal | Text | Brookfield Architects security review |
| B Company | Dropdown | Brookfield Architects |
| C Contact | Full name | Sarah Okafor |
| D Value | Number | 18000 |
| E Stage | Dropdown from Lists!A | Proposal |
| F Probability | Formula | 60% |
| G Weighted | Formula | 10800 |
| H Owner | Dropdown from Lists!G | Anna Berg |
| I Next step | One sentence | Send revised scope |
| J Next step date | Date | 2025-12-04 |
| K Closed on | Date, filled when Won or Lost |
Put these in row 2 and fill them down:
F2 =IFERROR(VLOOKUP(E2,Lists!A:B,2,FALSE),0)
G2 =D2*F2Activities
One row per conversation that mattered: Date, Contact, Deal, Type, Owner, Summary and Next step. It is the only tab that answers "what did we last tell them?", so fill it right after the call or nobody will.
Dashboard
Four numbers:
Open pipeline =SUMIFS(Deals!D2:D,Deals!E2:E,"<>Won",Deals!E2:E,"<>Lost",Deals!A2:A,"<>")
Weighted pipeline =SUMIFS(Deals!G2:G,Deals!E2:E,"<>Won",Deals!E2:E,"<>Lost",Deals!A2:A,"<>")
Win rate =COUNTIF(Deals!E2:E,"Won")/MAX(1,COUNTIF(Deals!E2:E,"Won")+COUNTIF(Deals!E2:E,"Lost"))
Past next steps =COUNTIFS(Deals!J2:J,"<"&TODAY(),Deals!E2:E,"<>Won",Deals!E2:E,"<>Lost")Two functions make the Google version better than the Excel one. QUERY builds the pipeline by stage as a table that updates itself:
=QUERY(Deals!A1:K,"select E, count(A), sum(D) where E is not null and E <> 'Won' and E <> 'Lost' group by E label count(A) 'Deals', sum(D) 'Value'",1)And FILTER gives you a live follow-up list, every open deal whose next step date has passed:
=FILTER(Deals!A2:J,Deals!J2:J<>"",TODAY()>Deals!J2:J,Deals!E2:E<>"Won",Deals!E2:E<>"Lost")Put each on its own block of the Dashboard and leave room below it, because both spill downwards.
What Google adds around the sheet
Lead capture with Google Forms. Link a form to the spreadsheet under Responses › Select destination for responses, and Google's Forms help says each new response is added automatically. Leave the responses tab as it is and copy qualified leads into Contacts by hand. Editing or reordering its columns is the fastest way to break the form link.
Change alerts. Tools › Notification settings › Edit notifications sends an email when anyone changes the file, right away or as a daily digest. Per Google's notification help, it covers other people's edits only. It tells you that Anna edited Deals. It does not tell you that a next step date passed.
A morning email from Apps Script. For that, open Extensions › Apps Script and paste:
function mailPastNextSteps() {
const rows = SpreadsheetApp.getActive().getSheetByName('Deals').getDataRange().getValues();
const today = new Date(); today.setHours(0, 0, 0, 0);
const due = rows.slice(1).filter(r =>
r[9] instanceof Date && r[9] < today && r[4] !== 'Won' && r[4] !== 'Lost');
if (due.length) {
MailApp.sendEmail(Session.getEffectiveUser().getEmail(),
due.length + ' past next steps',
due.map(r => r[0] + ': ' + r[8]).join('\n'));
}
}Then add a trigger (clock icon › Add trigger), event source "Time-driven", type "Day timer", 7 to 8 am. Column J is index 9 because the array starts at 0. Google's Apps Script quotas cap a free Gmail account at 100 email recipients and 90 minutes of trigger runtime a day; Workspace accounts get 1,500 recipients and 6 hours. One email a day is far below both.
Filter views instead of filters. A normal filter changes the view for everyone with access. Data › Create filter view saves a filter that changes only your own view, as Google's filter help explains. Give each person a "My open deals" filter view on Owner and Stage.
Where a Google Sheets CRM stops working
Size is rarely the problem. Google allows up to 20 million cells per spreadsheet in its Drive file limits, far more than a small team fills. These limits show up first:
- Everyone sees everything. Sharing works per file. Protected ranges stop edits, but Google's protection help says plainly that people can still copy and export a protected spreadsheet, and that protection "shouldn't be used as a security measure". A freelancer you add to update two deals can read the whole client list.
- Links are text. Rename a company and every contact and deal that used the old name points nowhere until you run Find and replace.
- Conversations stay elsewhere. Gmail threads, LinkedIn messages and WhatsApp chats reach the sheet only when someone summarizes them in Activities.
- Personal data multiplies. Every download, copy and "make a copy for me" is another place a person's data lives. When someone uses their GDPR rights to see their data (Article 15) or have it erased (Article 17), you have to find all of them.
Stay in Sheets, add a tool, or switch?
| If this describes you | Do this |
|---|---|
| One person, a few hundred contacts, mostly email | Keep the template above and the Apps Script email |
| Two or three people, one shared deal list, everyone may see everything | Keep the sheet, add filter views per person and notification rules |
| You want CRM features but your team refuses to leave Sheets | Look at the CRM add-ons in the Google Workspace Marketplace, which work inside Sheets or Gmail |
| Several people share deals, follow-ups slip, or deals move in LinkedIn and WhatsApp threads | Move to a CRM, starting with the import below |
| You run Excel or Microsoft 365 rather than Google | Use the Excel CRM template, which has the same layout plus a weekly tracker per salesperson |
A simple rule of thumb: once two people edit the same deals every week and one follow-up has slipped, move. Before that, the sheet is fine.
Moving a Google Sheets CRM into Customermates
Customermates imports Excel workbooks (.xlsx) and CSV files, one list at a time, so a Google Sheet goes through a download first. There is no live sync with Google Sheets. The import documentation has the full rules, and the Excel template guide maps every column of this layout to a field. A sheet only follows up when someone remembers to open it. In Customermates, each client's conversations sit on the client record (on Customermates Cloud from Pro), Mate or your own AI agent drafts the reply and the next step, and the next-step date feeds your dashboard instead of a filter you have to check.
Clean the tabs
Use Data › Data cleanup › Remove duplicates on Contacts and Deals, and fix any value that a dropdown flags. Every stage and type should be exactly one of the Lists values.Create the custom fields
In Customermates, open the list in Data model and choose Add, then Field. For each dropdown column (Contact type, Source, Industry), add a Single choice field with option labels that match your Lists tab exactly. Use Date fields for Next step date and Closed on. The deal Stage field already exists with the stages above.Download the workbook
Choose File › Download › Microsoft Excel (.xlsx). The wizard asks which tab to import, so the same file serves every import.Import in dependency order
Companies into Organizations, then Contacts, then Deals. On each list, open More actions, choose Add from file, and match each column to a field, for example email addresses to the contact's Contact details field. Links are matched by name, ignoring case, so a company must exist before a contact can point to it. Owner goes into Assigned, matched by the team member's full name.Test with ten rows
The check lists every problem row before anything is added, and you can skip those rows. An import has no undo, so try a small file first. Bring only open next steps from Activities over as tasks; older history can go into a deal's Notes or stay in the archived sheet.
Deal values work differently: in Customermates a deal's value is the sum of its line items, each a service times quantity. The Excel guide's import steps show how to bring your price list in first.
What replaces the Dashboard and the Apps Script
- QUERY by stage becomes the Deals list as a board grouped by Stage, with total and weighted value per column, shown in the pipeline view.
- The FILTER follow-up list becomes a saved view of deals filtered on Next step date, sorted oldest first. It can be shared with the team.
- The morning email can become a routine in Customermates Cloud. Customermates has no reminders or notifications, but a routine set to run daily at 08:00 can follow an instruction like this one, using hosted AI credits:
List my open deals whose Next step date is today or earlier. For each one, create a task linked to the deal, assigned to me, named "Follow up" plus the deal name. Do not send or draft any messages.
A routine runs as its owner, and Mate sends messages when told to, so the last sentence matters. Starter includes 1 routine per user and Pro 5; the routines documentation has the run limits.
- The Activities tab gives way, on Pro and higher, to the unified inbox, where Gmail, Outlook, IMAP, LinkedIn, WhatsApp, Instagram and Telegram threads sit with the contact. Starter has no inbox.
If you still want a reporting copy in Google Sheets, webhooks can send record changes to a separately run n8n instance, which writes them into a sheet.
What it costs, and when Customermates is the wrong step
| Team | Plan | Per month |
|---|---|---|
| 3 people, CRM without inbox | Starter, €12 per user | €36 |
| 3 people, with the unified inbox | Pro, €29 per user | €87 |
| Your own server | Self-hosted AGPL core | No licence fee, plus hosting; REST, webhooks and MCP, but no inbox and no Mate |
Billing is monthly, and the hosted trial runs 14 days on Pro with no credit card: start your trial and import one tab to see your own deals there. Each paid hosted plan includes monthly hosted AI credits per active user; see pricing.
Customermates is the wrong step if your process lives in formulas: it has no formula fields, so custom calculations stay in a spreadsheet through the export. It is also wrong if you need a phone dialer, email campaigns or a native phone app. Customermates has none of those, so look for a CRM that ships them. And if the real requirement is "stay inside Google Sheets", a Marketplace add-on fits better than any separate CRM.
FAQ
Can you use Google Sheets as a CRM?
Yes, for one to three people with one sales process. The five-tab layout above covers contacts, deals, activities and a dashboard, and Apps Script can email you past next steps each morning. It cannot restrict who sees which rows, keep links intact when a name changes, or pull in conversations from Gmail, LinkedIn or WhatsApp.
Does Google have a CRM tool?
No. Google Workspace has Sheets, Gmail, Contacts and Forms, but no CRM of its own. The Sales & CRM category of the Workspace Marketplace lists third-party CRMs that connect to Google, among them Copper, Pipedrive, Streak and Zoho CRM.
Is there a free CRM template for Google Sheets?
The layout on this page is free: copy the headers into a blank spreadsheet, add the dropdowns and paste the formulas. If you download a template elsewhere, check that it keeps one record per row, contacts apart from companies, and no merged cells. A template that fails those checks is hard to import into a CRM later.
How many contacts can a Google Sheets CRM handle?
A spreadsheet holds up to 20 million cells, according to Google's file limits. The practical limit is people, not rows: shared access and slipped follow-ups become a problem long before the file fills up. Customermates currently reads up to 5,000 rows per import file, so split larger tabs.
Can I connect a Google Sheets list to a CRM?
Some CRMs ship a Sheets add-on; the Workspace Marketplace lists Copper CRM for Google Sheets, for example. Customermates does not: it imports Excel and CSV files and exports .xlsx files, one list at a time, with no live sync. You can export a list, edit it in Sheets, download it as .xlsx and import it again as Existing records; its Record ID column updates the existing records instead of creating duplicates.
Can you create a CRM in Excel instead?
Yes. The Excel CRM template uses the same structure with Excel tables, XLOOKUP and a weekly tracker per salesperson. Pick the tool your team already shares files in.
Google details checked on 8 October 2026 against Google's help pages on tables, dropdowns, QUERY, Forms responses, notifications, filter views, protected ranges, Drive file limits, the Apps Script quotas and the Workspace Marketplace.
Start from one description
One description. A workspace your agent built.
This is what setup builds from one sentence. Sample data, the real product.
Describe your business
One sentence about your team, your clients and how you sell.
Mate builds it
It asks a few questions, then creates the lists, pipeline, dashboard and playbook.
Work in it
Connect LinkedIn and email. Click through a finished example below.
We're a four-person strategy consultancy. Track clients, engagements and proposals, and help us follow up with prospects on LinkedIn.
Widgets built from the workspace's own lists and fields
Every client conversation from email, LinkedIn and WhatsApp in one place. Mate sets up your workspace and drafts the follow-up, you review and send. Works with Claude, ChatGPT or any agent that speaks MCP.
Related pages
Excel CRM Template: Free Sheet Layout and Formulas
A free Excel CRM template: six sheets with columns, dropdowns and formulas for pipeline and rep tracking, built so each sheet imports into a CRM later.
Free CRM: What Free Plans Include and Where They Stop
Free CRM plans compared by users, contacts and storage, plus open-source CRMs you host yourself, and a one-afternoon test before you commit your data.
Simple CRM
A simple CRM with an open-source core: lists built by the cloud AI setup or by you, plus Excel import. From €12 per user per month.
CRM records, lists, and views
Work with the lists of your workspace: records, fields, saved views, board, import and export.
Customermates
Follow up on every client conversation
Start with one channel and one client on Customermates Cloud, or self-host the open-source core.
✓ No credit card required ✓ First 14 days free





