Build a CRM in Google Sheets (Free Template)

Salesforce starts at USD $25 per user per month for its most basic plan. For a team of five in a small Australian business, that is roughly AUD $2,400 a year before you add configuration, training, and the consulting hours needed to make it actually useful. For many SMBs, that budget simply does not exist, especially when the business is still at the stage where customer relationships are tracked in someone's inbox, on sticky notes, or in a spreadsheet with no structure.

Here is the good news: you do not need expensive software to track customers, manage a sales pipeline, and follow up on leads. Google Sheets, which you already have as part of your Google Workspace subscription, can serve as a surprisingly capable CRM for teams of two to fifteen people. It will not replace a dedicated CRM forever, but it will get your customer data organised, your pipeline visible, and your follow-ups on track right now, at zero additional cost.

This guide walks you through building a functional CRM in Google Sheets from scratch, including the spreadsheet structure, data validation rules, dashboard formulas, and Apps Script automations that turn a plain spreadsheet into a working system your team can use from day one.

Designing Your Sheets CRM Structure

A CRM is only as useful as its structure. The most common mistake is dumping everything into a single tab: contacts, deals, notes, and follow-up dates all mixed together in a growing mess of columns. That approach breaks within a month.

Instead, build your CRM across four separate tabs, each with a clear purpose:

1. Contacts — Your master list of people and companies. One row per contact. This is the foundation everything else references.

2. Deals / Pipeline — Every active opportunity your team is working on. Each deal links back to a contact and moves through defined stages.

3. Activity Log — A running record of calls, emails, meetings, and notes. Each entry references a contact or deal, creating a timeline of interactions.

4. Dashboard — A summary view that pulls data from the other three tabs using formulas and charts. This is where your team checks pipeline health every morning.

This separation mirrors how proper CRM software works. It keeps your data clean, avoids duplication, and makes it possible to build useful summaries without scrolling through thousands of mixed rows.

Essential Columns and Data Validation

Contacts Tab

Set up the following columns in your Contacts tab:

Deals / Pipeline Tab

Setting Up Data Validation

Data validation keeps your CRM consistent by restricting inputs to predefined options. Without it, one team member types "New Lead" while another types "new lead" and a third types "Lead — New", and your pipeline reports become useless.

For the Stage column in Deals:

1. Select the entire Stage column (excluding the header).
2. Go to Data > Data validation > Add rule.
3. Set the criteria to Dropdown and enter your stages: New Lead, Contacted, Discovery, Proposal Sent, Negotiation, Closed Won, Closed Lost.
4. Under "If the data is invalid," select Reject the input.

For the Status column in Contacts:

Apply the same dropdown approach with values: Lead, Active, Inactive, Churned.

For the Owner column:

Create a dropdown with your team members' names so assignments are consistent and filterable.

For the Source column:

Use values like: Google Search, Referral, LinkedIn, Cold Outreach, Website Form, Trade Show, Existing Client.

These dropdowns take five minutes to set up and save hours of data cleanup later. They also make your QUERY formulas and pivot tables reliable, since every value is spelled and capitalised identically.

Dashboard with QUERY Formulas and Charts

The Dashboard tab is where your CRM goes from a data entry tool to something your team actually wants to open every morning. Use QUERY formulas to pull live summaries from your Contacts, Deals, and Activity Log tabs.

Pipeline Summary by Stage

This formula counts how many deals are in each pipeline stage:

=QUERY(Deals!A1:K, "SELECT E, COUNT(A), SUM(F) WHERE A IS NOT NULL GROUP BY E ORDER BY COUNT(A) DESC LABEL COUNT(A) 'Deals', SUM(F) 'Total Value'", 1)

This gives you a table showing each stage, the number of deals in it, and the total dollar value. Insert a bar chart from this output to visualise where your pipeline is concentrated.

Total Pipeline Value

=SUMIF(Deals!E2:E, "<>Closed Won", Deals!F2:F) - SUMIF(Deals!E2:E, "Closed Lost", Deals!F2:F)

This sums the value of all open deals, excluding closed-won and closed-lost records. Display this prominently at the top of your Dashboard tab as a headline number.

Weighted Pipeline Value

=SUM(Deals!J2:J)

The weighted value multiplies each deal's value by its probability. This gives a more realistic forecast than raw pipeline totals. A $10,000 deal at 20% probability contributes $2,000, not $10,000.

Deals Closing This Month

=QUERY(Deals!A1:K, "SELECT A, B, D, F, E WHERE E <> 'Closed Won' AND E <> 'Closed Lost' AND G >= date '2025-12-01' AND G <= date '2025-12-31' ORDER BY G", 1)

This pulls all deals with a close date in the current month that have not yet been won or lost. Your team sees exactly which opportunities need attention right now.

Stale Deals (No Activity in 14+ Days)

=QUERY(Deals!A1:K, "SELECT A, B, D, F, K WHERE E <> 'Closed Won' AND E <> 'Closed Lost' AND K < date '2025-12-15' ORDER BY K", 1)

Deals that go quiet tend to go cold. This formula flags any open deal where the last activity date is more than two weeks ago, so your team can re-engage before it is too late.

Charts to Add

Build these three charts on your Dashboard tab:

• Pipeline funnel chart — A bar chart showing deal count by stage, ordered from New Lead to Closed Won. This reveals where deals get stuck.
• Revenue by owner — A bar chart showing total weighted pipeline value per team member. Useful for capacity planning and coaching.
• Deals won over time — A line chart tracking closed-won deals by month. This is your revenue trend indicator.

Select the data output from each QUERY formula and go to Insert > Chart to create each visual. Position them on the Dashboard tab so the most important metrics are visible without scrolling.

Automations with Apps Script

Google Sheets has a built-in scripting engine called Apps Script that can automate repetitive CRM tasks. You do not need to be a developer to use these scripts. Go to Extensions > Apps Script, paste the code, and set up a trigger.

Automated Follow-Up Reminders via Email

This script checks your Deals tab daily and sends you an email if any open deal has not been updated in seven or more days:

function sendFollowUpReminders() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Deals");
  var data = sheet.getDataRange().getValues();
  var today = new Date();
  var reminders = [];

  for (var i = 1; i < data.length; i++) {
    var stage = data[i][4]; // Stage column (E)
    var lastActivity = new Date(data[i][10]); // Last Activity column (K)
    var daysSince = Math.floor((today - lastActivity) / (1000 * 60 * 60 * 24));

    if (stage !== "Closed Won" && stage !== "Closed Lost" && daysSince >= 7) {
      reminders.push(
        data[i][1] + " (" + data[i][3] + ") — " + daysSince + " days since last activity"
      );
    }
  }

  if (reminders.length > 0) {
    MailApp.sendEmail({
      to: Session.getActiveUser().getEmail(),
      subject: "CRM Follow-Up Reminders — " + reminders.length + " deals need attention",
      body: "The following deals have not been updated in 7+ days:\n\n" + reminders.join("\n")
    });
  }
}

To run this automatically, go to Triggers (clock icon in Apps Script), click Add Trigger, select sendFollowUpReminders, and set it to run daily.

Auto-Populate Company Name from Contact ID

This script automatically fills in the Company column on the Deals tab when you enter a Contact ID, saving manual lookups:

function onEdit(e) {
  var sheet = e.source.getActiveSheet();
  if (sheet.getName() !== "Deals") return;

  var editedColumn = e.range.getColumn();
  if (editedColumn !== 3) return; // Contact ID is column C

  var contactId = e.range.getValue();
  var contactsSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Contacts");
  var contactsData = contactsSheet.getDataRange().getValues();

  for (var i = 1; i < contactsData.length; i++) {
    if (contactsData[i][0] === contactId) {
      sheet.getRange(e.range.getRow(), 4).setValue(contactsData[i][3]); // Company column
      break;
    }
  }
}

This runs automatically every time someone edits a cell in the Contact ID column of the Deals tab. No manual trigger needed since onEdit fires on every edit.

Auto-Timestamp New Contacts

This script automatically adds the current date to the Date Added column when a new contact row is created:

function onEdit(e) {
  var sheet = e.source.getActiveSheet();
  if (sheet.getName() !== "Contacts") return;

  var editedColumn = e.range.getColumn();
  var row = e.range.getRow();

  if (editedColumn === 2 && row > 1) { // First Name column (B)
    var dateCell = sheet.getRange(row, 11); // Date Added column (K)
    if (dateCell.getValue() === "") {
      dateCell.setValue(new Date());
    }
  }
}

Note: If you use both onEdit scripts, combine them into a single function to avoid conflicts.

Limitations and When to Upgrade

A Google Sheets CRM works well for small teams, but it has real limitations you should plan for.

Performance degrades with scale. Google Sheets handles up to 10 million cells, but performance noticeably slows once you exceed a few thousand rows with complex QUERY formulas and multiple cross-tab lookups. If your contact list grows past 2,000 to 3,000 records, you will start feeling the drag.

No built-in email tracking. A proper CRM logs email opens, link clicks, and reply rates automatically. Sheets cannot do this. You are reliant on team members manually logging activities, which means your Activity Log is only as complete as your team's discipline.

No mobile app experience. While Google Sheets has a mobile app, entering CRM data on a phone is tedious. Dedicated CRMs offer mobile apps designed for quick updates on the go, which matters if your team works in the field.

Reporting has limits. Sheets dashboards built with QUERY and charts are functional but basic compared to the drill-down reports, forecasting models, and AI-driven insights in platforms like Salesforce or HubSpot.

Multi-user editing creates friction. When two people edit the same tab simultaneously, cell-level conflicts are rare but structural issues can arise. There is no record-level locking, assignment notifications, or workflow automation without custom Apps Script.

When to Upgrade

Consider moving to a dedicated CRM when any of these are true:

• Your team exceeds 10 to 15 people using the system daily
• Your contact database passes 3,000 records
• You need automated email sequences or built-in email tracking
• Your sales process requires multi-step approval workflows
• You want AI-driven forecasting and lead scoring
• You need a mobile-first experience for field teams

The good news is that a Sheets CRM makes upgrading easier, not harder. Your data is already structured in clean tables with consistent columns. Exporting a CSV from Sheets and importing it into a dedicated CRM is straightforward, and the discipline your team builds around data entry in Sheets transfers directly to any new system.

Free Template Download

To save you the setup time, we have built a ready-to-use Google Sheets CRM template that includes all four tabs (Contacts, Deals, Activity Log, and Dashboard), pre-configured data validation dropdowns, the QUERY formulas described above, conditional formatting for stale deals, and the Apps Script automations.

Look for the "Sheets CRM Template" link in the resources section of our website, or reach out to us directly and we will share it with your Google account.

Recommended Tools and Upgrade Paths

If you are already running Google Workspace and your Sheets CRM is reaching its limits, here are two paths worth considering before making the leap to Salesforce.

Google Workspace (Your Foundation)

Everything described in this guide runs on Google Workspace, which your business likely already has. If you are not yet on Workspace, or if you are on an older plan, consider upgrading through our referral link:

Get started with Google Workspace — Google Workspace Business plans start at AUD $10.80 per user per month and include Sheets, Gmail, Drive, Calendar, and Meet. It is the foundation for your Sheets CRM and dozens of other business workflows.

When You Outgrow Sheets: CRM Upgrade Options

Copper CRM — Purpose-built for Google Workspace teams. It lives inside Gmail as a sidebar, automatically logs emails and calendar events, and syncs contacts bidirectionally with Google Contacts. Pricing starts at approximately USD $23 per user per month (~AUD $36). For small teams moving from a Sheets CRM, Copper has the gentlest learning curve because it works inside the Google tools your team already knows. If your pipeline is growing but you want to stay inside the Google ecosystem, Copper is the natural next step.

HubSpot CRM (Free Tier) — HubSpot offers a genuinely free CRM with contact management, deal tracking, email logging, and basic reporting for unlimited users. The free tier has limitations (HubSpot branding on forms, limited email templates, no custom reporting), but it provides a significant step up from Sheets without any cost. It does not integrate as deeply with Google Workspace as Copper does, but it connects via a Gmail extension and Google Calendar sync. For budget-conscious teams that need more than Sheets but cannot justify a per-user CRM subscription yet, HubSpot Free is a strong intermediate step.

Wrapping Up

You do not need to spend thousands of dollars a year on Salesforce to bring structure to your customer relationships. A well-designed Google Sheets CRM gives small Australian teams a practical, zero-cost system for tracking contacts, managing a pipeline, and keeping follow-ups from falling through the cracks.

The key takeaways:

• Structure matters more than tools. Four tabs (Contacts, Deals, Activity Log, Dashboard) with consistent data validation will outperform an expensive CRM that nobody uses correctly.
• QUERY formulas are your reporting engine. They transform raw data into pipeline summaries, stale-deal alerts, and revenue forecasts without leaving Sheets.
• Apps Script automates the tedious parts. Follow-up reminders, auto-populated fields, and timestamps eliminate manual work that teams inevitably forget.
• Plan your upgrade path. A Sheets CRM is not forever. When your team or data outgrows it, tools like HubSpot Free and Copper CRM provide natural next steps, and your structured data exports cleanly.
• Start today, iterate weekly. Build the basic structure in an hour, start entering contacts, and refine the system as you learn what your team actually needs.

Whether you are a two-person consultancy in Brisbane tracking twenty prospects or a ten-person trades business in Sydney managing a hundred active quotes, a Google Sheets CRM gets you organised now, without waiting for budget approval or a software procurement process. Build it this afternoon and start using it tomorrow.

Need help setting up Google Workspace or choosing the right CRM for your team? Contact us for a free consultation.