How to Build a Simple CRM in Google Sheets

You don't need a $99/user/month tool to track contacts, deals, and follow-ups. A well-structured Google Sheet handles the CRM basics for small teams — with the bonus that you control every field, formula, and view. This guide walks through building one from scratch: contacts, deals, activity log, and a summary dashboard.

The 4-sheet structure

A working CRM needs four tabs:

1. Contacts — everyone you know or want to know
2. Deals — open opportunities and their stage
3. Activities — call, email, meeting, and note log
4. Dashboard — pipeline value, activity counts, key metrics

Each row in Deals references a Contact ID. Each row in Activities references a Contact and optionally a Deal. This linking is what turns a flat spreadsheet into a CRM.

Sheet 1 — Contacts

Row 1 headers. Row 2 onwards, one row per person.

Column notes

• Contact ID — a stable unique identifier. Manual (C001, C002...) or formula-based. Deals and Activities reference this.
• Source — where the lead came from. Great for later analysis ("what channel converts best?").
• Owner — who's responsible for the relationship. Filter by owner for individual pipelines.
• Status — Cold, Warm, Hot, Customer, Lost. Drives the "who needs attention" logic.
• Last Touched — updated manually or from the Activities sheet (see formulas below).

Sheet 2 — Deals

Pull the Contact Name automatically

Column D (Contact Name) should look itself up rather than being typed. That way if you rename a contact, it updates everywhere.

Or with the newer XLOOKUP:

Auto-calculate the weighted value

Weighted value = deal value × probability. Column H:

If your Probability column stores percentages as text ("50%"), use =F2 * (G2/1) or store as a decimal (0.5) with % display formatting.

Sheet 3 — Activities

Column notes

• Date — press Ctrl+; to insert today's date as a static value
• Contact ID / Deal ID — reference the entity this activity relates to. Deal ID is optional (some activities aren't tied to a specific deal).
• Type — Call, Email, Meeting, Note, Task
• Direction — In / Out (mostly for calls and emails)

Update "Last Touched" on the Contact automatically

Instead of manually updating column I on the Contacts sheet, compute it from the Activities log. In Contacts column I:

Returns the most recent Activity date for each Contact ID. Format the cell as a date.

Data validation dropdowns

Manual entry of Stage, Source, Type, and Status leads to typos ("Hot", "hot", "HOT", "H0t") that break your filters. Fix with dropdowns.

1. Select the column (e.g., Deals!E2:E)
2. Data → Data validation → Add rule
3. Criteria: Dropdown — enter each option on its own line: New, Contacted, Qualified, Demo, Proposal, Negotiation, Won, Lost
4. Save

Now every cell in that column shows a clickable dropdown. No typos. Filters and pivot tables work cleanly.

Pipeline formulas

Total pipeline value

Total weighted pipeline (probability-adjusted)

Pipeline value in the "Won" column only

Deals expected to close this month

Days since last touched (per contact)

Add a "Days Since" column to Contacts (column K):

Next action for each contact

Use IFS to derive a recommended action based on status and days since:

Count of activities per contact

Conditional formatting

Highlight what needs attention. Format → Conditional formatting.

Cold contacts turn orange

On the Contacts sheet, select rows A2:K:

• Custom formula: =$K2 > 60
• Fill: light orange

Any row where "Days Since" (column K) exceeds 60 gets highlighted.

Very cold (90+ days) turn red

• Custom formula: =$K2 > 90
• Fill: light red

Order matters — put the "> 90" rule above "> 60" in the rules list.

Hot deals in the pipeline

On Deals, select A2:J:

• Custom formula: =$G2 >= 0.7
• Fill: light green

Stalled deals (close date past)

• Custom formula: =AND($I2 < TODAY(), $E2 <> "Won", $E2 <> "Lost")
• Fill: light red

Highlights any deal whose expected close is behind schedule and isn't yet closed.

Sheet 4 — Dashboard

The at-a-glance view. Keep it simple: 6-8 metrics and 2-3 charts.

Top-row summary cells

Metric	Formula
Total contacts	=COUNTA(Contacts!A2:A)
Hot contacts	=COUNTIF(Contacts!H:H, "Hot")
Open deals	=COUNTIFS(Deals!E:E, "<>Won", Deals!E:E, "<>Lost")
Pipeline value	=SUMIFS(Deals!F:F, Deals!E:E, "<>Won", Deals!E:E, "<>Lost")
Weighted pipeline	=SUMIFS(Deals!H:H, Deals!E:E, "<>Won", Deals!E:E, "<>Lost")
Won this month	=SUMIFS(Deals!F:F, Deals!E:E, "Won", Deals!I:I, ">="&EOMONTH(TODAY(),-1)+1)
Activities this week	=COUNTIFS(Activities!A:A, ">="&TODAY()-WEEKDAY(TODAY(),2)+1)

Deals by stage — a QUERY-based summary

Use QUERY to build a live count and total per stage:

Returns a small table: Stage | Deals | Value. Drop a bar chart on top to visualize.

Add a stage chart

1. Select the QUERY output range
2. Insert → Chart
3. Chart type: Column chart
4. Stage on X-axis, Value on Y-axis
5. Customize colors to match your brand

Add a source-of-lead pie chart

Chart the result as a pie or donut.

Optional automations

Send daily reminder emails

Extensions → Apps Script. Paste a small script that runs on a daily trigger:

function dailyReminders() {
  const sheet = SpreadsheetApp.getActive().getSheetByName('Contacts');
  const data = sheet.getDataRange().getValues();
  const rows = data.slice(1)
    .filter(r => {
      const daysSince = r[10]; // column K
      return typeof daysSince === 'number' && daysSince > 30;
    })
    .map(r => `${r[1]} (${r[2]}) — ${r[10]} days since touched`);

  if (rows.length) {
    MailApp.sendEmail({
      to: Session.getActiveUser().getEmail(),
      subject: `CRM: ${rows.length} contacts need attention`,
      body: rows.join('\n')
    });
  }
}

Then: Apps Script → Triggers → Add Trigger → dailyReminders → Time-driven → Day timer → 8am.

Auto-log emails as activities

A Zapier or Make integration can watch your Gmail inbox and drop new emails to specific senders into the Activities sheet. Free-tier plans on both services handle a small CRM's volume.

Slack notifications on stage change

Apps Script onEdit(e) trigger: watch the Deals sheet, and when a stage changes to "Won", post to a Slack webhook. Under 20 lines of code once you have the webhook URL.

Where Sheets stops being enough

A Sheets CRM is excellent up to a point. Signs you're outgrowing it:

• Over 5,000 contacts and formulas are getting slow
• You need multiple people editing simultaneously without stepping on each other
• Deal automation is getting complex — multi-step workflows, approval routing
• You need email tracking (opens, clicks) built into the CRM
• Reports need custom user permissions (some people can only see their pipeline)

At that point, move to a dedicated tool. But by then, you'll know your process well enough to pick the right one — because you built the workflow in Sheets first.

Common pitfalls

Frequently asked questions